Left Join : isnull(T1.Field, T2.Field)

[Former Member]

I'm translating some legacy SQL into HANA model.... and I'm going around in circles with this one.

Here's some 'traditional' SQL to illustrate what I am trying to do:

SELECT

    ADRC_D.CLIENT

    , ADRC_D.ADDRNUMBER

    , COALESCE (ADRC_I.NAME1, ADRC_D.NAME1) AS Name1

FROM    

    ADRC_D

    LEFT OUTER JOIN

        ADRC_I

ON

    ADRC_D.CLIENT = ADRC_I.CLIENT AND

    ADRC_D.ADDRNUMBER = ADRC_I.ADDRNUMBER

In a calculation view, I tried IFFNULL, but that won't let me include columns in my expression, unless I've used them as 'select' columns ... and I do not want them as select columns.

This must be a really common requirement, so I am sure there is a standard method of achieving ?

Many Thanks

[Former Member · Accepted solution]

Ok, so 90 views and no reply ... suggests I'm asking something dumb ?

Anyone care to comment on the appropriateness of the question ?

[sreehari_vpillai · Accepted solution]

Hi Bush,

Yes if you want to use the IFNULL functionality in a graphical calculation view, you need to select the used columns first(By the way, not selecting columns doesn't make any sense. In your SQL also, you are selecting ADRC_I.NAME1 and ADRC_D.NAME1. In calculation view, you are selecting it directly instead ). Otherwise , you can go for a scripted calculation view with case statement in the selection to achieve the same.

Sreehari

[Former Member · Accepted solution]

"By the way, not selecting columns doesn't make any sense"

Not sure what makes you say that ?

If I select name1 from T1 and again form T2, and in addition, create my composite column; then any user of my model will be staring at 3 similar columns, 2 of which are 'incorrect' data.
Unnecessary confusion.

If however, I create my composite column and do not expose the originals, the users will see only 1 unambiguous column.

"In your SQL also, you are selecting ADRC_I.NAME1 and ADRC_D.NAME1"

I guess that depends on your definition of 'selecting' !!
I am not exposing the columns  as output.

[Former Member · Accepted solution]

However, you have provided the answer which is correct as far as the technical ability of HANA is concerned, so thank you 

[sreehari_vpillai · Accepted solution]

I will try to explain my understandings .

SELECT

    ADRC_D.CLIENT

    , ADRC_D.ADDRNUMBER

    , COALESCE (ADRC_I.NAME1, ADRC_D.NAME1) AS Name1

FROM    

    ADRC_D

    LEFT OUTER JOIN

        ADRC_I

ON

    ADRC_D.CLIENT = ADRC_I.CLIENT AND

    ADRC_D.ADDRNUMBER = ADRC_I.ADDRNUMBER

You are not selecting ADRC_I.NAME1, ADRC_D.NAME1 columns, but you are projecting it. For COALESCE (or any such calculated columns) calculation would happen for each and every record which is returned. That is why I said, we are selecting it . In a calculation view also, the selection will happen only in the semantic node.