replacing all code problem
-
Hello group !
Can you help me to resolve this issue? After I ran SQL Server 2005 upgrade advisor I found out that I need to fix old join syntax ( *= or =*). Here is where problem started .I have to views . One view included in another view.After I fixed first view replacing *= with left outer join the second one stopped workinq. Error message is "Joined tables cannot be specified in a query containing outer join operators.View or function 'sf_po_xref_vw' contains joined tables".
View that causing the problem is like this :
CREATE VIEW sf_po_xref_vw
AS
SELECT
ltrim(rtrim(ISNULL(CASE WHEN LEFT(order_number, 2) = '00'
THEN CONVERT(varchar, CONVERT(integer, order_number))
ELSE order_number END,''))) + ltrim(rtrim(ISNULL((right(h1.po_no,11)),''))) 'old_po',
p.po_no 'new_po',
ltrim(rtrim(ISNULL(invoice_number,'')+ISNULL(h1.prc_no,''))) 'Invoice_no'
FROM
NIKON..H41ITEM h,
purchase p,
dbo.H91DATA h1
WHERE p.vend_inv_no *= h.invoice_number AND p.vend_inv_no *= h1.prc_no
AND ltrim(rtrim(ISNULL(CASE WHEN LEFT(order_number, 2) = '00'
THEN CONVERT(varchar, CONVERT(integer, order_number))
ELSE order_number END,''))) + ltrim(rtrim(ISNULL((right(h1.po_no,11)),''))) <> ''
GROUP BY
ltrim(rtrim(ISNULL(CASE WHEN LEFT(order_number, 2) = '00'
THEN CONVERT(varchar, CONVERT(integer, order_number))
ELSE order_number END,''))) + ltrim(rtrim(ISNULL((right(h1.po_no,11)),''))),p.po_no,
ltrim(rtrim(ISNULL(invoice_number,'')+ISNULL(h1.prc_no,'')))
And I changed it to
CREATE VIEW sf_po_xref_vw
AS
SELECT
ltrim(rtrim(ISNULL(CASE WHEN LEFT(h.order_number, 2) = '00'
THEN CONVERT(varchar, CONVERT(integer,h.order_number))
ELSE h.order_number END,''))) + ltrim(rtrim(ISNULL((right(h1.po_no,11)),''))) 'old_po',
p.po_no 'new_po',
ltrim(rtrim(ISNULL(h.invoice_number,'')+ISNULL(h1.prc_no,''))) 'Invoice_no'
FROM
purchase p LEFT OUTER JOIN H91DATA h1 ON p.vend_inv_no = h1.prc_no
LEFT OUTER JOIN NIKON..H41ITEM h
ON p.vend_inv_no=h.invoice_number AND
ltrim(rtrim(ISNULL(CASE WHEN LEFT(h.order_number, 2) = '00'
THEN CONVERT(varchar,CONVERT(integer,h.order_number))
ELSE h.order_number END,''))) + ltrim(rtrim(ISNULL((right(h1.po_no,11)),''))) <> ''
GROUP BY
ltrim(rtrim(ISNULL(CASE WHEN LEFT(h.order_number, 2) = '00'
THEN CONVERT(varchar, CONVERT(integer,h.order_number))
ELSE h.order_number END,''))) + ltrim(rtrim(ISNULL((right(h1.po_no,11)),''))),p.po_no,
ltrim(rtrim(ISNULL(h.invoice_number,'')+ISNULL(h1.prc_no,'')))
The second view I am having problem with looks like this:
CREATE VIEW sf_poremain_report_vw
AS
SELECT
p.po_no 'old_po',
x.new_po,
p.vendor_no,
l.rel_date 'old_date',
l.part_no,
l.vend_sku,
l.qty_ordered 'orig_qty',
ISNULL( (
CASE WHEN LEFT(x.invoice_no, 2) <> 'RP'
THEN
( SELECT
max(CONVERT(integer, h.quantity) )
FROM
NIKON..H41ITEM h,
purchase p2
WHERE
h.invoice_number = x.invoice_no
AND
h.item_code = l.vend_sku
AND
h.order_number LIKE '%' + p.po_no + '%'
AND
h.invoice_number = p2.vend_inv_no
AND
p2.status = 'C'
)
ELSE
( SELECT
CONVERT(integer, h.qty)
FROM
NIKON..H91DATA h,
purchase p2
WHERE
h.prc_no = x.invoice_no
AND
RTRIM(h.parts_no) + '/' + RTRIM(h.product_no) = l.part_no
AND
h.po_no LIKE '%' + p.po_no + '%'
AND
h.prc_no = p2.vend_inv_no
AND
p2.status = 'C'
)
END ) ,0 ) 'new_qty',
( SELECT TOP 1
CONVERT(datetime, shipping_schedule_date)
FROM
sf_poremain_h31_vw h
WHERE
p.po_no = h.order_number
AND
l.vend_sku = h.item_code) 'h31_date',
CASE WHEN LEFT(x.invoice_no, 2) <> 'RP'
THEN
( SELECT TOP 1
n.rel_date
FROM
pur_list n
WHERE
n.po_no = x.new_po
AND
n.part_no = l.part_no)
ELSE
( SELECT TOP 1
h.shipping_date
FROM
NIKON..H91DATA h
WHERE
h.prc_no = x.invoice_no
AND
RTRIM(h.parts_no) + '/' + RTRIM(h.product_no) = l.part_no
AND
h.po_no LIKE '%' + p.po_no + '%'
)
END 'new_date',
CASE WHEN LEFT(x.invoice_no, 2) <> 'RP'
THEN
( SELECT
max(CONVERT(integer, h.quantity) )
FROM
NIKON..H41ITEM h
WHERE
h.invoice_number =* x.invoice_no
AND
h.item_code =* l.vend_sku
AND
h.order_number LIKE '%' + p.po_no + '%')
ELSE
( SELECT
CONVERT(integer, h.qty)
FROM
NIKON..H91DATA h
WHERE
h.prc_no = x.invoice_no
AND
RTRIM(h.parts_no) + '/' + RTRIM(h.product_no) = l.part_no
AND
h.po_no LIKE '%' + p.po_no + '%'
)
END 'new_shp'
FROM
purchase p,
pur_list l,
sf_po_xref_vw x
WHERE (p.vendor_no like 'NIKCORP%' or p.vendor_no like 'NIKVIS%') AND
p.po_no = l.po_no
AND
p.po_no *= x.old_po
AND
p.po_type NOT IN ( '41', 'NO' )
AND
(p.void = 'V' OR p.status = 'O')
Mark Gorelik
-
I'm not sure what's easier: parse such queries or build a new, properly designed, database.
_____________
Code for TallyGenerator -
You must be consistent and clean out all old legacy syntax everywhere you still have it.
The error comes when you have a view written in ANSI style (which is good), and you join to this view with old legacy syntax. The solution is to use ANSI everywhere.
CREATE VIEW v1
AS
SELECT foo
FROM bar
LEFT JOIN foobar
ON foo = bar
go
Now, if you were to reference view v1 which is written with a ANSI join with a statement that uses a legacy outer join, the error will occur.
SELECT *
FROM v1, foobar
WHERE foo *= bar
Server: Msg 4424, Level 16, State 1, Line 1
Joined tables cannot be specified in a query containing outer join operators. View or function 'v1' contains joined tables.
The solution is to not use legacy joins at all...
SELECT *
FROM v1
LEFT JOIN foobar
WHERE foo = bar
/Kenneth
-
Hello Kenneth ! Thanks for looking into this issue. I discovered this problem when I actually applied the same syntax in the second view.
FROM
purchase p,
pur_list l,
sf_po_xref_vw x
WHERE (p.vendor_no like 'NIKCORP%' or p.vendor_no like 'NIKVIS%') AND
p.po_no = l.po_no
AND
p.po_no *= x.old_po
AND
p.po_type NOT IN ( '41', 'NO' )
AND
(p.void = 'V' OR p.status = 'O')
Any way , I will try it again.Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply