Hi Everyone,
I am running this select query in oracle. It keeps running until I stop it explicitly. This issue is coming because of the last where clause condition with aggregate function highlighted in the image below.
SELECT "DIMLOOKUP_CUSTOMER_CUSTOMER_KEY", "DIMLOOKUP_CUSTOMER1_CUSTOMER_KEY", "DATE_INVOICE_DATE_KEY_OUTPUT_VALUE",
"DATE_DELIVERY_DATE_KEY_OUTPUT_VALUE", "FACTTABLELOAD_STAGING_WWI_INVOICE_ID", "FACTTABLELOAD_STAGING_QUANTITY",
"FACTTABLELOAD_STAGING_UNIT_PRICE"
FROM (
SELECT "FACTTABLELOAD_STAGING"."DELIVERY_DATE_KEY" "FACTTABLELOAD_STAGING_DELIVERY_DATE_KEY", "FACTTABLELOAD_STAGING"."INVOICE_DATE_KEY" "FACTTABLELOAD_STAGING_INVOICE_DATE_KEY", "FACTTABLELOAD_STAGING"."CUSTOMER_WWI_CUSTOMER_ID" "FACTTABLELOAD_STAGING_CUSTOMER_WWI_CUSTOMER_ID",
"FACTTABLELOAD_STAGING"."CUSTOMERBILLTO_WWI_CUSTOMER_ID" "FACTTABLELOAD_STAGING_CUSTOMERBILLTO_WWI_CUSTOMER_ID", "FACTTABLELOAD_STAGING"."WWI_INVOICE_ID" "FACTTABLELOAD_STAGING_WWI_INVOICE_ID", "FACTTABLELOAD_STAGING"."QUANTITY" "FACTTABLELOAD_STAGING_QUANTITY",
"FACTTABLELOAD_STAGING"."UNIT_PRICE" "FACTTABLELOAD_STAGING_UNIT_PRICE", "DIMLOOKUP_CUSTOMER"."CUSTOMER KEY" "DIMLOOKUP_CUSTOMER_CUSTOMER_KEY", "DIMLOOKUP_CUSTOMER1"."CUSTOMER KEY" "DIMLOOKUP_CUSTOMER1_CUSTOMER_KEY",
"FACTTABLELOAD_STAGING"."DELIVERY_DATE_KEY" "DATE_DELIVERY_DATE_KEY_INPUT_DATETIME", TO_CHAR("FACTTABLELOAD_STAGING"."DELIVERY_DATE_KEY", 'YYYYMMDD') "DATE_DELIVERY_DATE_KEY_OUTPUT_VALUE", "FACTTABLELOAD_STAGING"."INVOICE_DATE_KEY" "DATE_INVOICE_DATE_KEY_INPUT_DATETIME",
TO_CHAR("FACTTABLELOAD_STAGING"."INVOICE_DATE_KEY", 'YYYYMMDD') "DATE_INVOICE_DATE_KEY_OUTPUT_VALUE"
FROM "STG_FACTTABLELOAD_6364_ASTWK_108" "FACTTABLELOAD_STAGING"
LEFT OUTER JOIN "WWI_DESTINATION_REGRESSION"."CUSTOMER" "DIMLOOKUP_CUSTOMER"
ON ("FACTTABLELOAD_STAGING"."CUSTOMER_WWI_CUSTOMER_ID" = "DIMLOOKUP_CUSTOMER"."WWI CUSTOMER ID")
LEFT OUTER JOIN "WWI_DESTINATION_REGRESSION"."CUSTOMER" "DIMLOOKUP_CUSTOMER1"
ON ("FACTTABLELOAD_STAGING"."CUSTOMERBILLTO_WWI_CUSTOMER_ID" = "DIMLOOKUP_CUSTOMER1"."WWI CUSTOMER ID")
WHERE "DIMLOOKUP_CUSTOMER1"."VALID TO" = (
SELECT MAX("VALID TO")
FROM "WWI_DESTINATION_REGRESSION"."CUSTOMER" "MM"
WHERE "DIMLOOKUP_CUSTOMER1"."WWI CUSTOMER ID" = "MM"."WWI CUSTOMER ID"
AND "MM"."VALID TO" <= "FACTTABLELOAD_STAGING"."INVOICE_DATE_KEY")
AND "DIMLOOKUP_CUSTOMER"."VALID TO" = (
SELECT MAX("VALID TO")
FROM "WWI_DESTINATION_REGRESSION"."CUSTOMER" "MMM"
WHERE "DIMLOOKUP_CUSTOMER"."WWI CUSTOMER ID" = "MMM"."WWI CUSTOMER ID"
AND "MMM"."VALID TO" <= "FACTTABLELOAD_STAGING"."INVOICE_DATE_KEY"))
;
Can you please help me with this.
Thanks