02-20-2013 04:13 PM
select cust_xref_id,ssn,id_nbr,count(id_nbr) as card_count from card_master
where cust_xref_id,ssn in (select cust_xref_id,ssn from card_master
where id_nbr in (&id_nbr)
group by cust_xref_id,ssn.id_nbr
The user passes the id_nbr for which ssn and cust_xref_id are populated.The idea is to get the count of number of cards for that combination of cust_xref_id and ssn.
and then join the above table with table on id_nbr,populating the card_count
Can a where clause like above be used?
02-20-2013 05:00 PM
count(t1.id_nbr) as card_count
inner join (select distinct cust_xref_id, ssn from card_master where id_nbr in (&id_nbr)) t2
on t1.cust_xref_id=t2.cust_xref_id and t1.ssn=t2.ssn
02-20-2013 05:12 PM
input cust_xref_id ssn id_nbr;
1234 1111 123456
1234 1111 234567
1234 1111 345678
1234 1111 456789
2345 2222 891023
2345 2222 910234
the idea is: when the user passes the value 345678 for id_nbr,the cust_xfer_id abd ssn are 1234 and 1111.
for this combination there are four cards.(123456,234567,345678,456789)