Rewrite SQL with ANSI for 2005- HELP!!
-
Hello,
This is the original query from SQL Server 2000 version.
select MAIN_CODE, SUB_CODE, NAME,
DAYS=(select INTVALUE from PINF p2
where p2.MAIN_CODE =* p1.MAIN_CODE
and p2.SUB_CODE =* p1.SUB_CODE
and p2.NAME = 'DAYS'),
from pinf p1
where MAIN_CODE = 'RET'
and NAME <> 'ENDS'
How do we translate this to ANSI compliance for SQL 2005? I tried but getting different results from the original.
Thanks in advance.
M.
-
How about showing us the code you wrote in 2005?
-
This don't do it?
SELECT p1.MAIN_CODE, p1.SUB_CODE, p1.NAME, p2.INTVALUE AS DAYS
FROM
pinf p1 LEFT OUTER JOIN pinf p2 ON
(p2.MAIN_CODE = p1.MAIN_CODE AND
p2.SUB_CODE = p1.SUB_CODE AND
p2.NAME = 'DAYS')
WHERE p1.MAIN_CODE = 'RET'
AND p1.NAME <> 'END'
-
Or try this:
select
MAIN_CODE,
SUB_CODE,
NAME,
DAYS = (select
INTVALUE
from
PINF p2
right outer join PINF p3
on (p2.MAIN_CODE =* p3.MAIN_CODE
and p2.SUB_CODE = p3.SUB_CODE
and p2.NAME = 'DAYS'))
from
pinf p1
where
MAIN_CODE = 'RET'
and NAME <> 'ENDS'
-
or:
SELECT P1.MAIN_CODE
,P1.SUB_CODE
,P1.[NAME]
,P2.INTVALUE AS Days
FROM pinf P1
LEFT JOIN pinf P2
ON P1.MAIN_CODE = P2.MAIN_CODE
AND P1.SUB_CODE = P2.SUB_CODE
AND P2.[NAME] = 'DAYS'
WHERE P1.MAIN_CODE = 'RET'
AND P1.[NAME] <> 'ENDS'
[Edit] Sorry Sean, you had already suggested this but I did not notice your post.
-
Thank you very much guys. It worked. Sorry for missing one more piece from original query, which selects one more column.
select MAIN_CODE, SUB_CODE, NAME,
DAYS=(select INTVALUE from PINF p2
where p2.MAIN_CODE =* p1.MAIN_CODE
and p2.SUB_CODE =* p1.SUB_CODE
and p2.NAME = 'DAYS'),
Months = =(select INTVALUE from PINF p2
where p2.MAIN_CODE =* p1.MAIN_CODE
and p2.SUB_CODE =* p1.SUB_CODE
and p2.NAME = 'MONTHS')
from pinf p1
where MAIN_CODE = 'RET'
and NAME <> 'ENDS'
-
Thanks Ken and Sean. My earlier case also worked when I introduced another instance of the pinf table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply