I have the a DB2 table PROD-RULE-CD which has the following columns:
A sample data in the table is as follows:
COMP_ID PROD_ID ST_CD CAT_CD RULE_CD
01 141 AK A 2006
01 141 AK R 0516
01 141 AL A 2006
01 141 AL R 0516
01 141 AZ A 2001
01 141 AZ S 0063
01 141 AZ R 0516
01 141 CA A 2001
01 141 CA A 2006
01 141 CA R 0516
In this table, I have a Company-ID and Product ID available in each state and a rule-cd applies to the Company-Code and Product ID in that state.
Now, I want to find out the unique Rule-CD combinations which are present in this table.
So, in the above data sample, the State of AK and AL has the same Rule-CD combinations i.e. the Rule Code 2001 and 0516 apply to both the states. AZ and CA have a different rule-cd combinations. For AZ, the rule-cd which can be applied is 2001, 0063 and 0516. For CA, the rule-cd is 2001, 2006 and 0516.
Can I find out the unique combinations in a query? I tried ranking but that does not work.
Distinct does not work because each row is distinct so it returns all the rows. I do have a logic to implement in COBOL but just checking if anyone in forum have some idea to implement in DB2 query.