IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

COBOL -DB2 SQL code to have GROUP BY for CASE statement


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Wed Oct 23, 2019 7:00 pm
Reply with quote

Hi All,

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.

Thanks
Vinu
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Oct 24, 2019 12:02 am
Reply with quote

How about moving the entire CASE within "(SELECT ......) AS AB" and use this value in outer Select to which GROUP BY is applied?
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Thu Oct 24, 2019 1:55 am
Reply with quote

vinu78 wrote:
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).
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Oct 24, 2019 3:53 am
Reply with quote

sergeyken wrote:
vinu78 wrote:
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.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Thu Oct 24, 2019 1:00 pm
Reply with quote

Hi Rohit,

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 )

Is there any other way to achieve this

Thanks
Vinu
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Oct 24, 2019 8:05 pm
Reply with quote

Quote:
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?
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Thu Oct 24, 2019 9:50 pm
Reply with quote

vinu78 wrote:
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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Oct 25, 2019 2:25 am
Reply with quote

While you answer my question and post the comple sql, try this and let us know
Quote:

"Another solution apart from what I suggested is to try MIN/MAX around CASE and should work."
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Tue Oct 29, 2019 4:32 pm
Reply with quote

Hi Rohit,

Can you please help me with the MIN/MAX for CASE statement syntax. I have tried and was not able to get it right

Thanks
Vinu
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Oct 29, 2019 7:56 pm
Reply with quote

Quote:
Can you please help me with the MIN/MAX for CASE statement syntax. I have tried and was not able to get it right

What is the SQLCODE? I can't help you if you don't post the complete SQL you tried and keep overlooking what is being asked repeatedly.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Wed Oct 30, 2019 5:55 pm
Reply with quote

Hi Rohit,

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.

Thanks
Vinu
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel

PostPosted: Wed Oct 30, 2019 6:10 pm
Reply with quote

vinu78 wrote:
Code:
SELECT
      A.CLAIM_NO,
     ,CASE


Code:
SELECT
      A.CLAIM_NO,
     ,CASE

GROUP BY
     A.CLAIM_NO,
     ,CASE


Is that a typo ???
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Wed Oct 30, 2019 6:33 pm
Reply with quote

Sorry. Yes thatz a typo.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Nov 01, 2019 2:42 am
Reply with quote

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.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Fri Nov 01, 2019 5:03 pm
Reply with quote

Rohit,

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

Thanks
Vinu
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Fri Nov 01, 2019 11:20 pm
Reply with quote

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.

Good luck.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sat Nov 02, 2019 12:00 am
Reply with quote

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
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Sun Nov 03, 2019 10:25 am
Reply with quote

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.

Thanks
Vinu
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sun Nov 03, 2019 10:41 am
Reply with quote

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.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Sun Nov 03, 2019 5:32 pm
Reply with quote

Rohit,

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.


Thanks
Vinu
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sun Nov 03, 2019 7:20 pm
Reply with quote

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.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Mon Nov 11, 2019 5:43 pm
Reply with quote

Thanks Rohit. I am trying that.

Regards
Vinu
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Replace each space in cobol string wi... COBOL Programming 2
No new posts COBOL -Linkage Section-Case Sensitive COBOL Programming 1
No new posts run rexx code with jcl CLIST & REXX 15
No new posts COBOL ZOS Web Enablement Toolkit HTTP... COBOL Programming 0
No new posts Compile rexx code with jcl CLIST & REXX 6
Search our Forums:

Back to Top