November 12, 2010 at 8:59 am
My left outer join below works in a sql query window just as I would expect however not in my stored proc result.
Let me explain:
1) Here's the stand alone query working fine :
select s.ticket as tick_strat, s.dependent as dep, s.sort_string as sort, ord.ticket, ord.status FROM order_fx ord
LEFT OUTER JOIN order_strategy s
ON s.ticket = ord.ticket
order by ord.ticket
2) Now I try to incorporate this into a stored proc as follows (just a snippet of the proc is posted).
Please note the CASE/WHEN statement which attempts to return LTRIM(STR(s.ticket)) + '(d)'
SELECT
buy_sell=
CASE when ord.buy_sell=1 then 'Buy'
when ord.buy_sell=2 then 'Sell'
END,
ord.amount,
ord.amt_unit,
TICKET=
CASE
WHEN ord.tick_lnk2 !=0 THEN LTRIM(STR(ord.ticket))+ '(o)'
WHEN ord.sub_type = 'LOOP' THEN LTRIM(STR(ord.ticket))+ '(l)'
WHEN ord.tick_lnk1 !=0 and ord.sub_type !='LOOP' THEN LTRIM(STR(ord.ticket)) + '(d)'
WHEN s.ticket <> null THEN LTRIM(STR(s.ticket)) + '(d)'
ELSE LTRIM(STR(ord.ticket))
END,
s.ticket as tick_strat, /* TEST SECTION TO SHOW GROUP STRATEGY !!! */
s.dependent as dep,
s.sort_string as sort,
into #temp
FROM filter_list flst, users u, order_fx ord
LEFT OUTER JOIN order_strategy s /* add order_strategy to query */
ON s.ticket = ord.ticket
WHERE
status not in ('EXECUTED','FILLED')
AND u.client = @client
AND ord.client = @client
------
And my problem is that this line is not getting respected at all :
WHEN s.ticket <> null THEN LTRIM(STR(s.ticket)) + '(d)'
I would expect certain records to come back with a TICKET column value something like "855761(D)"; i.e. the previous three WHEN statements part of that same CASE seem to work fine but not when looking at the "s" alias .
thanks.
Bob
November 12, 2010 at 9:14 am
OK, so what is the question, or where does it fail and how?
November 12, 2010 at 9:22 am
my apologies, as I got distracted and posted before I actually asked the final question.
Note: my outer join is on table name "order_strategy", and I reference it using alias "s". Then in the WHEN clause I want to reference alias "s" ONLY if its record is not NULL.
it doesn't seem to repect that WHEN clause...
November 12, 2010 at 9:24 am
one obvious issue
s.ticket <> null
should be
s.ticket is not null
Which table does status come from?
Do you really want to cross join the other tables?
Cursors never.
DTS - only when needed and never to control.
November 12, 2010 at 10:51 am
Yes you pointed out something interesting : "status" column comes from order_fx . I suppose that should be qualfied with the "ord" alias to be consistent.
And yes, I would like to cross join the ORDER_STRATEGY table to see I there are special links between ticket numbers ? Hence if a ticket# exists in both Order_fx AND Order_Strategy, I want to account for that.
November 13, 2010 at 2:43 pm
bob mazzo (11/12/2010)
My left outer join below works in a sql query window just as I would expect however not in my stored proc result.Let me explain:
1) Here's the stand alone query working fine :
select s.ticket as tick_strat, s.dependent as dep, s.sort_string as sort, ord.ticket, ord.status FROM order_fx ord
LEFT OUTER JOIN order_strategy s
ON s.ticket = ord.ticket
order by ord.ticket
...
It's completely different to the query you're referencing in the stored procedure. Test us much as you can in a SSMS window before committing to a sproc. Often all you have to do (to test it once it's in a sproc) is comment out the TRY-CATCH block and convert the parameters to a DECLARE.
bob mazzo (11/12/2010)
...FROM filter_list flst, users u, order_fx ord
LEFT OUTER JOIN order_strategy s /* add order_strategy to query */
ON s.ticket = ord.ticket
WHERE
status not in ('EXECUTED','FILLED')
AND u.client = @client
AND ord.client = @client
....
Old-style joins (FROM table1, table2 WHERE table2.column=table1.column) will be deprecated in a future version of SQL Server. Mixing old-style joins with explicit JOINs is never a good idea. Change to explicit JOIN syntax throughout:
FROM filter_list flst
CROSS JOIN (SELECT <<whatever>>
FROM users u
INNER JOIN order_fx ord ON ord.client = u.client
LEFT OUTER JOIN order_strategy s ON s.ticket = ord.ticket
WHERE ord.client = @client)
Or starting with your original query:
SELECT s.ticket as tick_strat, s.dependent as dep, s.sort_string as sort, ord.ticket, ord.status
FROM order_fx ord
LEFT OUTER JOIN order_strategy s ON s.ticket = ord.ticket
INNER JOIN users u ON u.client = ord.client
WHERE ord.client = @client
- does this work as it should? If so, time to figure out what type of join to use for filter_list.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply