July 12, 2007 at 8:20 am
I've got an in-house application that we've recently transferred from Oracle 9i to SQL 2005.
Can anybody translate this?
(SELECT
distinct FISCAL_YEAR, ACCOUNTING_PERIOD, AMOUNT, DESCR, REPORT_ITEM, REPORT_NAME, 'BUDGET' ACCOUNTING_TYPE
FROM
View3 WHERE (FISCAL_YEAR, ACCOUNTING_PERIOD, DESCR, REPORT_ITEM, REPORT_NAME) NOT IN
(SELECT
DISTINCT A.YEAR, A.MONTH_NBR, A.PRODUCT, B.REPORT_ITEM, B.REPORT_NAME
FROM
View4 A, BAccounts B WHERE A.ACCOUNT = B.ACCOUNT) )
Any Help Appreciated
July 12, 2007 at 8:42 am
I took a quick glance at the query at looks like it should work. Can you parse the command and give us the error, or don't you have a SQL Server database yet?
Thanks,
Eric
July 12, 2007 at 8:42 am
See which of the following is better for your data:
-- 1. Probably not very efficient due to join in sub-query
SELECT DISTINCT -- distinct may not be needed
V.FISCAL_YEAR
,V.ACCOUNTING_PERIOD
,V.AMOUNT
,V.DESCR
,V.REPORT_ITEM
,V.REPORT_NAME
,'BUDGET' AS ACCOUNTING_TYPE
FROM View3 V
WHERE NOT EXISTS (
SELECT *
FROM View4 A
JOIN BAccounts B
ON A.ACCOUNT = B.ACCOUNT
WHERE A.[YEAR] = V.FISCAL_YEAR
AND A.MONTH_NBR = V.ACCOUNTING_PERIOD
AND A.PRODUCT = V.DESCR
AND B.REPORT_ITEM = V.REPORT_ITEM
AND B.REPORT_NAME = V.REPORT_NAME
)
-- 2. Exists with no join in sub-query
SELECT DISTINCT -- distinct may not be needed
V.FISCAL_YEAR
,V.ACCOUNTING_PERIOD
,V.AMOUNT
,V.DESCR
,V.REPORT_ITEM
,V.REPORT_NAME
,'BUDGET' AS ACCOUNTING_TYPE
FROM View3 V
WHERE NOT EXISTS (
SELECT *
FROM View4 A
WHERE A.[YEAR] = V.FISCAL_YEAR
AND A.MONTH_NBR = V.ACCOUNTING_PERIOD
AND A.PRODUCT = V.DESCR
AND EXISTS (
SELECT *
FROM BAccounts B
WHERE A.ACCOUNT = B.ACCOUNT
AND B.REPORT_ITEM = V.REPORT_ITEM
AND B.REPORT_NAME = V.REPORT_NAME
)
)
-- 3. Outer Join method
SELECT DISTINCT -- distinct may not be needed
V.FISCAL_YEAR
,V.ACCOUNTING_PERIOD
,V.AMOUNT
,V.DESCR
,V.REPORT_ITEM
,V.REPORT_NAME
,'BUDGET' AS ACCOUNTING_TYPE
FROM View4 A
JOIN BAccounts B
ON A.ACCOUNT = B.ACCOUNT
RIGHT JOIN View3 V
ON A.[YEAR] = V.FISCAL_YEAR
AND A.MONTH_NBR = V.ACCOUNTING_PERIOD
AND A.PRODUCT = V.DESCR
AND B.REPORT_ITEM = V.REPORT_ITEM
AND B.REPORT_NAME = V.REPORT_NAME
WHERE A.ACCOUNT IS NULL
[Edit:] If your views contain joins you may find it quicker to specify the underlying tables.
July 12, 2007 at 9:02 am
Thank you for replying so fast.
No errors during parsing.
Ken I'm using the second method and i'm going to use underlying tables since we've cleaned up most of the tables.
Query executes in less than a second.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply