informaticainformatica-cloud

Informatica Cloud - CASE stmt

I Want to check the data in Mapping in Informatica cloud whether data exists or not before proceed to process further.. Here is my Teradata DB query and i want to do the same in informatica cloud

select CASE WHEN A_COUNT = 0 THEN 'FAIL' 
            WHEN B_COUNT = 0 THEN 'FAIL'
            WHEN C_COUNT = 0 THEN 'FAIL'
       ELSE 'PASS'
       END CHECK
from 
(
select SUM(case when source = 'A' then 1 else 0 end) A_COUNT,
       SUM(case when source = 'B' then 1 else 0 B_COUNT,
       SUM(case when source = 'C' then 1 else 0 end) C_COUNT
from TABL1
where source in ('A', 'B', 'C', 'D')
) ;

Table:

CREATE TABLE TABL1
(SOURCE CHAR(1), DT DATE);

Data:

INSERT INTO TABL1 ('A', '01-NOV-2021');
INSERT INTO TABL1 ('A', '02-NOV-2021');
INSERT INTO TABL1 ('B', '01-NOV-2021');
INSERT INTO TABL1 ('B', '02-NOV-2021');
INSERT INTO TABL1 ('C', '01-NOV-2021');
INSERT INTO TABL1 ('C', '04-NOV-2021');

I don't have the luxury to put the query as source.. that's why i need to create mapping.. :(

Solution

  • Use Aggregator Transformation to calculate the SUMs, followed by Expression Transformation with IIF function like:

    IIF(A_COUNT = 0 OR B_COUNT = 0 OR C_COUNT = 0; 'FAIL'; 'PASS')