Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
query to fetch record which has only 1 row with other cond

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

New User


Joined: 27 Feb 2008
Posts: 87
Location: india

PostPosted: Mon Dec 11, 2017 5:03 pm    Post subject: query to fetch record which has only 1 row with other cond
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

Senior Member


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

PostPosted: Tue Dec 12, 2017 12:10 am    Post subject:
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

Active User


Joined: 29 Apr 2008
Posts: 247
Location: Maryland

PostPosted: Tue Dec 12, 2017 12:26 am    Post subject:
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

New User


Joined: 27 Feb 2008
Posts: 87
Location: india

PostPosted: Tue Dec 12, 2017 10:39 am    Post subject: Reply to: query to fetch record which has only 1 row with other cond
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: 8629
Location: Back in jolly old England

PostPosted: Tue Dec 12, 2017 12:29 pm    Post subject:
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

New User


Joined: 27 Feb 2008
Posts: 87
Location: india

PostPosted: Tue Dec 12, 2017 2:26 pm    Post subject: Reply to: query to fetch record which has only 1 row with other cond
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

New User


Joined: 27 Feb 2008
Posts: 87
Location: india

PostPosted: Tue Dec 12, 2017 2:27 pm    Post subject: Reply to: query to fetch record which has only 1 row with other cond
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10388
Location: italy

PostPosted: Tue Dec 12, 2017 3:04 pm    Post subject: Reply to: query to fetch record which has only 1 row with other cond
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: 8629
Location: Back in jolly old England

PostPosted: Tue Dec 12, 2017 4:24 pm    Post subject:
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

Senior Member


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

PostPosted: Tue Dec 12, 2017 6:26 pm    Post subject:
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: 413
Location: USA

PostPosted: Wed Dec 13, 2017 2:21 am    Post subject: Reply to: query to fetch record which has only 1 row with other cond
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

New User


Joined: 27 Feb 2008
Posts: 87
Location: india

PostPosted: Wed Dec 13, 2017 12:47 pm    Post subject: Reply to: query to fetch record which has only 1 row with other cond
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

Active User


Joined: 29 Apr 2008
Posts: 247
Location: Maryland

PostPosted: Wed Dec 13, 2017 10:09 pm    Post subject: Re: Reply to: query to fetch record which has only 1 row with other cond
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: http://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    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 How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 0 Tue Jan 16, 2018 10:49 pm
No new posts reg query on DYNALLOC feature raghuraman123 SYNCSORT 12 Wed Jan 10, 2018 2:42 pm
No new posts Extract record for change in combinat... Trinadh DFSORT/ICETOOL 6 Thu Nov 23, 2017 3:32 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm

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