Case statement in CE_JOIN join conidtion

[Former Member]

Hi All,

I am trying to replicate the following SQL code using CE_JOIN. Can someone please help me on how i can achieve this.

SELECT

  a.REFERENCE01,  a.REFERENCE02 , a.REFERENCE03, a.REFERENCE04, a.REFERENCE05, a.REFERENCE06, b.REFERENCE09,

  b.REFERENCE10

  FROM table1  INNER JOIN table2 ON

  a.REFERENCE01 = b.REFERENCE01

  AND a.REFERENCE02 = CASE WHEN b.REFERENCE02 = 'ALL' THEN a.REFERENCE02 ELSE b.REFERENCE02 END

  AND a.REFERENCE03 = CASE WHEN b.REFERENCE03 = 'ALL' THEN a.REFERENCE03 ELSE b.REFERENCE03 END

  AND a.REFERENCE04 = CASE WHEN b.REFERENCE04 = 'ALL' THEN a.REFERENCE04 ELSE b.REFERENCE04 END

Regards,

Ravi.

[former_member182114 · Accepted solution]

Hi Ravi,

I hope you want to do it just for education purpose, take a look on blog:

Anyhow, what we sometimes can do in one statement on SQL need more steps on CE. (something like CISC x RISC)

Look like this can become complicated:

- prepare a dataflow from table1 with a calculated column with content ALL

- perform CE_JOIN with this dataflow with table2 4 times (a.reference01 = b.reference01, a.cc_all = b.reference02, a.cc_all = b.reference03 and another a.cc_all = b.reference04)

- perform CE_UNION with 1st/2nd data flows and 3th/4th data flows

- perform CE_UNION with these two results

As the granularity of CE instructions are too low you have other options to achieve the same, but pay attention that it's not the recommended approach.

Regards, Fernando Da Rós

[Former Member · Accepted solution]

Hello Fernando,

Thank you very much for the Blog. You answered more than the question.

Regards,

Ravi.