CODEWITHSUNDEEP
sqloracle-database
user3015739
user3015739

Reputation: 643

Strange SQL statement error using join

This is not the first time I am writing SQL statement and it is really bizarre to me what why I keep getting this error.

   SELECT
   CASE WHEN  VENDORDELIVERY IS NOT NULL THEN VENDORDELIVERY   ELSE 'No Delivery' END as " Vendor Delivery No.",
        DATE_TIME as "Delivery Date/Time",
        'z0' AS "Icon1",
        'z0' AS "Icon2",
        'z0' AS "Icon3",
        'z0' AS "Icon4",
            NVL(completion,0) AS "Progress",
        'd0' AS "Active",
        MATERIALCODE as "Material Code",
        MATERIAL as "Material",
        DELIVERY_QTY as "Delivery Qty",
        ASN_UNLOADINGSTATION AS "Unload. Manf",
        ASN_DESTINATIONTANK AS "DEST. Tank",
        STATUS AS "Status" , 'NO_DATA' AS "NoData1"
  FROM  XVW_ASN_OVERVIEW z
        RIGHT OUTER JOIN ( SELECT 'NO_DATA' AS NoData2 FROM DUAL ) b  
  ON z.NoData1 = b.NoData2;

The error I am getting are ORA-00904: "Z"."NODATA1": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action: Error at Line: 23 Column: 10

XVW_ASN_OVERVIEW is a view and I even try creating a column inside this view.

Both column are valid and when I run the SQL statement independently, no error occurs. So strange. Anything that I miss out ? Maybe I need another pair of eyes to point out my error.

Thanks for suggestion.

Upvotes: 0

Views: 72

Answers (3)

itsLex
itsLex

Reputation: 786

I see that you want one record saying "No_DATA" in case the query doesn't return any rows. For that you also need column NoData2. The parser wants to join the tables before it evaluates the selected columns, so it doesn't know you added NoData1. Unless you use your query as a temporary table like this:

SELECT *
FROM 
(SELECT 'NO_DATA' as NoData2 FROM DUAL) b
LEFT JOIN
(SELECT
  CASE WHEN  VENDORDELIVERY IS NOT NULL THEN VENDORDELIVERY   ELSE 'No Delivery' END as "Vendor Delivery No.",
  DATE_TIME as "Delivery Date/Time",
  'z0' AS "Icon1",
  'z0' AS "Icon2",
  'z0' AS "Icon3",
  'z0' AS "Icon4",
  NVL(completion,0) AS "Progress",
  'd0' AS "Active",
  MATERIALCODE as "Material Code",
  MATERIAL as "Material",
  DELIVERY_QTY as "Delivery Qty",
  ASN_UNLOADINGSTATION AS "Unload. Manf",
  ASN_DESTINATIONTANK AS "DEST. Tank",
  STATUS AS "Status" , 'NO_DATA' AS "NoData1"
FROM  XVW_ASN_OVERVIEW) Z
ON NoData1 = NoData2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

This isn't strange. You can't use an alias defined in the SELECT elsewhere in the same query.

Instead, here is one solution:

SELECT (CASE WHEN  VENDORDELIVERY IS NOT NULL THEN VENDORDELIVERY ELSE 'No Delivery' END) as " Vendor Delivery No.",
        DATE_TIME as "Delivery Date/Time",
       'z0' AS "Icon1",
       'z0' AS "Icon2",
       'z0' AS "Icon3",
       'z0' AS "Icon4",
        COALESCE(completion, 0) AS "Progress",
        'd0' AS "Active",
        MATERIALCODE as "Material Code",
        MATERIAL as "Material",
        DELIVERY_QTY as "Delivery Qty",
        ASN_UNLOADINGSTATION AS "Unload. Manf",
        ASN_DESTINATIONTANK AS "DEST. Tank",
        STATUS AS "Status",
        'NO_DATA' AS "NoData1"
  FROM ( SELECT 'NO_DATA' AS NoData2 FROM DUAL
       ) b LEFT JOIN
       XVW_ASN_OVERVIEW z
      b  
      ON 1 = 1;

That is, you don't really need a condition.

Note some other changes:

  • I switched to a LEFT JOIN. I find these much easier to follow -- because all the rows in the first table are kept.
  • I prefer COALESCE() to NVL() because the former is ANSI standard.

Upvotes: 0

Paul S
Paul S

Reputation: 171

For NoData1, you use a quoted identifier in your select and a non-quoted identifiers in your join statement. So try changing

      STATUS AS "Status" , 'NO_DATA' AS "NoData1"

to

      STATUS AS "Status" , 'NO_DATA' AS NoData1

Upvotes: 1

Related Questions