CODEWITHSUNDEEP
sqloracle-database
Kristy Welsh
Kristy Welsh

Reputation: 8530

Error in where clause PL/SQL query selecting from dual

I am doing the following query,

SELECT 'Payment Token,Transaction ID,Create Date' 
  FROM dual 
 UNION ALL 
SELECT DISTINCT 
       pt.mw_payment_token ||','||
       t.mw_transaction_id ||','||
       t.create_date 
  FROM t_mw_payment_token pt, 
       t_mw_transaction t 
 where pt.mw_payment_token =  t.mw_payment_token 
 order by t.mw_transaction_id;

Error is:

ORA-00904: "T"."MW_TRANSACTION_ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 5 Column: 103

Why is the column mw_transaction_id invalid?

Edit:

t_mw_transaction table

ENABLECREATE TABLE WFMW.T_MW_TRANSACTION 
(
  MW_PAYMENT_TOKEN VARCHAR2(40 BYTE) NOT NULL,
  CREATE_DATE DATE DEFAULT NULL, 
  MW_TRANSACTION_ID NUMBER NOT NULL,
  TRANSACTION_TYPE NUMBER(*, 0),
  CONSTRAINT XPK_T_MW_TRANSACTION PRIMARY KEY 
  (
    MW_TRANSACTION_ID 
  )
  ENABLE 
)

mw_payment_token table

ENABLECREATE TABLE WFMW.T_MW_PAYMENT_TOKEN 
(
  MW_PAYMENT_TOKEN VARCHAR2(40 BYTE) NOT NULL 
, CREATED_BY VARCHAR2(50 BYTE) 
, MODIFIED_BY VARCHAR2(50 BYTE) 
, CREATE_DATE DATE 
, MODIFIED_DATE DATE 
, TOKENIZED_PAN VARCHAR2(20 BYTE) 
, CONSTRAINT XPKT_MW_PAYMENT_TOKEN PRIMARY KEY 
  (
    MW_PAYMENT_TOKEN 
  )
  ENABLE 
)

Upvotes: 2

Views: 2003

Answers (6)

Kristy Welsh
Kristy Welsh

Reputation: 8530

Here is the solution: 

    SELECT 'Payment Token,Transaction ID,Create Date'
    FROM dual 
    UNION ALL
    SELECT 
             mw_payment_token ||','||
             mw_transaction_id ||','||
             create_date 
        FROM 
            (SELECT 
             DISTINCT
             pt.mw_payment_token,
             t.mw_transaction_id,
             t.create_date
             FROM t_mw_payment_token pt, 
             t_mw_transaction t 
             WHERE pt.mw_payment_token =  t.mw_payment_token 
             ORDER BY t.mw_transaction_id);

Upvotes: 0

EyeKneel
EyeKneel

Reputation: 16

order by 1 OR give your column a nice alias in both queries (only 1st matters) and order by it

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Sorry, I was in a hurry when I wrote the other answer. Here is an untested, but hopefully working solution. First collect all records, giving your header a low sort key. Then sort:

SELECT the_string
FROM
(
  SELECT 'Payment Token,Transaction ID,Create Date' as the_string, -1 as sort_key
    FROM dual 
   UNION ALL
  SELECT DISTINCT 
           pt.mw_payment_token ||','|| t.mw_transaction_id ||','|| t.create_date as the_string,
           t.mw_transaction_id as sort_key
    FROM t_mw_payment_token pt 
    JOIN t_mw_transaction t ON pt.mw_payment_token =  t.mw_payment_token
)
ORDER BY sort_key

Upvotes: 0

Kristy Welsh
Kristy Welsh

Reputation: 8530

Removing the DISTINCT clause from Thorsten Kettner's answer allowed the query to run. 

SELECT 'Payment Token,Transaction ID,Create Date'
  FROM dual 
 UNION ALL
SELECT * FROM
( 
  SELECT 
         pt.mw_payment_token ||','||
         t.mw_transaction_id ||','||
         t.create_date 
    FROM t_mw_payment_token pt, 
         t_mw_transaction t 
   where pt.mw_payment_token =  t.mw_payment_token 
   order by t.mw_transaction_id
);

Upvotes: 0

Armunin
Armunin

Reputation: 996

Your problem is the ORDER BY-clause because it's global over the whole query and doesn't recognize t.mw_transaction_id in the whole statement.
Same problem as in:

select 'a' from dual 
union all
select dummy from dual
order by dummy;

You can fix that by giving the columns in both queries the same alias:

select 'a' some_col from dual 
union all
select dummy some_col from dual
order by some_col;

But I don't think that is what you want to achieve.

Edit:
I am not sure what you want to achieve with your query, but maybe this gives you the desired result:

SELECT DISTINCT 
       pt.mw_payment_token "Payment Token",
       t.mw_transaction_id "Transaction ID",
       t.create_date "Create Date"
  FROM t_mw_payment_token pt, 
       t_mw_transaction t 
 where pt.mw_payment_token =  t.mw_payment_token 
 order by "Transaction ID";

Edit2:
If you want your output as CSV, maybe check here or here or here. Maybe consider asking another question here on SO.
My suggestion would be to use the UTL_FILE package iterating over a cursor that is based on my first edit. But I have not tried that so far.

Upvotes: 4

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want an order by clause for only the second part of your statement. Hence:

SELECT 'Payment Token,Transaction ID,Create Date'
  FROM dual 
 UNION ALL
SELECT * FROM
( 
  SELECT DISTINCT 
         pt.mw_payment_token ||','||
         t.mw_transaction_id ||','||
         t.create_date 
    FROM t_mw_payment_token pt, 
         t_mw_transaction t 
   where pt.mw_payment_token =  t.mw_payment_token 
   order by t.mw_transaction_id
);

Upvotes: 1

Related Questions