Skip to Content

More concise SELECT in SQL Script

I have a select which uses the same test for a number of fields. Is there more concise way of getting the same result?

outTab = select

             case

                 when coord <> ' ' and prorder.prorder <> ' '

                 then prorder.entry

                 else :intab.entry

             end as entry

                

             case

                 when coord <> ' ' and prorder.prorder <> ' '

                 then prorder.auart

                 else :intab.auart  

             end as auart

    left outer join proder_tab as prorder

                   on prorder.prorder = :intab.coord;

The idea is that if there is an matching record in table proder_tab, use the values from that, rather than the ones from :intab.

This is part of an AMDP in 7.4.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Aug 19, 2016 at 11:15 PM

    Hello Matthew,

    assuming that your :intab table is the left table (I miss that in your example above), the left outer join will produce null values for not matching/existant lines iin proder_tab. This enables the usage of the COALESCE function which returns the first non null value.

    So an option would be:

    outTab = select coalesce(prorder.entry, :intab.entry) as entry,
                            coalesce(prorder.auart, :intab.auart) as auart
                  from :intab left outer join prorder_tab as prorder
                    on :intab.coord = prorder.prorder;
    

    Regards,

    Florian

    Add comment
    10|10000 characters needed characters exceeded