|
View previous topic :: View next topic
|
| Author |
Message |
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
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 |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2024
Location: USA
|
|
|
|
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 |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| 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 |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
| 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 |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
| I am using citrix, so code tag not working for me. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| 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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
Pssssssst, Enrico, a double tag works. If you edit this post, all will be revealed 
| Code: |
[code]
Put your stuff in here
[/code]
|
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
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 ..  |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2024
Location: USA
|
|
|
|
| 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 |
|
|
|
|
