n*****t 发帖数: 22014 | 1 最标准的做法肯定是 LAST_UPDATE:
CREATE TRIGGER dbo.trgAfterUpdate ON dbo.YourTable
AFTER INSERT, UPDATE
AS
UPDATE dbo.YourTable
SET last_changed = GETDATE()
FROM Inserted i
不能加 trigger 是什么原理啊?除此之外其他办法又笨又慢又不可靠 |
|
l******9 发帖数: 579 | 2 am trying to access SQL server 2008 R2 from Eclipse pydev ( python 3.2 ) on
win7 .
I need to create a table on database.
The code can be run well. But, I cannot create tables in the database. If I
print the sql string and run the query from SQL server management studio, no
problems.
import pyodbc
sql_strc = " IF OBJECT_ID('[my_db].[dbo].[my_table]') IS NOT NULL n"
sql_strc1 = " DROP TABLE [my_db].[dbo].[my_table] n"
sql_stra = " CREATE TABLE [my_db].[dbo].[my_table] n"
sql_stra1 = "(n"
sql_stra... 阅读全帖 |
|
l******9 发帖数: 579 | 3 【 以下文字转载自 Quant 讨论区 】
发信人: light009 (light009), 信区: Quant
标 题: create table on SQL server from python pyodbc
发信站: BBS 未名空间站 (Fri Aug 1 13:19:15 2014, 美东)
am trying to access SQL server 2008 R2 from Eclipse pydev ( python 3.2 ) on
win7 .
I need to create a table on database.
The code can be run well. But, I cannot create tables in the database. If I
print the sql string and run the query from SQL server management studio, no
problems.
import pyodbc
sql_strc = " IF OBJECT_ID('[my_db].[dbo].[my_t... 阅读全帖 |
|
d**********i 发帖数: 4877 | 4 Chinese troops intrude into Indian territory in Ladakh, erect a tented post
PTI | Apr 19, 2013, 10.08 PM IST
Chinese troops have entered the Indian territory in Daulat Beg Oldi sector
in eastern Ladakh and erected a tented post.(TOI Photo)
LEH/NEW DELHI: In a deep incursion, Chinese troops have entered the Indian
territory in Daulat Beg Oldi (DBO) sector in eastern Ladakh and erected a
tented post, setting the stage for a face-off with Indian troops.
A Platoon-strength contingent of China's Peop... 阅读全帖 |
|
s**********n 发帖数: 868 | 5 USO主要持有当月的合约,所以最近受contango影响,
DBO 6个月,USL 12个月
USO liquidity好,交易量大。DBO交易量不够trading,但长期持有还不错。 |
|
l******9 发帖数: 579 | 6 【 以下文字转载自 Quant 讨论区 】
发信人: light009 (light009), 信区: Quant
标 题: SQL debug step into a store procedure from another one
发信站: BBS 未名空间站 (Mon Jun 30 11:04:12 2014, 美东)
I have to debug a store procedure on SQL server 2008 R2 on Microsoft sql
studio.
In my SQL query, I have to call two store procedures :
EXEC @return_value = [my_database].[dbo].[store_procedure1]
in [my_database].[dbo].[store_procedure1], I need to call another procedure:
execute @return_status = store_procedure2
in debu... 阅读全帖 |
|
l******9 发帖数: 579 | 7 I need to check the returned value from a stored procedure on SQL server
2008 R2.
import pyodbc
sql_str = """
DECLARE @return_value int
SET @return_value = -1
INSERT INTO [my_database].[dbo].[my_table]
EXEC @return_value = [my_database].[dbo].[my_stored_
procedure]
if @return_value <> 0
BEGIN
EXEC sys.sp_addmessage 60000, 16, ' test sp returns
wrong code ! '
... 阅读全帖 |
|
m********0 发帖数: 2717 | 8 嗯,记错了。
SELECT SUM(Volume*WAP*100)
FROM [Stock].[dbo].[weekly_options_20101105]
SELECT SUM(Volume*WAP*100)
FROM [Stock].[dbo].[weekly_options_20101029]
总共,包括换手,5.7亿
572308380.00
EQUITY WEEKLY OPTIONS ONLY大概1.5亿的样子。
155157113.00 |
|
s********u 发帖数: 1054 | 9 UNG United States Natural Gas Fund Natural Gas
USO United States Oil Fund West Texas Intermediate Crude Oil
UGA United States Gasoline Fund Gasoline
DBO PowerShares DB Oil Fund West Texas Intermediate Crude Oil
UHN United States Heating Oil Fund Heating Oil
iPath S&P GSCI Crude Oil Total Return Index ETN (OIL)
PowerShares DB Oil Fund (DBO) |
|
m********0 发帖数: 2717 | 10 JUST FYI, no more comments
SELECT a.Symbol, b.AvgVolume, a.[Date], a.Volume
FROM Stock.dbo.stock_daily a
Join (
SELECT Symbol, AVG(Volume) as AvgVolume
FROM Stock.dbo.stock_daily
WHERE Date >= '20100101'
GROUP BY Symbol
) b
ON a.Symbol = b.Symbol
WHERE 1=1
AND a.Volume > 5*b.AvgVolume
AND a.[Date] >= '20101131'
AND b.AvgVolume > 0
ORDER BY a.Symbol, a.[Date]
Symbol Year_AvgVolume Date Volume Ratio
AERL 74756 12/3/2010 667100 8.923698432
AMAC 9247 12/3/2010 78400 ... 阅读全帖 |
|
m********0 发帖数: 2717 | 11 The thing is the it's not a graph for any stock. I just make it with the
following R code,
require(TTR)
ret<-cumsum(rnorm(252,sd=0.036))
ret2<-exp(ret)
p<-50*ret2
ma20<-SMA(p,20)
ema20<-emaTA(p,20)
ema50<-emaTA(p,50)
plot(p, type="l")
lines(ema20, type="l",col='green')
lines(ema50, type="l",col='red')
and I used lognormal distribution for the returns, textbook assumptions
for
a big group of models.
skeptic(as exists everywhere), yes, I borrowed this idea from ET. Please
don
't rush to criticize,... 阅读全帖 |
|
w*i 发帖数: 63 | 12 A job must done before tomorrow, please help...
we have two tables here,
table 1,
CREATE TABLE dbo.eai_inv_hdr
(
invid numeric(9,0) IDENTITY,
vendorid varchar(10) NOT NULL,
invnbr int NOT NULL,
batchid varchar(10) NOT NULL,
status varchar(1) NOT NULL
)
table2,
CREATE TABLE dbo.eai_inv_dtl
(
invamtid numeric(9,0) IDENTITY,
invid numeric(9,0) NOT NULL,
invamtcd varchar(10) NOT NULL,
invamt decimal(13,2) NOT NULL
)
table example:
|
|
b******u 发帖数: 676 | 13 我需要把database里面比较sensitive的东西encrypt起来。
在网上找到了一些东西。基本上都是要写两个简单的function,
one for encrypt, one for decrypt.
然后用的时候,假设ud_MyCrypt is the function name for encrypt,
insert就要write something like:
Insert table1(SSN) values (dbo.ud_MyCrypt('123456789',NULL))
Say ud_MyDecrypt is the function name for decrypt,select就要写成:
select dbo.ud_MyDecrypt(SSN,NULL , 'SecurePassword' ) from table1
这意味着我所有和DB连的东西都得从写。那我就惨了。有没有什么方法能使
encryption totally transparent呢?不用改我的existing SQL statements? |
|
B*****g 发帖数: 34098 | 14 oracle using connect by + level
sql server 2008正在学习中,看看下面这个例子
CREATE TABLE dbo.Company
(CompanyID int NOT NULL PRIMARY KEY,
ParentCompanyID int NULL,
CompanyName varchar(25) NOT NULL)
INSERT dbo.Company (CompanyID, ParentCompanyID, CompanyName)
VALUES
(1, NULL, 'Mega-Corp'),
(2, 1, 'Mediamus-Corp'),
(3, 1, 'KindaBigus-Corp'),
(4, 3, 'GettinSmaller-Corp'),
(5, 4, 'Smallest-Corp'),
(6, 5, 'Puny-Corp'),
(7, 5, 'Small2-Corp')
WITH CompanyTree(ParentCompanyID, CompanyID, CompanyName, CompanyLevel)
AS |
|
p********a 发帖数: 5352 | 15 不是SQL PROGRAMMER,但需要从SQL SERVER PULL DATA
用SERVER MANAGEMENT STUDIO CONNECT进去了,在那个DATABASES下面,点了DATABASE
的名字BSC,然后TABLES下面怎么只有SYSTEM TABLES,什么都没有呢?DEFAULT的
SCHEMA不就是DBO吗
我用SAS ODBC进去是看得见DATA的
libname bsc odbc datasrc=BSC schema=dbo User=xxx Password=xxx;
谢谢帮助 |
|
z********y 发帖数: 14 | 16 try this one
Create function [dbo].[myFun]
(@P1 int)
returns varchar(1000)
as
begin
declare @aa varchar(1000);
set @aa ='';
select @aa = [value] + ',' + @aa from myTable
where id = @P1 order by [value] desc;
return @aa;
end
select top 1 [id] , [dbo].[myFun]([id]) from myTable where [id] = 1; |
|
i****a 发帖数: 36252 | 17 SQL 2008?
use MERGE
example:
MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED AND TGT.companyname <> SRC.companyname THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
$action, deleted.custid AS de |
|
t********5 发帖数: 274 | 18 select *
From KABCD_PRD.kabcd_email.dbo.tblRealtor
tblRealtor 是表名
dbo 是schema
kabcd_email 数据库名?
KABCD_PRD 这个是什么,去哪里找呢 |
|
i****a 发帖数: 36252 | 19 SELECT GroupID, COUNT(1) AS cnt
FROM dbo.GroupMember
WHERE GroupID IN
(
SELECT GroupID
FROM dbo.GroupMember
WHERE UserID = 3
)
GROUP BY GroupID |
|
m*********y 发帖数: 389 | 20 Let's say you have first name, last name, and address in one table.
Here duplicate means same firstname&lastname&address combination, for
instance:
John|Doe|123 Main st|...|...
John|Doe|123 Main st|...|...
Mike|Smith|567 Springfield ave|...|...
to find duplicate, do this:
select * from dbo.person where firstname+lastname+address
in (select firstname+lastname+address from dbo.person
group by firstname+lastname+address having count(*)>1)
Does this make sense? |
|
j*****n 发帖数: 1781 | 21 nop, there is only dbo in the DB, no other schema.
dbo, |
|
l******9 发帖数: 579 | 22 【 以下文字转载自 Quant 讨论区 】
发信人: light009 (light009), 信区: Quant
标 题: SQL debug step into a store procedure from another one
发信站: BBS 未名空间站 (Mon Jun 30 11:04:12 2014, 美东)
I have to debug a store procedure on SQL server 2008 R2 on Microsoft sql
studio.
In my SQL query, I have to call two store procedures :
EXEC @return_value = [my_database].[dbo].[store_procedure1]
in [my_database].[dbo].[store_procedure1], I need to call another procedure:
execute @return_status = store_procedure2
in debu... 阅读全帖 |
|
l******9 发帖数: 579 | 23 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: get value returned by SQLstored procedure from python
发信站: BBS 未名空间站 (Fri Aug 1 18:07:59 2014, 美东)
I need to check the returned value from a stored procedure on SQL server
2008 R2.
import pyodbc
sql_str = """
DECLARE @return_value int
SET @return_value = -1
INSERT INTO [my_database].[dbo].[my_table]
EXEC @return_value = [my_database].[dbo].[my_stored_
proce... 阅读全帖 |
|
y********o 发帖数: 61 | 24 请问这个procedure干什么用的?sql 蝌蚪,读不懂啊~~求高人指点
USE [CFH_ODS]
GO
/****** Object: StoredProcedure [dbo].[DQ_VALIDATE] Script Date: 07/13/
2015 15:08:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DQ_VALIDATE](@tableID bigint = null, @fldID bigint =
null)
AS
BEGIN
SET NOCOUNT ON
declare @VAL_ID bigint;
--
INSERT INTO DQ_VAL(VAL_START_DT) values(GETDATE());
SELECT @VAL_ID = @@IDENTITY;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
... 阅读全帖 |
|
x****e 发帖数: 1773 | 25 USE [CFH_ODS]
GO
/****** Object: StoredProcedure [dbo].[DQ_VALIDATE] Script Date: 07/13/
2015 15:08:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DQ_VALIDATE](@tableID bigint = null, @fldID bigint =
null) -- This proc takes @tablID and @fldID arguments.
-- No idea what this proc does without knowing about the procs it calls, and
all the tables that are involved in the whole process.
AS
BEGIN
SET NOCOUNT ON
declare @VAL_ID bigint; -- Declare an ... 阅读全帖 |
|
c*****d 发帖数: 6045 | 26 create index ind_p on dbo.product (product_id)
create index ind_pp on dbo.productprice (product_id)
SELECT p.product_id, pp.price / pp.regular_price discount
FROM product p
INNER JOIN productprice pp ON pp.product_id = p.product_id
WHERE p.type = 1
product_id discount
----------- -----------
1 1
(1 row(s) affected) |
|
c*****d 发帖数: 6045 | 27 create index ind_p on dbo.product (product_id)
create index ind_pp on dbo.productprice (product_id)
SELECT p.product_id, pp.price / pp.regular_price discount
FROM product p
INNER JOIN productprice pp ON pp.product_id = p.product_id
WHERE p.type = 1
product_id discount
----------- -----------
1 1
(1 row(s) affected) |
|
c*****m 发帖数: 1160 | 28 谢谢aripple。 在你的启发下,解决了:
=====
declare @aa int;
set @aa=0;
select @aa=@aa*100+dbo.ConvertStringToInt(value) from dbo.fn_Split('version
3.14.5.6', '.') order by position
select @aa
=======
好像是很基本的东西,我都忘记了。 |
|
l******9 发帖数: 579 | 29 【 以下文字转载自 Quant 讨论区 】
发信人: light009 (light009), 信区: Quant
标 题: SQL debug step into a store procedure from another one
发信站: BBS 未名空间站 (Mon Jun 30 11:04:12 2014, 美东)
I have to debug a store procedure on SQL server 2008 R2 on Microsoft sql
studio.
In my SQL query, I have to call two store procedures :
EXEC @return_value = [my_database].[dbo].[store_procedure1]
in [my_database].[dbo].[store_procedure1], I need to call another procedure:
execute @return_status = store_procedure2
in debu... 阅读全帖 |
|
l******9 发帖数: 579 | 30 I have to debug a store procedure on SQL server 2008 R2 on Microsoft sql
studio.
In my SQL query, I have to call two store procedures :
EXEC @return_value = [my_database].[dbo].[store_procedure1]
in [my_database].[dbo].[store_procedure1], I need to call another procedure:
execute @return_status = store_procedure2
in debugging mode of SQL server management studio, I can step into [store_
procedure1] but, I cannot step into [store_procedure2].
Any help would be appreciated. |
|
l******9 发帖数: 579 | 31 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: get value returned by SQLstored procedure from python
发信站: BBS 未名空间站 (Fri Aug 1 18:07:59 2014, 美东)
I need to check the returned value from a stored procedure on SQL server
2008 R2.
import pyodbc
sql_str = """
DECLARE @return_value int
SET @return_value = -1
INSERT INTO [my_database].[dbo].[my_table]
EXEC @return_value = [my_database].[dbo].[my_stored_
proce... 阅读全帖 |
|
R*********r 发帖数: 225 | 32 SQL SERVER VERSION:
CREATE TABLE dbo.Produce (
color varchar(20),
size varchar(20),
fruit varchar(20)
)
INSERT INTO dbo.Produce (
color,
size,
fruit
)
VALUES
( 'red', 'big', 'apple'),
('red' , 'small', 'apple' ),
('green', 'big' , 'apple' ) ,
('yellow', 'small', 'orange' ) ,
('red' , 'small', 'orange')
WITH Temp AS (
SELECT * FROM
(SELECT DISTINCT color FROM Produce) AS X CROSS JOIN
(SELECT DISTINCT size FROM Produce) AS Y
) |
|
s******y 发帖数: 352 | 33 下此问问题时,准备点包子,你就1个为币。唉。
add option schema=yourschema in your libname statment.
大多数时,schema=DBO 就行了。 如果不是default DBO, ask the Admin for the
name of schema you are trying to access.
SAS |
|
l******9 发帖数: 579 | 34 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: get value returned by SQLstored procedure from python
发信站: BBS 未名空间站 (Fri Aug 1 18:07:59 2014, 美东)
I need to check the returned value from a stored procedure on SQL server
2008 R2.
import pyodbc
sql_str = """
DECLARE @return_value int
SET @return_value = -1
INSERT INTO [my_database].[dbo].[my_table]
EXEC @return_value = [my_database].[dbo].[my_stored_
proce... 阅读全帖 |
|
c*****x 发帖数: 429 | 35 【《财经》综合报道】中国中铁股份有限公司(601390.SH) 3月25日表示公司已获得
总价约为47.99亿美元的印度尼西亚南苏门答腊煤炭运输项目建造和运营合同,该合同
占公司中国会计准则下2008年营业收入的 13.96%。
中国中铁在公告中表示,该合同类型为设计-施工-运营(DBO)总承包合同,项目业
主为印尼巴克塔山泛太平洋铁路公司,由中国中铁负责该项目的设计、建造和运营。
合同价款包括两部分,第一部分是设计-采购-施工价格为12.99亿美元,第二部分
是项目运营-维护价格为35亿美元,还需要根据合同条款进行调整。
但是中国中铁在公告中强调因该合同履行期限较长,该合同履行对本公司本年度以
及未来几年的资产总额、净资产和净利润等不构成重大影响。■ |
|
b*****d 发帖数: 61690 | 36 【环球时报综合报道】路透社报道,在海拔超过5000米、被印度称为DBO地区的无人山
地,对峙中的印中士兵每天都会走出帐篷巡视,彼此只隔100米站立。在印媒公布的画
面中,中国军人5日打出英文横幅提示印军后撤,横幅上写:“你们已跨过边界,请撤
回”。6日,中印两国外交部均确认,“帐篷对峙”已经解除。
据外交部发言人华春莹就中印双方解决边境对峙事件答记者问
问:中印双方是否已就解决天南河谷对峙事件达成了共识?目前双方现地对峙状态
是否已经解除?
答:近日,中印两国通过协商,就妥善解决中印边界西段发生的事件达成共识。目
前,双方边防部队已解除在天南河谷地区的对峙。
事件发生后,中印双方从两国关系大局出发,本着建设性的合作态度,通过有关涉
边机制、外交及边防会晤渠道及时妥善处理。维护边境地区的和平安宁符合中印双方的
共同利益。中方愿与印方共同努力,争取早日达成一个公平合理和双方都能接受的边界
问题解决方案。 |
|
|
l*****f 发帖数: 2198 | 38 SELECT * FROM dbo.vehicle_reg where [vehicle_make LIKE "Saturn" && vehicle_
model LIKE “Astra"];
尼玛几秒钟的功夫,硬要搞21个工作日。 |
|
s******t 发帖数: 1956 | 39 不推来推去了,改用石头砸!
中越南海冲突时就用了水炮,弹弓和酒瓶子....打伤越南多人...
下面是印度的报道:
LEH/NEW DELHI: Indian border guards today frustrated an attempt by Chinese
soldiers to enter Indian territory along the banks of famous Pangong lake in
Ladakh resulting in stone pelting that caused minor injuries to people on
both sides, officials said.
Soldiers of the People's Liberation Army (PLA) tried to enter the Indian
side in two areas -- Finger Four and Finger Five -- twice between 6 am and 9
am. But on both the occasions their at... 阅读全帖 |
|
a********c 发帖数: 3657 | 40
fee
dude, i thoguht u work on street? USO, OIL, DBO are etn and because of
Contango u r guaranteed to lose in long term. |
|
|
s**********n 发帖数: 868 | 42 Agree.
I used to have DBO, but couldn't stand the contango. Now I only have a
little COP. Holding oil companies with good upstream business makes more
sense in the long run than dealing with futures. |
|
i******w 发帖数: 407 | 43 TD就提供了这四个免费的:DBC,DBO,DJP,DPU。
有没有人hold其中的? |
|
I**A 发帖数: 2345 | 44 嗯,的确是这个好理解
JOIN dbo.enrollment e 这个join by default是啥join来着?inner? |
|
s*l 发帖数: 9421 | 45 第二题table 的 owner was missing. like dbo.employee. |
|
p****1 发帖数: 275 | 46 有兴趣做不? 我琢磨着, 出题的人是在寻找最优解 :)
Given the table [tSWIFT_History_Trades] , the C# structure TradeNAK and the
C# method UpdateTradeACKNAKDB as defined below:
CREATE TABLE [tSWIFT_History_Trades](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[BatchId] [varchar](25) NOT NULL,
[TradeId] [varchar](25) NOT NULL,
[Account] [varchar](25) NULL,
[TranType] [varchar](50) NULL,
[TradeDate] [int] NULL,
[SettleDate] [int] NULL,
[Exch] [varchar... 阅读全帖 |
|
p****1 发帖数: 275 | 47 Question:
Given the table [tSWIFT_History_Trades] , the C# structure TradeNAK and the
C# method UpdateTradeACKNAKDB as defined below:
CREATE TABLE [tSWIFT_History_Trades](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[BatchId] [varchar](25) NOT NULL,
[TradeId] [varchar](25) NOT NULL,
[Account] [varchar](25) NULL,
[TranType] [varchar](50) NULL,
[TradeDate] [int] NULL,
[SettleDate] [int] NULL,
[Exch] [varchar](50) NULL,
[D... 阅读全帖 |
|
h*****s 发帖数: 114 | 48 实时查google map API有局限:速度问题,访问次数限制问题
基于location搜索现在是很普遍的了,一般做法是:
step 1,根据地址用google map api反查出经纬度,然后保存到自己数据库或者其他数
据源
step 2, 按照不用距离,通过经纬度之间数学运算就可以找到满足条件的地址了,这时
候已经脱离google map api了
如果你用SQL Server,下面一个Function可以使用,其他DB的话,自己转化
CREATE FUNCTION [dbo].[GetDistanceBetweenTwoPoint](
@lat1 FLOAT,
@long1 FLOAT,
@lat2 FLOAT,
@long2 FLOAT)
RETURNS FLOAT
AS
BEGIN
DECLARE @DegToRad AS FLOAT
DECLARE @Ans AS FLOAT
DECLARE @Miles AS FLOAT
SET @DegToRad = 57.29577951
... 阅读全帖 |
|
d****s 发帖数: 117 | 49 Goldman Sachs Crude Oil Total Return ETN (OIL)
MacroShares Oil Down ETF (DOY)
MacroShares Oil Up ETF (UOY)
PowerShares DB Crude Oil Double Long ETN (DXO)
PowerShares DB Crude Oil Double Short ETN (DTO)
PowerShares DB Crude Oil Long ETN (OLO)
PowerShares DB Crude Oil Short ETN (SZO)
PowerShares DB Oil Fund (DBO)
ProShares Ultra DJ-AIG Crude Oil ETF (UCO)
ProShares UltraShort DJ-AIG Crude Oil ETF (SCO)
United States 12 Month Oil Fund (USL)
|
|
c**********n 发帖数: 45 | 50 Based on my historical research, there is a high probability that the
airline stocks will decline in the upcoming months, including LCC, UAUA, LUV
, DAL, AMR, CAL. At the same time, crude oil is looking for more bullish
move, I think the USO or DBO will be a good trade for the following months. |
|