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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy