sqlsap-iq

using case clause with join statement sql

I have the following query:

select * from isg.tdbFutures f, isg.tdbOption e 
    where 
        f.contract = 306121 and
        e.underlier = f.entityID

Which will return this:

 entityID     lastTradeDate     expiration     firstTradeDate     contract     lastTradeDate     underlier     isPut     expiration     strike     entityID     optionMetricsID     expirationCycle    
 -----------  ----------------  -------------  -----------------  -----------  ----------------  ------------  --------  -------------  ---------  -----------  ------------------  ------------------ 
 311320       3/1/2018          3/1/2018       6/22/2017          306123       12/22/2017        311320        false     12/22/2017     100        368145       0                   monthly            
 311320       3/1/2018          3/1/2018       6/22/2017          306123       12/22/2017        311320        false     12/22/2017     106        368146       0                   monthly            
 311320       3/1/2018          3/1/2018       6/22/2017          306123       12/22/2017        311320        false     12/22/2017     120        368147       0                   monthly

I want to build a string to insert into another table, conditioned on the isPut column. This is my attempt:

select * from isg.tdbFutures f, isg.tdbOption e 
     where 
        f.contract = 306123 and
        e.underlier = f.entityID
     CASE isPut
        WHEN false THEN 'FI_US_M Call'
        WHEN true THEN 'FI_US_M Put'
     END

However, I get the following error:

>[Error] Script lines: 45-52 ------------------------
 SQL Anywhere Error -131: Syntax error near 'false' on line 6
 Msg: 102, Level: 15, State: 0
 Line: 0

The table I wat to insert in, where category is the string from my conditional and entityID is my f.contract value:

 category           entityID    
 -----------------  ----------- 
 US Equity          66281       
 US Fixed Income    66283       
 AUD                66359
Solution

  • The query that you seem to want is:

    select f.*, o.*,
       (case when isPut then 'FI_US_M Put'
             else 'FI_US_M Call'
        end) as new_column
from isg.tdbFutures f join
     isg.tdbOption o 
     on o.underlier = f.entityID
where f.contract = 306123;

    Notes: