How to re-use a dynamically created column in a select statement for calculation?

[Former Member]

Hi guys,

I was wondering how can I reuse a dynamically created column ("qty" in this case) in a select statement for calculation?

Example as below:

  lt_mat = SELECT m.matnr, m.charg,
                  ( SELECT total_qty FROM :lt_quantity
                                    WHERE matnr = m.matnr 
                                      AND werks = m.werks
                                      AND lgort = m.lgort 
                                      AND charg = m.charg ) AS "qty", 
                  CASE
                    WHEN qty > 0
                        THEN
                            m.clabs - qty
                        ELSE
                           0
                  END AS quant,

I get the error: SQLSCRIPT Message: invalid column name: QTY as a result.

Thanks,

Sam

[lbreddemann · Accepted solution]

Your SQL syntax is wrong.

total_qry is not guaranteed to be a scalar here and might return no or multiple results.

So you cannot use it in the projection list like that.

Also, you cannot refer to projection list aliases in the projection list itself.

However, you could include the sub-select into the case statement.

Alternatively you could

a) create a UDF to perform the sub-query for you

or

b) have a nested SELECT that first computes QTY and then performs the projection and the CASE evaluation.

[Former Member · Accepted solution]

Thanks Lars for confirming.

I already did b.) a minute after i started this thread.