Skip to Content
0
May 23, 2018 at 08:53 AM

Subqueries in Information Design Tool

326 Views

I am working on IDT to create a universe to be used in Design Studio.

The requirement is I have to provide two different measures in single query to be used in design studio, where

Query 1 (Spilled over cases for current month) -->

select sum(Loan_Amt)

from Loan_Table

where Login_Date < Month_Start_Date AND (Decision_Date >=Month_Start_Date OR Decision_Date is null)

Query 2 (Spilled over cases for previous month) -->

select sum(Loan_Amt)

from Loan_Table

where Login_Date < add_months (Month_Start_Date,-1) AND (Decision_Date >=add_months(Month_Start_Date,-1) OR Decision_Date is null)

where Month_Start_Date is the 1st day of the current month. Now suppose if Query 1's answer is 657 and Query 2's is 1200 independently but when both of them are taken together with a dimension say, zone, they are giving similiar results,

ZONES CURR_MONTH PREV_MONTH

West Zone 67.66 67.66

East Zone 5.89 5.89

North Zone 55.65 55.65

South Zone 54.82 54.82

the script generated is -->

select zones, sum(Loan_Amt),

sum(Loan_Amt)

from Loan_Table

where Login_Date < Month_Start_Date AND (Decision_Date >=Month_Start_Date OR Decision_Date is null) AND Login_Date < add_months(Month_Start_Date,-1) AND (Decision_Date >=add_months(Month_Start_Date,-1) OR Decision_Date is null)

group by zones

The script that was expected is -->

select zones,

(select sum(Loan_Amt) where Login_Date < Month_Start_Date AND (Decision_Date >=Month_Start_Date OR Decision_Date is null) ),

(select sum(Loan_Amt) from Loan_Table where Login_Date < add_months (Month_Start_Date,-1) AND (Decision_Date >=add_months(Month_Start_Date,-1) OR Decision_Date is null))

group by zones

I can't attach screenshots due to some reasons but I think I've explained to the best I can. Any ideas would be highly appreciated.