using case clause with join statement sql

Question

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

The `case` expression belongs in the `select` clause, not the `from`clause. — Gordon Linoff, Sep 27 '18 at 14:01
@GordonLinoff it seems like even moving the `case` clause before the `where` results in the same error. — Évariste Galois, Sep 27 '18 at 14:06

Gordon Linoff · Accepted Answer · 2018-09-27T14:56:41.297

2

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:

Learn to use proper, explicit, standard JOIN syntax. Never use commas in the FROM clause.
As a best practice, you should list out all the columns in the select.
Table aliases should be abbreviations for the table name, so I changed e to o.
The case expression belongs in the select.

edited Sep 27 '18 at 14:56

answered Sep 27 '18 at 14:22

Gordon Linoff

1,242,037
58
646
786

As a followup, if I wanted to replace `FI_US_M` with a query, would it be possible? For example: `(select category from isg.tdbExternCategory where entityID = 306123)+' Put'` – Évariste Galois Sep 27 '18 at 16:17
1

@ÉvaristeGalois . . . Yes, you can replace the value with a scalar subquery -- one that returns one column and zero or one rows. – Gordon Linoff Sep 27 '18 at 19:53

score 0 · Answer 2 · answered Sep 27 '18 at 14:06

0

As @GordonLinoff points out, shouldn't it be like:

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

answered Sep 27 '18 at 14:06

The Impaler

45,731
9
39
76

I want to insert the result of `'FI_US_M Call'` or `'FI_US_M Put` into another table along with my `f.contract` value, what's the significance as the `as my_new_column`? – Évariste Galois Sep 27 '18 at 14:08
The new column is a "computed column". You can use it any way you like. For example for an insert. From your question is not clear where you want to insert that column: will it replace an existing column? will it be a new column? – The Impaler Sep 27 '18 at 14:14
My mistake, I updated the post to include what my desired insert is. – Évariste Galois Sep 27 '18 at 14:16

score 0 · Answer 3 · answered Sep 27 '18 at 14:09

0

I think you want this

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

answered Sep 27 '18 at 14:09

user2042214

165
9

score 0 · Answer 4 · answered Sep 27 '18 at 14:13

If you want to insert into another table, shouldn't it be something like this then?

insert new_column_from_case = CASE isPut WHEN false THEN 'FI_US_M Call'
                                         WHEN true THEN 'FI_US_M Put'
                                         END
       new_f_contract = f.contract
  into another_table
  from isg.tdbFutures f, isg.tdbOption e 
 where f.contract = 306123 
   and e.underlier = f.entityID

using case clause with join statement sql

4 Answers4