Thread: Union in Nested IF Statements

All,

My question related around nested IF statements and a Union. I am trying to figure out how the last component of the formula should force the query to do a union on data (like a lookup) if it runs through all the previous tests.

In design mode it looks like this:

Prod Detail (Pre): IIf([*2011 Corp (P1 & PGS)]![L9 Cost Cent Desc]="TOTAL CORE FI DCM US CONDUITS" And [*2011 Corp (P1 & PGS)]![L1 Orig Prod Desc]="Origination Income","Conduits",IIf([*2011 Corp (P1 & PGS)]!Product1="Portfolio" And [*2011 Corp (P1 & PGS)]![Source System Code]="TLP","TLP",IIf([*2011 Corp (P1 & PGS)]![L8 Cost Cent Desc]="Total Core Mortgage Trading" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="DCM Origination","DCM",IIf([*2011 Corp (P1 & PGS)]!Product1="Markets" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="Eq Cash","Markets Other",IIf([*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="DCM Origination Cross Sell","Cross Sell",IIf([*2011 Corp (P1 & PGS)]![L2 Orig Prod Desc]="Deduplication Cross Sell","Solution Sales",IIf([*2011 Corp (P1 & PGS)]!Product1="Markets" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="Syndicate","Markets Other", Insert Union Here )))))))

OR in SQL Mode:

SELECT [*2011 Corp (P1 & PGS)].*, IIf([*2011 Corp (P1 & PGS)]![L9 Cost Cent Desc]="TOTAL CORE FI DCM US CONDUITS" And [*2011 Corp (P1 & PGS)]![L1 Orig Prod Desc]="Origination Income","Conduits",IIf([*2011 Corp (P1 & PGS)]!Product1="Portfolio" And [*2011 Corp (P1 & PGS)]![Source System Code]="TLP","TLP",IIf([*2011 Corp (P1 & PGS)]![L8 Cost Cent Desc]="Total Core Mortgage Trading" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="DCM Origination","DCM",IIf([*2011 Corp (P1 & PGS)]!Product1="Markets" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="Eq Cash","Markets Other",IIf([*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="DCM Origination Cross Sell","Cross Sell",IIf([*2011 Corp (P1 & PGS)]![L2 Orig Prod Desc]="Deduplication Cross Sell","Solution Sales",IIf([*2011 Corp (P1 & PGS)]!Product1="Markets" And [*2011 Corp (P1 & PGS)]![L3 Orig Prod Desc]="Syndicate","Markets Other"))))))) AS [Prod Detail (Pre)]
FROM [*2011 Corp (P1 & PGS)];

My skill in writing SQL is moderate, so I needed the advice on how to best create this. As I said, ultimately, this Union should happen after running through all the other tests and is the last component (similar to a vlookup).


Appreciate all input.