I have two tables (customer and Alignment table) that are joined on Customer ID e.g.
Customer Table Alignment Table
Customer ID ----------- Customer ID
There is a situation where Customer ID can be Null so whenever it is null i need to use zip to zip join rather than Customer Id to customer ID join.
I created a alias of the Alignment table and joined the alias with the customer table on zip e.g.
Alignment table zip (alias) Customer Table Alignment Table
Customer ID Customer ID ----------- Customer ID
Zip ---------------------------------------- Zip Region
If i run a query on Alignment table Zip (alias) and Customer Table and another on Alignment table and Customer table and merge on report level it works fine. But that requires me to have two alignment classes in the universe (one for Zip join and other for Cmid join). I want to have one class with region defination but have the condition working such as : records where Customer Id is NOT null then get the region information using Customer ID join but records where it is NULL then use the Zip join with the Alignment table zip alias . I am trying to replicate the running of two queries on report level and merging dimension creteria on the uiverse side.
Let me know if that is possible !
Sheikh Ayub replied
i had to modfy the join as :
(Customer table.CUST_ID IS NOT NULL AND Customer Table.CUST_ID=Alignment.CUST_ID)
(Customer table.CUST_ID IS NULL AND Alignment.Cust_ID is NULL AND Customer table.ZIP=Alignment.ZIP)
The reason was that i only wanted to get records where all customer ID's were NULL both for Alignmenet table and the Customer table, so i was missing the CUST_ID is NULL condition from the alignment table.