I wanted only unique rows to be extracted from below SQL statement that is in COBOL program.
Here the inner SQL query AB returns only unique row, however after it is LEFT JOIN with TABLE_B, it returns multiple rows.
Code:
SELECT
A.CLAIM_NO,
,CASE
WHEN LEFT(B.REMARKS,5) > B.CLAIM_NO THEN 'CC'
WHEN RIGHT(B.CLAIM_NO,1) NOT BETWEEN 'A' AND 'T' THEN 'Z'
ELSE 'A'
END
,A.EFF_DATE
,A.STATUS
,:WS-CLAIM-DT-FROM
,:WS-CLAIM-DT-TO
FROM
(SELECT ......) AS AB
JOIN TABLE_A AS A
ON A.CLAIM_NO = AB.CLAIM_NO
LEFT JOIN TABLE_B AS C
ON C.ID_NO = AB.ID_NO
AND C.FUNC_NO = AB.FUNC_NO
ORDER BY
CASE WHEN WS-SORT-ORDER = 'E' THEN A.EFF_DATE END
CASE WHEN WS-SORT-ORDER = 'C' THEN A.CLAIM_NO END
So adding GROUP BY should extract only unique rows. But I am getting error when GROUP BY was added for CASE statement and :WS-CLAIM-DT-FROM (this gets value from online screen).
Can you please help me how to have GROUP BY for CASE statement
Code:
SELECT
A.CLAIM_NO,
,CASE
WHEN LEFT(B.REMARKS,5) > B.CLAIM_NO THEN 'CC'
WHEN RIGHT(B.CLAIM_NO,1) NOT BETWEEN 'A' AND 'T' THEN 'Z'
ELSE 'A'
END
,A.EFF_DATE
,A.STATUS
,:WS-CLAIM-DT-FROM
,:WS-CLAIM-DT-TO
FROM
(SELECT ......) AS AB
JOIN TABLE_A AS A
ON A.CLAIM_NO = AB.CLAIM_NO
LEFT JOIN TABLE_B AS C
ON C.ID_NO = AB.ID_NO
AND C.FUNC_NO = AB.FUNC_NO
GROUP BY
A.CLAIM_NO,
,CASE
WHEN LEFT(B.REMARKS,5) > B.CLAIM_NO THEN 'CC'
WHEN RIGHT(B.CLAIM_NO,1) NOT BETWEEN 'A' AND 'T' THEN 'Z'
ELSE 'A'
END
,A.EFF_DATE
,A.STATUS
,:WS-CLAIM-DT-FROM
,:WS-CLAIM-DT-TO
ORDER BY
CASE WHEN WS-SORT-ORDER = 'E' THEN A.EFF_DATE END
CASE WHEN WS-SORT-ORDER = 'C' THEN A.CLAIM_NO END
The above SQL works good while executed in SPUFI, but not inside COBOL program.
Here the inner SQL query AB returns only unique row, however after it is LEFT JOIN with TABLE_B, it returns multiple rows.
RTFM:
LEFT JOIN selects all records from the left table, and then appends only those records from right table where key(s) do match.
Total output size shall be mandatory the size of the left table (with optional WHERE selection).
Not quite right.
If a left table ( Main Table) has 1 row matching with 5 rows of the right table ( Table used in left join) then 5 rows are returned as a part of the Join. So always a Null handling is recommended for any cols coming out of this right table and distinct or group by is used to remove duplicates.
OPs problem is Group By rule violation because of the CASE statement used in the select. Another solution apart from what I suggested is to try MIN/MAX around CASE and should work.
I have tried moving the entire CASE within "(SELECT ......) AS AB" however I was getting abend as "AS" is invalid. Then I had the entire CASE statement as such in SELECT and also in GROUP BY without AS "AB". This time i am getting Bind error. (SQLCODE=-312 SQLSTATE=42618 )
I have tried moving the entire CASE within "(SELECT ......) AS AB" however I was getting abend as "AS" is invalid.
This is very poor information that you provided.
Post the complete SQL that you have tried and specially"(SELECT ......) AS AB", You probably be doing some syntax errors and only way to know that is when we can see it what you tried than believe what you say.
Quote:
The above SQL works good while executed in SPUFI, but not inside COBOL program.
Out of curiosity, Where is the B table and shouldn't CASE refers to AB instead?
The above SQL works good while executed in SPUFI, but not inside COBOL program.
This is an obvious bullshit.
The given SQL is real garbage; no intention to dig into it.
If such sort of SQL is needed to produce some useful result, then definitely the approach itself must be changed, and/or data structure(s) re-organized, to fit the business requirements.
I have commented out CASE statements in SELECT and GROUP BY and also commented out ORDER BY statement and also commented Host variable :WS-CLAIM-DT-FROM and :WS-CLAIM-DT-TO and it ran fine.
As first step, I just kept the host variables in both SELECT and GROUP By to see whether SQL is running and unfortunately it gave Bidn error with SQL CODE -312 with SQLSTATE 42618
Code:
SELECT
A.CLAIM_NO,
* ,CASE
* WHEN LEFT(B.REMARKS,5) > B.CLAIM_NO THEN 'CC'
* WHEN RIGHT(B.CLAIM_NO,1) NOT BETWEEN 'A' AND 'T' THEN 'Z'
* ELSE 'A'
* END
,A.EFF_DATE
,A.STATUS
,:WS-CLAIM-DT-FROM
,:WS-CLAIM-DT-TO
FROM
(SELECT ......) AS AB
JOIN TABLE_A AS A
ON A.CLAIM_NO = AB.CLAIM_NO
LEFT JOIN TABLE_B AS C
ON C.ID_NO = AB.ID_NO
AND C.FUNC_NO = AB.FUNC_NO
GROUP BY
A.CLAIM_NO,
* ,CASE
* WHEN LEFT(B.REMARKS,5) > B.CLAIM_NO THEN 'CC'
* WHEN RIGHT(B.CLAIM_NO,1) NOT BETWEEN 'A' AND 'T' THEN 'Z'
* ELSE 'A'
* END
,A.EFF_DATE
,A.STATUS
,:WS-CLAIM-DT-FROM
,:WS-CLAIM-DT-TO
* ORDER BY
* CASE WHEN WS-SORT-ORDER = 'E' THEN A.EFF_DATE END
* CASE WHEN WS-SORT-ORDER = 'C' THEN A.CLAIM_NO END
Please advice. I will uncomment CASE statement after this gets fixed.
You still refusing to post complete SQL and specially what is the full query here (SELECT ......) as AB.
Second when you move CASE within this SELECT above ( and once you share that complete sql) tell us the sqlcode. Don't try anything else but only this change.
If you can not do that simple thing then this post will eventually get locked soon as it is going nowhere.
Sorry. Please see the complete SQL with CASE statement also included
Code:
SELECT
A.CLAIM_NO
,CASE
WHEN LEFT(B.REMARKS,5) > B.CLAIM_NO THEN 'CC'
WHEN RIGHT(B.CLAIM_NO,1) NOT BETWEEN 'A' AND 'T' THEN 'Z'
ELSE 'A'
END
,A.EFF_DATE
,A.STATUS
,:WS-CLAIM-DT-FROM
,:WS-CLAIM-DT-TO
FROM
(SELECT B.CLAIM_NO,
B.ID_NO,
B.FUNC_NO
FROM TABLE_A AS B
WHERE B.CD_ANALYST = '01'
AND B.DEPT_NO = :WS-DEPT-NO
GROUP BY B.CLAIM_NO) AS AB
JOIN TABLE_A AS A
ON A.CLAIM_NO = AB.CLAIM_NO
LEFT JOIN TABLE_B AS C
ON C.ID_NO = AB.ID_NO
AND C.FUNC_NO = AB.FUNC_NO
GROUP BY
A.CLAIM_NO,
,CASE
WHEN LEFT(B.REMARKS,5) > B.CLAIM_NO THEN 'CC'
WHEN RIGHT(B.CLAIM_NO,1) NOT BETWEEN 'A' AND 'T' THEN 'Z'
ELSE 'A'
END
,A.EFF_DATE
,A.STATUS
,:WS-CLAIM-DT-FROM
,:WS-CLAIM-DT-TO
* ORDER BY
* CASE WHEN WS-SORT-ORDER = 'E' THEN A.EFF_DATE END
* CASE WHEN WS-SORT-ORDER = 'C' THEN A.CLAIM_NO END
After executing it, I got the SQL code as
SQLCODE=-312
SQLSTATE=42618
Any code which is complex enough (based on the experience of its author) must be debugged step-by-step:
1) Prepare test data with limited number of rows, and eliminate all parts (fields, or whatever) which are not critical for this particular test.
2) Split any complex SQL (or any other code!) into component parts, to be able to run them separately; start from the most "inner" parts of your code.
3) Try to run each elementary part with your test data, and carefully verify all intermediate results, in minor details!
4) If you are satisfied with the results of a "unit test" of #3 above, then add the next smallest possible part of the "complete query" to your "unit test", and carefully repeat #3 with new enhanced code.
5) Don't forget, and don't be lazy verifying all intermediate results, in minor details!
6) When all original code is included into your "unit test", then re-run it with production-size testing data.
7) If you don't like, or don't want, or just cannot follow the steps suggested above, then I would recommend you to change your job, instead of asking the forum to perform your job duties for you.
Your query is in very bad shape and I don't know what are you trying to achieve with this sql. You don't need Left outer Join because none of the columns are being referred in the SELECT from it. Second you are trying to access B. something when you should use AB. something . Basically you had many basic errors in your sql.
I tried to simplify per my understanding and wrote this, try it to see if it works.
Code:
SELECT
Main_Table.CLAIM_NO,
Main_Table.ID_NO,
Main_Table.FUNC_NO,
CASE
WHEN LEFT(Main_Table..REMARKS,5) > Main_Table..CLAIM_NO THEN 'CC'
WHEN RIGHT(Main_Table..CLAIM_NO,1) NOT BETWEEN 'A' AND 'T' THEN 'Z '
ELSE 'A '
END,
Main_Table.EFF_DATE,
Main_Table.STATUS
FROM
(SELECT DISTINCT B.CLAIM_NO,
B.ID_NO,
B.FUNC_NO,
B.REMARKS,
A.EFF_DATE,
A.STATUS,
:WS-CLAIM-DT-FROM as DT_FRM,
:WS-CLAIM-DT-TO as FT_TO
FROM TABLE_A AS B
WHERE B.CD_ANALYST = '01'
AND B.DEPT_NO = :WS-DEPT-NO
AND EXISTS (SELECT 1 from TABLE_A AS A Where A.CLAIM_NO = B.CLAIM_NO)) Main_Table
Thanks Sergekeyn for the suggestion. Will follow that.
Rohit,
Thanks for the revised SQL. I certainly understood that the SQL is in bad shape and it is an existing written SQL years back. The SQL used to be running fine. The inner SELECT returns only 1 row. Since the LEFT JOIN returns multiple row, the total SQL output returns duplicate rows and is an issue. So to remove duplicate rows, I thought just GROUP BY will solve this issue. But GROUP BY is not working correctly for CAST statement and HOST variables.
So as short term fix, if we were able to get this GROUP BY for CASE and Host variable fixed, it will be of great help. Eventually as per your suggestion, I will go ahead and rewrite this existing SQL.
But you must understand that LEFT OUTER JOIN is not needed anymore since there are no columns referenced in the SELECT from it. It does not matter if it’s working and existing as over a period of time that JOIN became obsolete.
Sorry. LEFT JOIN is mandatory since we have one more condition in it SUPP_TYPE IN ('U', 'V')as mentioned in below SQL. I purposefully didn't mention earlier to reduce the SQL complexity. Since you have mentioned above regarding LEFT JOIN neccessity, I just mentioned the condition in below SQL.
Code:
SELECT
A.CLAIM_NO
,CASE
WHEN LEFT(B.REMARKS,5) > B.CLAIM_NO THEN 'CC'
WHEN RIGHT(B.CLAIM_NO,1) NOT BETWEEN 'A' AND 'T' THEN 'Z'
ELSE 'A'
END
,A.EFF_DATE
,A.STATUS
,:WS-CLAIM-DT-FROM
,:WS-CLAIM-DT-TO
FROM
(SELECT B.CLAIM_NO,
B.ID_NO,
B.FUNC_NO
FROM TABLE_A AS B
WHERE B.CD_ANALYST = '01'
AND B.DEPT_NO = :WS-DEPT-NO
GROUP BY B.CLAIM_NO) AS AB
JOIN TABLE_A AS A
ON A.CLAIM_NO = AB.CLAIM_NO
LEFT JOIN TABLE_B AS C
ON C.ID_NO = AB.ID_NO
AND C.FUNC_NO = AB.FUNC_NO
AND C.SUPP_TYPE IN ('U','V')
GROUP BY
A.CLAIM_NO,
,CASE
WHEN LEFT(B.REMARKS,5) > B.CLAIM_NO THEN 'CC'
WHEN RIGHT(B.CLAIM_NO,1) NOT BETWEEN 'A' AND 'T' THEN 'Z'
ELSE 'A'
END
,A.EFF_DATE
,A.STATUS
,:WS-CLAIM-DT-FROM
,:WS-CLAIM-DT-TO
* ORDER BY
* CASE WHEN WS-SORT-ORDER = 'E' THEN A.EFF_DATE END
* CASE WHEN WS-SORT-ORDER = 'C' THEN A.CLAIM_NO END
So it will be appreciated, if you can let me know how we can have GROUP BY for the CASE statement and Host variables declared in SELECT statement.
You need to get the basics cleared, Whatever LEFT JOIN you are doing isn’t impacting the final result set of this sql. If you say it’s mandatory then it must be changed to INNER JOIN.
I also realized that you only need a TABLE A in the entire sql , remove the EXISTS from my sql suggested and run.
If you can not get it understand and done then I am sure the topic will be locked soon as the post isn’t going nowhere because you keep asking the same question when provided answers and explanations repeatedly.
