outer join with substring | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

outer join with substring

Hi, I create a query as below: select a.* from
(select a.dstrct_code, a.supplier_no, c.supplier_name, a.inv_no, b.currency_type,
a.po_no, d.currency_type po_currency, a.po_item_no, a.inv_item_no, a.qty_invoiced, a.loc_val_invd,
a.for_val_invd, a.itm_pmt_status, e.curr_qty_p, e.curr_net_pr_p, e.receipt_status,
e.inv_pend_qty_p, e.inv_pend_val_l, e.inv_pend_val_s, e.inv_pend_val_f, e.preq_stk_code,
substring(e.preq_stk_code,1,6) purch_req, substring(e.preq_stk_code,7,3) preq_item_no
from msf26a a, msf260 b, msf200 c, msf220 d, msf221 e
where b.dstrct_code = a.dstrct_code
and b.supplier_no = a.supplier_no
and b.inv_no = a.inv_no
and c.supplier_no = a.supplier_no
and d.po_no =* a.po_no
and e.po_no =* a.po_no
and e.po_item_no =* a.po_item_no) a,
msf231 b
where b.dstrct_code =* a.dstrct_code
and b.preq_no =* a.purch_req
and b.preq_item_no =* a.preq_item_no and then the database gives me an error:
Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted. Sorry, I am a newbie in SQL Server, but I know very well that my query will work in Oracle. How to overcome this problem? Thanks very much before.
Nico.A.
You should get into the habit JOINing your tables in the JOIN condition and not in the WHERE clause. I believe even Oracle supports this today. Especially when doing OUTER JOINs it’s far more reliable. So, I would rewrite your query using LEFT JOINs and see if the problem still exists. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Hi Frank, many thanks… You are absolutely right. I have changed my query as per your suggestion and it works. I will need to change my habit in JOINing then… <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Thanks very much again,<br />Nico.A.
]]>