Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Multiple rows within Cursor when Count(*) > 1

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
iamdijkstra007

New User


Joined: 07 Jun 2020
Posts: 3
Location: USA

PostPosted: Mon Jun 08, 2020 12:53 am    Post subject: Multiple rows within Cursor when Count(*) > 1
Reply with quote

Hi,

I am trying to code this below scenario, and unable to get the results efficiently.

1) for certain ws-key1, query is retrieving more than one record, need "all" those records to be written to exception file and read next record in input file
2) for most records query retrieves one record and then that needs to be processed through rest of the program

Code:

select col1,col2,col3,col4,col5
from   tableA
where col1 = ws-key1 and
          col2 = ws-key2 and
          col3 = ws-key3


This query retrieves 2 records for first case; and I need both those records to be written to Exception file. Col4 and col5 will have different data in this case.
And when it retrieves 1 record, process the record and continue program.

I tried below, but count(*) is returning 1, so will not go into exception para even though 2 records are fetched.

Code:
select col1,col2,col3,col4,col5,count(*)
from   tableA
where col1 = ws-key1 and
          col2 = ws-key2 and
          col3 = ws-key3
group by col1,col2,col3,col4,col5

if host variable count(*) > 1
         write exception file
else
     continue
end-if


I can code a singleton select query for cont(*) and then if count(*) > 1 perform Cursor-para to write exception until end of record flag.
Else, proceed with Select-para to retrieve required columns to Continue with program processing.

Also, if I code this in singleton select and handle -811, it only writes first record to exception and not both records.(as you might have guessed)

But this is not efficient as most of you already know.

Please guide if you are able to.

Thank you.
Back to top
View user's profile Send private message

sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 860
Location: Maryland

PostPosted: Mon Jun 08, 2020 1:20 am    Post subject: Re: Multiple rows within Cursor when Count(*) > 1
Reply with quote

Code:
select *
from (
   select col1,col2,col3,col4,col5,count(*) total
   from   tableA
   where col1 = ws-key1 and
          col2 = ws-key2 and
          col3 = ws-key3
   group by col1,col2,col3,col4,col5
)
where total > 1 ;
Back to top
View user's profile Send private message
iamdijkstra007

New User


Joined: 07 Jun 2020
Posts: 3
Location: USA

PostPosted: Mon Jun 08, 2020 3:52 am    Post subject: Re: Multiple rows within Cursor when Count(*) > 1
Reply with quote

sergeyken wrote:
Code:
select *
from (
   select col1,col2,col3,col4,col5,count(*) total
   from   tableA
   where col1 = ws-key1 and
          col2 = ws-key2 and
          col3 = ws-key3
   group by col1,col2,col3,col4,col5
)
where total > 1 ;

Thank you sergyken for taking time to respond. Appreciate it.

I tried this in a sql client using my table and corresponding column/keys, but unfortunately returning SQLCODE=-4743

-4743 ATTEMPT TO USE NEW FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL
Explanation
An application attempted to use a Db2 feature which is not supported by the current application compatibility setting. Db2 environments in conversion-mode can have an application compatibility value that is set to previous versions. Db2 subsystems that are in new-function mode can have an application compatibility level that is set to either the current version or previous versions.


Code:
select col1,col2,col3,col4,col5,count(*) total
   from   tableA
   where col1 = ws-key1 and
          col2 = ws-key2 and
          col3 = ws-key3
   group by col1,col2,col3,col4,col5


Above query is giving results, but if I add select * as mentioned it is throwing error.

col1 col2 col3 col4 col5 total
ws-key1 ws-key2 ws-key3 abc xyz 1
ws-key1 ws-key2 ws-key3 abc uvw 1
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2419
Location: Hampshire, UK

PostPosted: Mon Jun 08, 2020 1:08 pm    Post subject: Reply to: Multiple rows within Cursor when Count(*) > 1
Reply with quote

I doubt it is throwing an error as errors are not thrown on the mainframe.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2487
Location: NY,USA

PostPosted: Mon Jun 08, 2020 3:00 pm    Post subject:
Reply with quote

Why don’t you declare a cursor on first select ( with simple where condition) and load the data into an array until sqlcode=+100 , and keep the counter updates for every fetch within it.

Code:
If the counter =0 - process next record
If the counter =1 - process the current record
If the counter >1 - write to exception
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 860
Location: Maryland

PostPosted: Mon Jun 08, 2020 5:00 pm    Post subject: Re: Multiple rows within Cursor when Count(*) > 1
Reply with quote

iamdijkstra007 wrote:
-4743 ATTEMPT TO USE NEW FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL

You need to raise this issue with your DBA. Your support would need to update installation settings/options, and/or would suggest you to add the statement like this
Code:
SET CURRENT APPLICATION COMPATIBILITY = ‘V11R1’ ;

This is local problem to be resolved by your support team. The Forum likely cannot help.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2487
Location: NY,USA

PostPosted: Mon Jun 08, 2020 6:28 pm    Post subject: Re: Multiple rows within Cursor when Count(*) > 1
Reply with quote

sergeyken wrote:
Code:
select *
from (
   select col1,col2,col3,col4,col5,count(*) total
   from   tableA
   where col1 = ws-key1 and
          col2 = ws-key2 and
          col3 = ws-key3
   group by col1,col2,col3,col4,col5
)
where total > 1 ;

Total =1 population is missing and they still need to be processed?
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 860
Location: Maryland

PostPosted: Mon Jun 08, 2020 7:13 pm    Post subject: Re: Multiple rows within Cursor when Count(*) > 1
Reply with quote

Rohit Umarjikar wrote:
Total =1 population is missing and they still need to be processed?


It's up to the TS's decision.
I hope he should be able to adjust accordingly.
Back to top
View user's profile Send private message
iamdijkstra007

New User


Joined: 07 Jun 2020
Posts: 3
Location: USA

PostPosted: Mon Jun 08, 2020 7:23 pm    Post subject:
Reply with quote

Rohit Umarjikar wrote:
Why don’t you declare a cursor on first select ( with simple where condition) and load the data into an array until sqlcode=+100 , and keep the counter updates for every fetch within it.

Code:
If the counter =0 - process next record
If the counter =1 - process the current record
If the counter >1 - write to exception

Thank you Rohit, yes this is one of the option I had considered and would work as you mentioned. But wanted to avoid internal array and handle all this in one query.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2487
Location: NY,USA

PostPosted: Tue Jun 09, 2020 4:51 am    Post subject: Re: Multiple rows within Cursor when Count(*) > 1
Reply with quote

sergeyken wrote:
Rohit Umarjikar wrote:
Total =1 population is missing and they still need to be processed?


It's up to the TS's decision.
I hope he should be able to adjust accordingly.

Actually TS needs both the conditions and needs a UNION but then so be it. icon_smile.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2487
Location: NY,USA

PostPosted: Tue Jun 09, 2020 4:52 am    Post subject:
Reply with quote

iamdijkstra007 wrote:
Rohit Umarjikar wrote:
Why don’t you declare a cursor on first select ( with simple where condition) and load the data into an array until sqlcode=+100 , and keep the counter updates for every fetch within it.

Code:
If the counter =0 - process next record
If the counter =1 - process the current record
If the counter >1 - write to exception

Thank you Rohit, yes this is one of the option I had considered and would work as you mentioned. But wanted to avoid internal array and handle all this in one query.

You can't do this in one query since you expect to have duplicates. This seems to be optimized way and single query and no downsides.
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 860
Location: Maryland

PostPosted: Tue Jun 09, 2020 5:41 pm    Post subject: Re: Multiple rows within Cursor when Count(*) > 1
Reply with quote

Rohit Umarjikar wrote:
Actually TS needs both the conditions and needs a UNION but then so be it. icon_smile.gif


This should not be UNION...
Code:
select A.col1, A.col2, A.col3, A.col4, A.col5, I.total
from (
    select col1, col2, col3, col4, col5, count(*) total
    from   tableA
    where col1 = ws-key1 
      and col2 = ws-key2 
      and col3 = ws-key3
    group by col1, col2, col3, col4, col5
    )                  I,
    tableA             A   
where I.total > 1
  and I.col1 = A.col1
  and I.col2 = A.col2
  and I.col3 = A.col3
  and I.col4 = A.col4
  and I.col5 = A.col5
;
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2487
Location: NY,USA

PostPosted: Tue Jun 09, 2020 8:32 pm    Post subject:
Reply with quote

This will create performance issues if the underlying data base is huge , second, you are using same table twice which means one-many joins on the way out, Third Union could do the same but in better way.

But instead of making a complex query and may run into performance issues or more elapsed times, why not to simple use the above suggested method, simple and clean.

This is by UNION ( I wouldn't suggest it to use unless going against a small database)

Code:
select col1,col2,col3,col4,col5,count(*) total, 'S' as recs
   from   tableA
   where col1 = ws-key1 and
          col2 = ws-key2 and
          col3 = ws-key3
   group by col1,col2,col3,col4,col5,recs
   having count(*) = 1
UNION
select col1,col2,col3,col4,col5,count(*) total, 'M'  as recs
   from   tableA
   where col1 = ws-key1 and
          col2 = ws-key2 and
          col3 = ws-key3
   group by col1,col2,col3,col4,col5,recs
   having count(*) > 1


Code:
 IF RECS = 'S'  THEN
     Process the row..
 ELSE
    Do the exception...
 END-IF
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 860
Location: Maryland

PostPosted: Tue Jun 09, 2020 10:29 pm    Post subject:
Reply with quote

Rohit Umarjikar wrote:
This will create performance issues if the underlying data base is huge,

It depends on actual database design; when designed appropriately, no more, or even less issues than UNION

Rohit Umarjikar wrote:
second, you are using same table twice which means one-many joins on the way out,

UNION needs two table searches as well, exactly the same
Rohit Umarjikar wrote:
Third Union could do the same but in better way.

Cannot see any confirmation of that statement
Rohit Umarjikar wrote:
But instead of making a complex query and may run into performance issues or more elapsed times, why not to simple use the above suggested method, simple and clean.

The words "simple, and clean" are very doubtful in this context.
It all depends on: where exactly the TS would agree to place his selection logic? Many TS are stuck to their original "requirements" (taken from nowhere) and repeat them like a mantra, instead of review and consider alternate approaches.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2487
Location: NY,USA

PostPosted: Tue Jun 09, 2020 10:49 pm    Post subject:
Reply with quote

Agree and let just TS pick from the above solutions.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts NOT ABLE TO FIND DB2 ROWS Suehowe DB2 2 Sat Oct 10, 2020 2:05 am
No new posts how to get total count from numeric d... Suehowe SYNCSORT 9 Fri Oct 09, 2020 8:34 pm
No new posts Need to read duplicate rows from tabl... Poha Eater DB2 2 Wed Oct 07, 2020 11:08 pm
No new posts Creating a single-record file with CO... Joerg.Findeisen DFSORT/ICETOOL 8 Tue Oct 06, 2020 8:19 pm
No new posts Read 4MB message and split into multi... myselfsk15 COBOL Programming 9 Fri Sep 25, 2020 7:24 am

Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us