General information
Title:
Problem using LEFT OUTER JOIN and alias to Dynamic Table
Hi,
The following queries on LEFT OUTER JOIN and Alias to Dynamic Table is not working as per our expectation
with the Sybase Ver 11.1 running on Windows 'NT , but the same works fine on Oracle and SQL Server Databases.So
Pls. go thru. the queries and do let us know if any changes or fine tune had to be made to work fine.
1. LEFT OUTER JOIN
SELECT rh.USER_TPK,hq.SEQUENCE_NO,hq.QUES_TPK,hr.ANSWER,rh.TPK,
(SELECT PICKLIST_OPTION FROM PICKLIST WHERE HEAQ_TPK =(SELECT TPK FROM HEADER_QUESTIONS WHERE QUES_TPK=
'1_1' AND HEAD_QUES_ID = (SELECT F.HEAD_QUES_ID FROM HEADER_QUESTIONS F,PICKLIST G WHERE G.TPK = p.TPK
AND G.HEAQ_TPK = F.TPK) ) AND PICK_ID = (SELECT PICK_ID FROM PICKLIST WHERE TPK=p.TPK))
FROM RESPONSES_HEADER rh, HEADER_RESPONSE hr LEFT OUTER JOIN PICKLIST p ON hr.PICK_TPK = p.TPK
AND hr.HEAQ_TPK = p.HEAQ_TPK, HEADER_QUESTIONS hq WHERE rh.TPK IN ('1_2','1_3') AND hr.RESH_TPK
= rh.TPK AND
hq.TPK = hr.HEAQ_TPK
2. Alias to dynamic table
SELECT E.TPK,D.TPK,C.TPK FROM NODE_TABLE A,ANSWER_LABELS B,
(SELECT TPK,ANSL_TPK,NODT_TPK FROM GRADED_RESPONSE WHERE RESH_TPK = '1_1') C,
NODE_TABLE D,ANSWER_LABELS E,ANSWER_STYLES F WHERE C.ANSL_TPK=B.TPK AND C.NODT_TPK=A.TPK AND B.ANSWER_ID=E.ANSWER_ID
AND E.ANSS_TPK=F.TPK AND F.QUES_TPK='1_1' AND A.NODE_ID=D.NODE_ID AND D.QUES_TPK='1_1' UNION SELECT
'xxx',D.TPK,C.TPK FROM NODE_TABLE A,(SELECT TPK, ANSL_TPK,NODT_TPK FROM GRADED_RESPONSE WHERE RESH_TPK
='1_1' AND ANSL_TPK IS NULL)C,NODE_TABLE D WHERE C.NODT_TPK=A.TPK AND A.NODE_ID=D.NODE_ID AND D.QUES_TPK='1_2'
Awaiting for ur reply at the earliest.
Thanks in Advance,
Siraj
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only