Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Nov 04, 2015 at 06:38 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded