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

query to fetch record which has only 1 row with other cond


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

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india

PostPosted: Mon Dec 11, 2017 5:03 pm
Reply with quote

I have to write a query based on below conditions from 2 tables:

Table names:
claim A & transaction B
1.
Code:
A.trans_id = B.trans_id
AND A.PROCS_OFF_CD IN ('491','299')
AND B.SYS_SRCE_CD IN ('APL','EOA') 
AND A.ENDG_TRN_ID IS NULL


2. want to fetch the record which has only 1 row in total in claim A table .. i..e count(*) = 1 for A.CLMF_ID with A.CLM_A_CD ='ALE' in claim A table

I don't need to fetch if there are more than 1 row with A.CLM_A_CD ='ALE' and A.CLM_A_CD ='CLE' or 'FLE' for the same A.CLMF_ID

I have tried with some combinations but did not get the proper output.

Code:
SELECT A.CLMF_ID, COUNT(*)         
FROM
BGI00.CLAIM A,
BGI00.TRANSACTION B
WHERE A.START_TRN_ID=B.TRN_ID
AND A.CLM_A_CD ='ALE'
AND A.PROCS_OFF_CD IN ('491','299')
AND B.SYS_SRCE_CD IN ('APL','EOA')
AND A.ENDG_TRN_ID IS NULL
      GROUP BY A.CLMF_ID
      HAVING COUNT(*) = 1           
WITH UR;                     


Thanks in advance for any input.
Thanks
Subrat
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Tue Dec 12, 2017 12:10 am
Reply with quote

It is very simple.
Do you know sub query/co related query or EXISTS in DB2? See if that clicks your mind.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Tue Dec 12, 2017 12:26 am
Reply with quote

A standard approach, irrelevant to whatever code/system/language/environment it is: whenever any complex code/statement/program/query/request doesn't produce what is expected,
1) first of all, split your suspicious code into simple parts, and
2) verify that every part by itself produces what is needed.

A very bad approach is as follows:
1) create as complex code as possible, including any of sophisticated expression/function/operation/statement/query found anywhere, and
2) post your code to the forum, asking for help
3) especially bad is, doing #1-2 without posting input/output data used for testing, and/or any log/error/warning messages
4) even worse than #3 is, posting your pieces of code without using <code> tags; that proves that you hate the potential readers of your post.
Back to top
View user's profile Send private message
maxsubrat

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india

PostPosted: Tue Dec 12, 2017 10:39 am
Reply with quote

I have tried with subquery.. but not sure why it is not fetching the data which has only one row , instead it is fetching data for the CLAIMF-ID which has 1 row or more than one row.

My main condition is to fetch data when CLM_A_CD ='ALE' and this should be the only row for that claimf-id.

The table has only one CLM_A_CD ='ALE' for each CLAIMF-ID. but some has other records for the same CLAIMF-ID with CLM_A_CD ='ALE'. So my main criteria is to get the output for those which has only one count for that CLAIMF-ID with CLM_A_CD ='ALE, no other row exist for that.



Code:
SELECT A.CLMF_ID                           
FROM BGI00.CLAIM A,                         
     BGI00.TRANSACTION B                   
WHERE A.START_TRN_ID=B.TRN_ID               
AND A.PROCS_OFF_CD IN ('491','299')         
AND B.SYS_SRCE_CD IN ('APL','EOA')         
AND A.CLMF_ID IN (SELECT CLMF_ID,COUNT(*)
                  FROM BGI00.CLAIM A       
                  WHERE A.CLM_A_CD ='ALE'   
                  AND A.ENDG_TRN_ID IS NULL
                  GROUP BY CLMF_ID
                  HAVING COUNT(*) = '1' )
WITH UR;
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales

PostPosted: Tue Dec 12, 2017 12:29 pm
Reply with quote

sergeyken wrote:
A standard approach, irrelevant to whatever code/system/language/environment it is: whenever any complex code/statement/program/query/request doesn't produce what is expected,
1) first of all, split your suspicious code into simple parts, and
2) verify that every part by itself produces what is needed.
Have you done this ???

Obviously after many posts have not yet bothered to find out about using the code tags.

Code:
[code]
your stuff here
[/code]

Easy eh ?

Why are you grouping by a field that doesn't exist ?
In your explanation you use CLAIMF-ID yet in your code you use CLMF_ID, so which one is it ?

You have been asked for input / output, have you given it .................. NO

Seriously, if you cannot be bothered to post an accurate representation of your problem or supply requested information do you really think that people would be willing to help you ?
Back to top
View user's profile Send private message
maxsubrat

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india

PostPosted: Tue Dec 12, 2017 2:26 pm
Reply with quote

Code:
SELECT A.CLMF_ID
FROM BGI00.CLAIM A,
     BGI00.TRANSACTION B
WHERE A.START_TRN_ID=B.TRN_ID
AND A.PROCS_OFF_CD IN ('491','299')
AND B.SYS_SRCE_CD IN ('APL','EOA')
AND A.CLMF_ID IN (SELECT CLMF_ID
                  FROM BGI00.CLAIM A
                  WHERE A.CLM_A_CD ='ALE'
                  AND A.ENDG_TRN_ID IS NULL
                  GROUP BY CLMF_ID
                  HAVING COUNT(CLMF_ID) = 1)
WITH UR;


Please check.

If I look my output from this query, it looks the subquery is considering those CLAIMF_ID who has more than 1 row...

Code:
---------+-----
CLMF_ID
---------+-----
3B414914082342
45282991572417
45282991572417
45282991572472
45282991572472


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

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india

PostPosted: Tue Dec 12, 2017 2:27 pm
Reply with quote

I am using citrix, so code tag not working for me.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Tue Dec 12, 2017 3:04 pm
Reply with quote

Quote:
I am using citrix, so code tag not working for me.


we do not give any organic waste about the software used to connect
as long as it does not make people who help work more to understand Your posts

hard to believe anyway ...
insert by hand the strings which implement the code tags
Code:

_[_c_o_d_e_]_
_[_/_c_o_d_e_]_


and do not type the underscores, the were used to prevent the forum software from mangling the post

P.S.
Your previous posts were coded for readabily and respect toward the people willing to helo
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8796
Location: Welsh Wales

PostPosted: Tue Dec 12, 2017 4:24 pm
Reply with quote

Pssssssst, Enrico, a double tag works. If you edit this post, all will be revealed icon_cool.gif

Code:

[code]
Put your stuff in here
[/code]
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Tue Dec 12, 2017 6:26 pm
Reply with quote

1. Did you check the output of this? You need more filters to get a unique row , which means you need to understand the requirements more clear.
Code:
SELECT A.CLMF_ID                           
FROM BGI00.CLAIM A,                         
     BGI00.TRANSACTION B                   
WHERE A.START_TRN_ID=B.TRN_ID               
AND A.PROCS_OFF_CD IN ('491','299')         
AND B.SYS_SRCE_CD IN ('APL','EOA')         
AND A.CLMF_ID = '45282991572417'


Quote:
If I look my output from this query, it looks the subquery is considering those CLAIMF_ID who has more than 1 row...

You need to run the subquery then by itself to see what you said is really true, right? moreover you are saying "count" has a bug .. icon_smile.gif
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Dec 13, 2017 2:21 am
Reply with quote

maxsubrat,

If you want to know why you have multiple CLMF_ID as output then you'll need to look into your table B i.e. if you have multiple records for same TRN_ID in Transaction table.

*this may also be a possibility for multiple output records in your query.

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

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india

PostPosted: Wed Dec 13, 2017 12:47 pm
Reply with quote

Hi,

Actually for the CLAIMF_ID, the table may be 1 record or 2 records or more than 2 records.

But I want to fetch the record which has only one record exist in the table for the CLAIMF_ID. I don't want to fetch the records which has more than 1 row exist for the CLAIMF_ID.

but my query selecting all types of records(whether one row exist for that CLAIMF_ID or multiple rows) , that is the issue.
That is why I tried to give COUNT(CLAIMF_ID) to select those which exst only 1 row for the CLAIMF_ID. But looks like it's not working here.

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

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Wed Dec 13, 2017 10:09 pm
Reply with quote

maxsubrat wrote:
Hi,

Actually for the CLAIMF_ID, the table may be 1 record or 2 records or more than 2 records.

But I want to fetch the record which has only one record exist in the table for the CLAIMF_ID. I don't want to fetch the records which has more than 1 row exist for the CLAIMF_ID.

but my query selecting all types of records(whether one row exist for that CLAIMF_ID or multiple rows) , that is the issue.
That is why I tried to give COUNT(CLAIMF_ID) to select those which exst only 1 row for the CLAIMF_ID. But looks like it's not working here.

Thanks

1) first of all, split your suspicious code into simple parts, and
2) verify that every part by itself produces what is needed.
3) don't forget to post your input/output data, and any trace/error/warning log

Read more: ibmmainframes.com/viewtopic.php?t=66350#ixzz519yInbni
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 Need help with ADABAS query (COBOL-AD... All Other Mainframe Topics 0
No new posts Copy only TEXT or String from a record SYNCSORT 4
No new posts combine multiple unique records into ... DFSORT/ICETOOL 2
No new posts SORT on detail record, then repeat he... DFSORT/ICETOOL 3
No new posts how to complete the end of a record w... DFSORT/ICETOOL 2
Search our Forums:

Back to Top