Search code examples
sqlpostgresqltemporaryin-operator

Combine IN Operator values with temporary table

Part of my current query is this CASE WHEN statement:

CASE WHEN SUBSTRING(article_code,1,4) IN ('Ak42','Ales','AlHe','Artu','Behr','Crea','Digi','Egos','Emu_','Fend','Fen2','GiSt',Icon','InMa','Korg','Lin6','Li6S','MAud','MGtr','Mack','Nova','Focu','NoLa','NoLM','Relo','Tasc','TC_E','Vest','Xona','Xpre','Zoom','SndC','LtGn') THEN 'X' ELSE 'Y' END AS lup

This is not only awkward to read/format it also has a fundamental flaw, namely that the list of values contained in the IN Operator is not static but dynamic. As there is no such lookup_table contained in the database and this is unlikely to change, I was wondering if it is possible to create a temp table which looks like this...:

AC

  • InMa
  • Relo
  • Zoom
  • etc..

... and then use those values in column AC as values for the IN operator, such as:

CASE WHEN SUBSTRING(article_code,1,4) IN (temp_table) THEN 'X' ELSE 'Y' END AS lup

It is even possible to use the IN operator in such a fashion? If that isn't possible, maybe there is another solution.

Solution

  • Yes. The syntax is:

    (CASE WHEN SUBSTRING(article_code,1,4) IN (select article_code from temp_table) THEN 'X'
      ELSE 'Y'
 END) AS lup

    For best performance, create the table with article_code as the primary key or create an index on it.