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
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.
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 ;
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.
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
-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.
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?
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.
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.
Actually TS needs both the conditions and needs a UNION but then so be it.
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
;
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
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.
