How to create a case when query to return a result in a table-CodePudding

I have a problem because I have two tables which are joined by country code and return tables with distinct keys. The problem is that I have blanks because these items do not have a country code added, but they have a currency that I would like to use to add items. For example, When "WALUTA" = 'PLN' THEN 'A.CODE =' PL 'but it doesn't work. Is there any other solution?

proc sql;
    create table COREP_CR as
    select distinct 
    a.DATA_DANYCH as REPORTING_DATE,
    a.CRD_CCF, 
    b.Kod,
    a.EXP_PIERWOTNA as  EAD_PRE_CCF,
    a.ID,
    a.TYPE_ASSET,
    a.Currency,

                when (TYPE_ASSET) 'Futures' and (Currency) = 'PLN' then a.KOD = 'PLN'
                end,

I got >ERROR 76-322: Syntax error, statement will be ignored.
>
            from corep as a
left join _work.country_corep as b on a.Kraj=b.Kraj
Group by b.Kod, a.TYPE_ASSET, a.CRD_CCF, ORIGINAL_ASSET_CLASS, FINAL_ASSET_CLASS, PRODUCT_TYPE
;
            QUIT;

After helped i got like this new colum _TEMP but i would like put this result to columdn Kod.

CodePudding user response：

The case statement should look like this:

case when a.TYPE_ASSET = 'Futures' and a.Currency = 'PLN' then 'PLN'
else b.KOD
end "KOD"

So when criteria defined are met, the now 'virtual' field named "KOD" will be returned as "PLN", otherwise the contents of the field KOD from the joind table _work.country_corep will be returned.

If i understand correctly what you are trying to do - if there is not a row corresponding in the joined table b, put something else in the field which is otherwise filled from data from this table, then your select should look like this:

select distinct 
    a.DATA_DANYCH as REPORTING_DATE,
    a.CRD_CCF, 
    --b.Kod,
    Case When a.TYPE_ASSET='Futures' And a.Currency='PLN' Then 'PLN' --IF criteria are met, let us returned PLN as Kod
    Else b.KOD --OTHERWISE return the Kod from table b
    End "Kod",
    a.EXP_PIERWOTNA as  EAD_PRE_CCF,
    a.ID,
    a.TYPE_ASSET,
    a.Currency
from corep as a
left join _work.country_corep as b on a.Kraj=b.Kraj
Group by b.Kod, a.TYPE_ASSET, a.CRD_CCF, ORIGINAL_ASSET_CLASS, FINAL_ASSET_CLASS, PRODUCT_TYPE
;