IBM Mainframe Forum

SELECT CLIENT_ID,ADR_SEQ_NBR FROM PSTPROD.CLT_ADR_RELATION
WHERE ADR_TYP_CD = 'HHA'
AND CIAR_EXP_DT = '9999-12-31'
GROUP BY CLIENT_ID,ADR_SEQ_NBR
HAVING COUNT(*) > 1
ORDER BY CLIENT_ID,ADR_SEQ_NBR

This fetches the rows if CIAR_EXP_DT = '9999-12-31' , and Adr_seq_nbr of either 0 or 1 etc .. and have duplicate rows for the same client id..but i need to fetch the duplicate rows even if the adr seq nbr is a combo of 0,1,2 etc.. As of nw it fetches only if adr_seq_nbr is either 0 or 1 or 2 ..

Can anyone suggest the modifications to above query to match my requirement?

Thanks
Uday

Hello,

Can anyone suggest the modifications to above query to match my requirement?

Probably not until we understand the requirement. . .

Suggest you post some sample data (that includes some rows you do not want selected as well as some that are wanted). Then show what the result of the query should be based on that sample data.

Hi ,

Please find below the sample requirement AND rows..

CASE (i) :

CLIENT HIST VLD ADRSEQ EFF ADR CIAR
ID NBR NBR DT ID EXP DT

-------------------- ------- ------ ---------- -------------------- ---------
CLTC0B8UGGBRSPSVBATC 0 0 2004-11-16 CLTA0B8UGGBRSPOGBATC 9999-12-31
CLTC0B8UGGBRSPSVBATC 0 0 2007-10-29 CLTAJXWHD4WNBAH0SKT4 9999-12-31
CLTC0B8UGGBRSPSVBATC 0 1 2004-11-16 CLTA0B8UGGBRSPOGBATC 9999-12-31

I need a query which fetches the above rows..

CASE (ii) :

But the query which i mentioned in earlier post fetches rows had it been the scenario like this..

CLIENT HIST VLD ADRSEQ EFF ADR CIAR
ID NBR NBR DT ID EXP DT

-------------------- ------- ------ ---------- -------------------- ---------
CLTC0B8UGGBRSPSVBATC 0 0 2004-11-16 CLTA0B8UGGBRSPOGBATC 9999-12-31
CLTC0B8UGGBRSPSVBATC 0 0 2007-10-29 CLTAJXWHD4WNBAH0SKT4 9999-12-31
CLTC0B8UGGBRSPSVBATC 0 0 2004-11-16 CLTA0B8UGGBRSPOGBATC 9999-12-31

The only difference b/w case (i) and case (ii) rows is that ADR SEQ NBR has changed for 3rd row..
so to put it straight, i need to fetch the rows EVEN if the adr seq nbr changes and my condition for duplicate row meets.
Now i will define what a duplicate row is ( If the same client id has more than one row with exp dt of 9999-12-31 , THEN
it has duplicate address or duplicate row.

In my query , only case (ii) rows are being fetched , but i need to fetch even case (i) rows , where adr seq number is combination of 0 and 1.

Hope this helps. Please let me know if you need any other information.

Thanks
Uday

Hello,

Hope this helps. Please let me know if you need any other information.

Yes, please create some additional data that shows the situation more clearly. The posted data and the desired output will not have the same number of rows. The sample "input" should show rows that will be selected and rows that will not. The sample "output" will be a subset of the input.

When posting, use the "Code" tag which will preserve alignment and greatly improve readability. From your terminal screen, use copy/paste and then Code. Below the box being typed in is a Preview function. This will let you see your post as the forum will see it. When you are satisfied with the appearance, click Submit. Do not skip the Submit - all of the work will be lost

Hi ,

Please read the columns as client id , hist vld nbr , adr seq nbr , effective date , adr id , and ciar exp dt ...

Sorry i was nt able to align columns in a clear manner .. so i mentioned the columns names clearly above.

The difference b/w first case and second case is that case (i) has 3 rows , and case (ii) has 2 rows..

CASE (i) :

CLIENT HIST VLD ADRSEQ EFF ADR CIAR
ID NBR NBR DT ID EXP DT

-------------------- ------- ------ ---------- -------------------- ---------
CLTC0B8UGGBRSPSVBATC 0 0 2004-11-16 CLTA0B8UGGBRSPOGBATC 9999-12-31
CLTC0B8UGGBRSPSVBATC 0 0 2007-10-29 CLTAJXWHD4WNBAH0SKT4 9999-12-31
CLTC0B8UGGBRSPSVBATC 0 1 2004-11-16 CLTA0B8UGGBRSPOGBATC 9999-12-31

I need a query which fetches the above rows..

CASE (ii) :

But the query which i mentioned in earlier post fetches rows had it been the scenario like this..

CLIENT HIST VLD ADRSEQ EFF ADR CIAR
ID NBR NBR DT ID EXP DT

-------------------- ------- ------ ---------- -------------------- ---------
CLTC0B8UGGBRSPSVBATC 0 0 2004-11-16 CLTA0B8UGGBRSPOGBATC 9999-12-31
CLTC0B8UGGBRSPSVBATC 0 0 2007-10-29 CLTAJXWHD4WNBAH0SKT4 9999-12-31


The only difference b/w case (i) and case (ii) rows is that ADR SEQ NBR of 1 in 3rd row of case (i)..
so to put it straight, i need to fetch the rows EVEN if the adr seq nbr changes and my condition for duplicate row meets.

Now i will define what a duplicate row is ( If the same client id has more than one row with exp dt of 9999-12-31 , THEN
it has duplicate address or duplicate row.

In my query , only case (ii) rows are being fetched , but i need to fetch even case (i) rows , where adr seq number is combination of 0 and 1.

Let me know if you have any more questions.

Thanks,
Uday

Hello,

Sorry i was nt able to align columns in a clear manner ..

It may take a bit of practice, but yes, you are able to align things. . .

When posting, use the "Code" tag which will preserve alignment and greatly improve readability. From your terminal screen, use copy/paste and then Code. Below the box being typed in is a Preview function. This will let you see your post as the forum will see it. When you are satisfied with the appearance, click Submit.

You need to practice using Preview and do not Submit until the preview looks the way you want it.

Repeating what you initially posted with almost no clarification will not help you get a useful answer. . . Do not post 2 outputs/answers as you have done both times. You need to post a more comprehensive set of input (3 rows is not very comprehensive) and the "output" you would want from that exact sample input. This should not be difficult to do, but may take a bit of work. When posting this, you would practice using the "Code" tag. Once you have used it a time or 2 it becomes almost automatic.

Keep in mind that the burden of getting useful replies from the forum is on the person posting the question. While your requirement and data may be completely clear to you, they are not to others who would help if they understood what was needed.