-1

I have some conditions in where condition where I am trying to join the tables with different fields based on some conditions . But its not allowing. Is there an alternate way to get the result . Can any of you help me resolve this.

 select s1.Seq1, s1.Seq2, s1.Transf_Date, s1.Place_Name, s1.Place_Code, '0' as Completion_Flag, s1.Flag from ( select case when T.STOCK_TRANSF_SEQ > 0 then T.STOCK_TRANSF_SEQ else T.PO_SEQ end as Seq1, case when T.STOCK_TRANSF_SEQ > 0 then T.STOCK_TRANSF_SEQ2 else T.PO_SEQ2 end as Seq2, case when T.STOCK_TRANSF_SEQ > 0 then '0' else '1' end as Flag, T.STOCK_TRANSF_DATE as Transf_Date, min(T.WHIN_PLACE_CD) as Place_Code, min(C.CUSTOMER_NAME1) as Place_Name from T_TRANSFER_ORDER t join M_CUSTOMER c ON t.WHIN_PLACE_CD = c.CUSTOMER_CODE and C.PLACE_F = '1' and C.DELETE_FLAG = 0 and C.OWN_WH = '1' and C.WAREHOUSE_CODE = '10' join M_CUSTOMER C2 on T.WHOUT_PLACE_CD = C2.CUSTOMER_CODE where (T.STOCK_TRANSF_SEQ > 0 or T.PO_SEQ > 0) and T.WHOUT_PLACE_CD = '002001000' group by T.STOCK_TRANSF_SEQ, T.STOCK_TRANSF_SEQ2, t.PO_SEQ,T.PO_SEQ2, t.STOCK_TRANSF_DATE having min(t.RG_FIN_F) = 0 ) s1 join ( select res.Seq1, res.Seq2, case when res.CNT= res.CNT2 then 1 else 0 end AS Completion_Flag from ( select case when T.STOCK_TRANSF_SEQ > 0 then T.STOCK_TRANSF_SEQ else T.PO_SEQ end as Seq1, case when T.STOCK_TRANSF_SEQ > 0 then T.STOCK_TRANSF_SEQ2 else T.PO_SEQ2 end as Seq2, count(*) CNT, (select count(DISTINCT ITEM_CD) from T_TRANSFER_RESULT where case when T.STOCK_TRANSF_SEQ > 0 then (STOCK_TRANSF_SEQ = T.STOCK_TRANSF_SEQ AND STOCK_TRANSF_SEQ2 = T.STOCK_TRANSF_SEQ2) ELSE( PO_SEQ = T.PO_SEQ AND PO_SEQ2 = T.PO_SEQ2) end and OUTPUT_FLAG = '1' AND DELETE_FLAG = 0) as CNT2 from T_TRANSFER_ORDER t group BY t.STOCK_TRANSF_SEQ, t.STOCK_TRANSF_SEQ2, t.PO_SEQ, t.PO_SEQ2 ) res ) s2 on s1.Seq1 = s2.Seq1 and s1.Seq2 = s2.Seq2 and s2.Completion_Flag = 0 order BY s1.Transf_Date
Improve this question
2
  • Its giving me ORA-00907: missing right parenthesis. But there is no problem with parathesis. Commented Feb 27, 2018 at 8:20
  • Next time you feel the need to post a vast chunk of code please take the time to remove all the unnecessary tabs so that it is readable without scrolling. Commented Feb 27, 2018 at 15:33

2 Answers 2

Reset to default
0

I changed the structure Its working primafacie 

select s1.Seq1, s1.Seq2, s1.Transf_Date, s1.Place_Name, s1.Place_Code, '0' as Completion_Flag, s1.Flag from ( select case when T.STOCK_TRANSF_SEQ > 0 then T.STOCK_TRANSF_SEQ else T.PO_SEQ end as Seq1, case when T.STOCK_TRANSF_SEQ > 0 then T.STOCK_TRANSF_SEQ2 else T.PO_SEQ2 end as Seq2, case when T.STOCK_TRANSF_SEQ > 0 then '0' else '1' end as Flag, T.STOCK_TRANSF_DATE as Transf_Date, min(T.WHIN_PLACE_CD) as Place_Code, min(C.CUSTOMER_NAME1) as Place_Name from T_TRANSFER_ORDER t join M_CUSTOMER c ON t.WHIN_PLACE_CD = c.CUSTOMER_CODE and C.PLACE_F = '1' and C.DELETE_FLAG = 0 and C.OWN_WH = '1' and C.WAREHOUSE_CODE = '10' join M_CUSTOMER C2 on T.WHOUT_PLACE_CD = C2.CUSTOMER_CODE where (T.STOCK_TRANSF_SEQ > 0 or T.PO_SEQ > 0) and T.WHOUT_PLACE_CD = '002001000' group by T.STOCK_TRANSF_SEQ, T.STOCK_TRANSF_SEQ2, t.PO_SEQ,T.PO_SEQ2, t.STOCK_TRANSF_DATE having min(t.RG_FIN_F) = 0 ) s1 join ( select res.Seq1, res.Seq2, case when res.CNT= res.CNT2 then 1 else 0 end AS Completion_Flag from ( select case when T.STOCK_TRANSF_SEQ > 0 then T.STOCK_TRANSF_SEQ else T.PO_SEQ end as Seq1, case when T.STOCK_TRANSF_SEQ > 0 then T.STOCK_TRANSF_SEQ2 else T.PO_SEQ2 end as Seq2, count(*) CNT, (select count(DISTINCT ITEM_CD) from T_TRANSFER_RESULT where ((T.STOCK_TRANSF_SEQ > 0 AND STOCK_TRANSF_SEQ = T.STOCK_TRANSF_SEQ AND STOCK_TRANSF_SEQ2 = T.STOCK_TRANSF_SEQ2 ) OR (T.STOCK_TRANSF_SEQ = 0 AND PO_SEQ = T.PO_SEQ AND PO_SEQ2 = T.PO_SEQ2)) and OUTPUT_FLAG = '1' AND DELETE_FLAG = 0) as CNT2 from T_TRANSFER_ORDER t group BY t.STOCK_TRANSF_SEQ, t.STOCK_TRANSF_SEQ2, t.PO_SEQ, t.PO_SEQ2 ) res ) s2 on s1.Seq1 = s2.Seq1 and s1.Seq2 = s2.Seq2 and s2.Completion_Flag = 0 order BY s1.Transf_Date
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

The issue seems to be in the usage of CASE in the WHERE predicates as below.

select count(DISTINCT ITEM_CD) from T_TRANSFER_RESULT where case when T.STOCK_TRANSF_SEQ > 0 then (STOCK_TRANSF_SEQ = T.STOCK_TRANSF_SEQ AND STOCK_TRANSF_SEQ2 = T.STOCK_TRANSF_SEQ2) ELSE (PO_SEQ = T.PO_SEQ AND PO_SEQ2 = T.PO_SEQ2) end and OUTPUT_FLAG = '1' AND DELETE_FLAG = 0) as CNT2; select * from mytab;

Output:

A B 1 Balaji 2 Vanchi

Incorrect way:

select * from mytab where case when a = 1 then b = 'Balaji' else b = 'Vanchi' end and a > 0;

Output:

ORA-00905: missing keyword

Correct way:

select * from mytab where b = (case when a = 1 then 'Balaji' else 'Vanchi' end) and a > 0;

Output:

A B 1 Balaji 2 Vanchi
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.