David David - 5 months ago 10
SQL Question

Combine CONCAT and LAG functions

To start, this relates to a post on how to Flag records based on record's date vs history, which was successfully resolved. I have decided to open a new topic for two reasons:


  1. This is a new issue (although it relates to the previous one).

  2. Someone may find the solution from the original post useful, so I did not want it changed.



I am currently running the following query (provided by @Mottor):

SELECT
"REGION"
, "COUNTRY"
, "IDNum"
, "CUSTOMER"
, "BUSSINESS"
, "FISCALYEARMONTH"
, "FISCALYEAR"
, SUM("VALUE")
, MAX(CASE WHEN to_date(prev_fym,'YYYYMM') >= ADD_MONTHS (to_date("FISCALYEARMONTH",'YYYYMM'), -24) THEN 'Existing'
ELSE 'New ' END) "HISTORICAL_PURCHASE_FLAG"
FROM
(
SELECT
T1."REGION"
, T1."COUNTRY"
, T1."IDNum"
, T1."CUSTOMER"
, T2."BUSSINESS"
, T3."FISCALYEARMONTH"
, T3."FISCALYEAR"
, T4."VALUE"
, LAG ("FISCALYEARMONTH", 1) OVER (PARTITION BY T1."IDNum" ORDER BY T3."FISCALYEARMONTH" DESC) prev_fym
FROM
"DATABASE"."SALES" T4
, "DATABASE"."CUSTOMER" T1
, "DATABASE"."PRODUCT" T2
, "DATABASE"."TIME" T3
WHERE
T4."CUSTOMERID" = T1."CUSTOMERID"
AND T4."PRODUCTID" = T2."PRODUCTID"
AND T4."DATEID" = T3."DATEID"
AND T1."COUNTRY" IN ('ENGLAND', 'France')
AND T3."FISCALYEAR" IN ('2014','2015','2016')
)
WHERE "FISCALYEAR" IN ('2016')
GROUP BY
"REGION"
, "COUNTRY"
, "IDNum"
, "CUSTOMER"
, "BUSSINESS"
, "FISCALYEARMONTH"
, "FISCALYEAR"
;


As a result I get a list of all transactions from fiscal year 2016. In addition, LAG function allows me to flag records as 'New' or 'Existing', based on "IDNum" and "FISCALYEARMONTH". I get the following result:

LineNum REGION COUNTRY IDNum CUSTOMER BUSSINESS FISCALYEARMONTH FISCALYEAR VALUE HISTORICAL_PURCHASE_FLAG
1 Europe ENGLAND 255 Abraxo Cleaner Co. Chemicals 201605 2016 34,567.00 Existing
2 Europe FRANCE 123 Metal Trade Heavy 201602 2016 12,500.00 Existing
3 Europe ENGLAND 255 Abraxo Cleaner Co. Mining 201601 2016 8,400.00 New


The issue I am facing at the moment is that a single customer can actually belong to multiple businesses. If you look at the above example, you will notice that Abraxo Cleaner Co. belongs to Chemicals and Mining.

I was trying to create a new column called "CUSTOMER_BUSSINESS_KEY" which would be a concatenation:
CONCAT(T1."IDNum",T2."BUSSINESS")
.

Next, I wanted to modify the LAG function as follows:
LAG ("FISCALYEARMONTH", 1) OVER (PARTITION BY "CUSTOMER_BUSSINESS_KEY" ORDER BY T3."FISCALYEARMONTH" DESC) prev_fym
. This should allow me to get records flagged as 'New' and 'Existing'based on the newly created column, instead of "IDNum".

Problem is, when I run the query I get an error message saying:


ORA-00904: "CUSTOMER_BUSSINESS_KEY": invalid identifier


My understanding is that SQL cannot find table name for "CUSTOMER_BUSSINESS_KEY". Unfortunately I cannot figure out a way around it.

Answer

You can't use an alias within the same Select, either add another Derived Table or use the calculation instead:

SELECT
"REGION"
, "COUNTRY"
, "IDNum"
, "CUSTOMER"
, "BUSSINESS"
, "CUSTOMER_BUSSINESS_KEY"
, "FISCALYEARMONTH"
, "FISCALYEAR"
, SUM("VALUE") 
, MAX(CASE WHEN to_date(prev_fym,'YYYYMM')  >= ADD_MONTHS (to_date("FISCALYEARMONTH",'YYYYMM'), -24) THEN 'Existing' 
           ELSE 'New ' END) "HISTORICAL_PURCHASE_FLAG"
FROM
(
    SELECT
    T1."REGION"
    , T1."COUNTRY"
    , T1."IDNum"
    , T1."CUSTOMER"
    , T2."BUSSINESS"
    , CONCAT(T1."IDNum",T2."BUSSINESS") AS "CUSTOMER_BUSSINESS_KEY"
    , T3."FISCALYEARMONTH"
    , T3."FISCALYEAR"
    , T4."VALUE"
    , LAG ("FISCALYEARMONTH", 1)
      OVER (PARTITION BY CONCAT(T1."IDNum",T2."BUSSINESS")
            ORDER BY T3."FISCALYEARMONTH" DESC) prev_fym 
    FROM
    "DATABASE"."SALES" T4
    , "DATABASE"."CUSTOMER" T1
    , "DATABASE"."PRODUCT" T2
    , "DATABASE"."TIME" T3
    WHERE
    T4."CUSTOMERID" = T1."CUSTOMERID"
    AND T4."PRODUCTID" = T2."PRODUCTID"
    AND T4."DATEID" = T3."DATEID"
    AND T1."COUNTRY" IN ('ENGLAND', 'France')
    AND T3."FISCALYEAR" IN ('2014','2015','2016')
)
WHERE "FISCALYEAR" IN ('2016')
GROUP BY
"REGION"
, "COUNTRY"
, "IDNum"
, "CUSTOMER"
, "BUSSINESS"
, "FISCALYEARMONTH"
, "FISCALYEAR"
;

But you can also simply use multiple columns in PARTITION:

SELECT
"REGION"
, "COUNTRY"
, "IDNum"
, "CUSTOMER"
, "BUSSINESS"
, "FISCALYEARMONTH"
, "FISCALYEAR"
, SUM("VALUE") 
, MAX(CASE WHEN to_date(prev_fym,'YYYYMM')  >= ADD_MONTHS (to_date("FISCALYEARMONTH",'YYYYMM'), -24) THEN 'Existing' 
           ELSE 'New ' END) "HISTORICAL_PURCHASE_FLAG"
FROM
(
    SELECT
    T1."REGION"
    , T1."COUNTRY"
    , T1."IDNum"
    , T1."CUSTOMER"
    , T2."BUSSINESS"
    , T3."FISCALYEARMONTH"
    , T3."FISCALYEAR"
    , T4."VALUE"
    , LAG ("FISCALYEARMONTH", 1)
      OVER (PARTITION BY T1."IDNum",T2."BUSSINESS"
            ORDER BY T3."FISCALYEARMONTH" DESC) prev_fym 
    FROM
    "DATABASE"."SALES" T4
    , "DATABASE"."CUSTOMER" T1
    , "DATABASE"."PRODUCT" T2
    , "DATABASE"."TIME" T3
    WHERE
    T4."CUSTOMERID" = T1."CUSTOMERID"
    AND T4."PRODUCTID" = T2."PRODUCTID"
    AND T4."DATEID" = T3."DATEID"
    AND T1."COUNTRY" IN ('ENGLAND', 'France')
    AND T3."FISCALYEAR" IN ('2014','2015','2016')
)
WHERE "FISCALYEAR" IN ('2016')
GROUP BY
"REGION"
, "COUNTRY"
, "IDNum"
, "CUSTOMER"
, "BUSSINESS"
, "FISCALYEARMONTH"
, "FISCALYEAR"
;