|
View previous topic :: View next topic
|
| Author |
Message |
iamdijkstra007
New User
Joined: 07 Jun 2020
Posts: 3
Location: USA
|
|
|
|
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 |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
| 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 |
|
|
iamdijkstra007
New User
Joined: 07 Jun 2020
Posts: 3
Location: USA
|
|
|
|
| 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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| I doubt it is throwing an error as errors are not thrown on the mainframe. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3098
Location: NYC,USA
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3098
Location: NYC,USA
|
|
|
|
| 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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
| 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 |
|
|
iamdijkstra007
New User
Joined: 07 Jun 2020
Posts: 3
Location: USA
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3098
Location: NYC,USA
|
|
|
|
| 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.  |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3098
Location: NYC,USA
|
|
|
|
| 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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
| Rohit Umarjikar wrote: |
| 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
;
|
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3098
Location: NYC,USA
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3098
Location: NYC,USA
|
|
|
|
| Agree and let just TS pick from the above solutions. |
|
| Back to top |
|
|
|
|
|
