Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jan 15, 2015 at 10:03 AM

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

    Anyone care to comment on the appropriateness of the question ?

    Add comment
    10|10000 characters needed characters exceeded

    • 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.