syntax to add a condition

[#1]

Hi all,

I need to add a condition to a field which is part of the LINKKEY field (Not sure why initially in one qvd LINKKEY is defined in LOAD part and in another in SELECT part).

QVD1---------------------------------------------------------------------------------------

condition to add:

           if D_ASSET_TYPE = '005' and A_DEPT < '1000' then A_DEPTFUNC = '000'

QVD1:

LOAD
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
LINKKEY;

SQL SELECT
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
A_DEPT || '-' || F_FUND || '-' || A_DEPTFUNC || '-' || D_ASSET_TYPE || '-' || A_SUBDEPT as LINKKEY
FROM YYYY;

store QVD1 into '..\QVDs\QVD1.qvd';

QVD2---------------------------------------------------------------------------------------

condition to add:

            if ASSET_TYPE = '005' and DEPT < '1000' then DEPTFUNC = '000'

QVD2:
LOAD
    DEPT,
    DEPTFUNC,
    DEPT & '-' & FUND & '-' & DEPTFUNC & '-' & ASSET_TYPE & '-' & SUBDEPT as LINKKEY,
    DEPT & '-' & DEPTFUNC & '-' & SUBDEPT as DEPTPROJKEY,
SQL SELECT
     DEPT,
     DEPTFUNC,
FROM AAAA;
store QVD2 into '..\QVDs\QVD2.qvd';

Thanks, appreciate your help

[sunny_talwar]

May be this:

QVD1:

LOAD
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
DEPT & '-' & FUND & '-' & If(D_ASSET_TYPE = '005' AND A_DEPT = <1000, '000', DEPTFUNC) & '-' & ASSET_TYPE & '-' & SUBDEPT as LINKKEY;

SQL SELECT
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
A_DEPT || '-' || F_FUND || '-' || A_DEPTFUNC || '-' || D_ASSET_TYPE || '-' || A_SUBDEPT as LINKKEY
FROM YYYY;

store QVD1 into '..\QVDs\QVD1.qvd';

QVD2:

LOAD

    DEPT,

    DEPTFUNC,

    DEPT & '-' & FUND & '-' & If(D_ASSET_TYPE = '005' AND A_DEPT = <1000, '000', DEPTFUNC) & '-' & ASSET_TYPE & '-' & SUBDEPT as LINKKEY,

    DEPT & '-' & DEPTFUNC & '-' & SUBDEPT as DEPTPROJKEY,

SQL SELECT

     DEPT,

     DEPTFUNC,

FROM AAAA;

store QVD2 into '..\QVDs\QVD2.qvd';

[#3]

Sunny,

looks like we can not condition within select statement?

[sunny_talwar]

That would be a question to ask a SQL guy. Don't have too much expertise there.

[Qrishna]

Hi Larisa,

Try below:

QVD1:

LOAD
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
LINKKEY;

SQL SELECT
A_DEPT,
F_FUND,
A_DEPTFUNC,
D_ASSET_TYPE,
A_SUBDEPT,
A_DEPT || '-' || F_FUND || '-' || A_DEPTFUNC || '-' || D_ASSET_TYPE || '-' || A_SUBDEPT as LINKKEY
FROM YYYY

WHERE D_ASSET_TYPE = '005' and A_DEPT < '1000' AND A_DEPTFUNC = '000';

//(if you want all the conditions to be satisfied at once else you can use 'OR' instead of 'AND')

store QVD1 into '..\QVDs\QVD1.qvd';

QVD2---------------------------------------------------------------------------------------

QVD2:

LOAD
    DEPT,
    DEPTFUNC,
    DEPT & '-' & FUND & '-' & DEPTFUNC & '-' & ASSET_TYPE & '-' & SUBDEPT as LINKKEY,
    DEPT & '-' & DEPTFUNC & '-' & SUBDEPT as DEPTPROJKEY,
SQL SELECT
     DEPT,
     DEPTFUNC,
FROM AAAA

where ASSET_TYPE = '005' and DEPT < '1000' and DEPTFUNC = '000';

store QVD2 into '..\QVDs\QVD2.qvd';

[#6]

Thank you Sunny for your help, it works.  

[#7]

Thanks a lot, problem fixed.

L

[Qrishna]

Please close the thread