May 22, 2008 at 5:26 pm
select
distinct b.tradekey,
a.workerregioncode,
b.referencedate,
a.issuerid,
a.tradeemplid,
a.tradeEmplName,
b.accountid,
b.accountidtype,
b.secid,
b.secidtype,
d.secid,
b.buysell,
b.quantity,
b.price,
b.netamount,
b.tradedate,
b.settlementdate,
null as symbol,
null as parentsymbol,
case
when b.cancelflag='1' then 'cxl' else '' end as cancel,
case
when b.correctionflag='1' then 'corr' else '' end as correction,
d.issuename as securityName,
case when left(d.putCallInd,1)='P' then 'Put' when left(d.putCallInd,1)='C' then 'Call' else '' end,
d.convertibleflag,
d.callableflag,
null as cusip,
d.maturitydate,
a.otwtype,
f.securitycode,
b.opencloseind,
0 as outside,
'OTW' as transType,
case when h.sector is not null and h.sector<>'' then 'GWM' else 'GMI' end as tradeEmplType,
g.busname as tradeEmplBU,
'' as tradeEmplGroup
from et_accounts a
inner join glb_trades_tmp b on a.accountId=b.accountId and a.accountidtype=b.accountidtype and a.secid=b.secid and a.secidtype=b.secidtype
left outer join glb_securities_xref_pme c on a.secid=c.xrefsecid and (a.secidtype=c.xrefsecidtype or (a.secidtype='ml' and c.xrefsecidtype='MLSECNUM'))
inner join glb_securities_pme d on c.secid=d.secid and (d.expirationdate is null or d.expirationdate>=b.referencedate)
and (d.contractactivatedate is null or d.contractactivatedate<=b.referencedate)
inner join glb_securities_product_codes f on d.issuetype=f.issuetype
inner join busUnitToAcct g on a.accountId=g.accountid and a.accountidtype=g.accountidtype
left outer join glb_gwm_sectors h on g.sector=h.sector
where b.referencedate = (select max(businessdate) from ais_batch_control where feedtype='daily' and statusid=0)
and (b.mlconsultsind<>'Y' or b.mlconsultsind is null)
and
exists
(select *
from glb_routing
where model='ET' and a.workerregioncode=glb_routing.region)
May 22, 2008 at 8:23 pm
What does not working mean? Are you getting an error? No results? Invalid Results?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 22, 2008 at 9:04 pm
Well, it parses just fine for me in SQL2005, so it must be in your data definitions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 23, 2008 at 7:39 am
Sorry guys for incomplete information. Here is the error that we are getting.
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting datetime from character string.
When i do a select * from..... for the same query i get the results back.
May 23, 2008 at 11:18 am
gourab_sahoo (5/23/2008)
Sorry guys for incomplete information. Here is the error that we are getting.Msg 241, Level 16, State 1, Line 3
Conversion failed when converting datetime from character string.
When i do a select * from..... for the same query i get the results back.
What are the data types for the following columns:
expirationdate
referencedate
contractactivatedate
businessdate
One (or more) of the above columns is probably not defined as a datetime, and you have data in the column that cannot be converted to a datetime.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 23, 2008 at 3:39 pm
I altered the date columns wherever it was mentioned varchar to datetime but still no help. Please advise.
May 23, 2008 at 6:03 pm
Not sure I follow you - what did you change?
If you modified the column from a varchar to a datetime, and that column contained invalid date data it should have failed with the same error.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 24, 2008 at 8:27 pm
As I said, the problem is in your data definitions. Please post them if you still want any help.
Here is a link that explains how to get the best results from your posts:http://www.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply