SQL Merge statement will not accept some of my identifiers

Question

Hi i have created the following SQL statement to help me merge data from one table into another table. The following code seems t o be working fine.

MERGE INTO FTOURSTATS ts
USING (SELECT * FROM guest_tour) t
ON (t.TOUR_ID = ts.TOURID AND t.START_DATETIME = (SELECT CALENDARDATE FROM FTIMEPERIOD tp WHERE ts.TIMEID = tp.TIMEID) AND ((SELECT SYSDATE FROM DUAL) - t.START_DATETIME ) < 7)
WHEN MATCHED THEN 
UPDATE SET 
ts.TOTALREVENUE = ts.TOTALREVENUE + ((SELECT COST_PER_DAY FROM TOUR WHERE TOUR_ID = t.TOUR_ID) * 0.1),
ts.TOTALTOURISTS = ts.TOTALTOURISTS + t.GROUP_SIZE

However when i enter the following code

WHEN NOT MATCHED THEN 
INSERT VALUES( (SELECT TIMEID FROM FTIMEPERIOD WHERE CALENDARDATE = t.TOUR_ID),1, t.TOUR_ID,(SELECT COST_PER_DAY FROM TOUR WHERE TOUR_ID = t.TOUR_ID) * 0.1,t.group_size);

I I get the following error

Error at Command Line : 10 Column : 69
Error report -
SQL Error: ORA-00904: "T"."TOUR_ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

But all of the identifiers worked in the section above. Is this maybe becuase of a scope issue?. Any help would be much appreciated.I want both the Matched and the NOT Matched to do something. They marked this as a duplicate for when nopthing must be done in the matched but this does not help me at all

Littlefoot · Answer 1 · 2019-10-08T19:28:47.083

0

Would this help?

UPDATE SET 
ts.TOTALREVENUE = (select ts.TOTALREVENUE + r.COST_PER_DAY * 0.1
                   FROM TOUR r
                   WHERE r.TOUR_ID = t.TOUR_ID
                  )

Function option I mentioned in a comment:

create or replace function f_cpd (par_tour_id in tour.tour_id%type)
  return tour.cost_per_day%type
as
  retval tour.cost_per_day%type;
begin
  select cost_per_day * 0.1
    into retval
    from tour
    where tour_id = par_tour_id;

  return retval;
end;

values
( (SELECT TIMEID FROM FTIMEPERIOD WHERE CALENDARDATE = t.START_DATETIME),
   1, 
   t.TOUR_ID,
   f_cpd(t.tour_id),            --> use it here
   t.group_size
);

edited Oct 08 '19 at 19:28

answered Oct 08 '19 at 19:07

Littlefoot

131,892
15
35
57

The UPDATE Set is working fiine it is the INSERT That is giving the issues i belive – Steve Rogers Oct 08 '19 at 19:16
Ah, sorry; somehow, I suffered from partial blindness and didn't see what was written. It must be the COST_PER_DAY select statement that causes problems; I agree with you, TOUR_ID seems to be too deep. Try to rewrite those SELECT statements into functions and call them by passing T.TOUR_ID as a parameter. Yes, I know - context switching and stuff, but ... see if it helps. I edited my message by adding an example to show what I meant. – Littlefoot Oct 08 '19 at 19:26
But the cost section is fine there are no problems if i simply run it up till before the Insert but if I run the Insert then there are issues. – Steve Rogers Oct 08 '19 at 19:32
Right; code I posted should be used instead of the INSERT part. – Littlefoot Oct 08 '19 at 19:32

score 0 · Answer 2 · answered Oct 08 '19 at 21:02

Join the TOUR table to the GUEST_TOUR table in the USING clause (assuming each GUEST_TOUR row relates to a single TOUR row) and then you can use it in the UPDATE clause:

MERGE INTO FTOURSTATS ts
USING (
  SELECT gt.*,
         t.cost_per_day
  FROM   guest_tour gt
         LEFT OUTER JOIN tour t
         ON gt.tour_id = t.tour_id
) src
ON (   src.TOUR_ID = ts.TOURID
   AND EXISTS( SELECT 1
               FROM   FTIMEPERIOD tp
               WHERE  tp.CALENDARDATE = src.START_DATETIME
               AND    tp.TIMEID       = ts.TIMEID )
  AND SYSDATE - src.START_DATETIME < 7
)
WHEN MATCHED THEN 
UPDATE SET 
  TOTALREVENUE  = ts.TOTALREVENUE  + src.cost_per_day * 0.1,
  TOTALTOURISTS = ts.TOTALTOURISTS + src.GROUP_SIZE

SQL Merge statement will not accept some of my identifiers

2 Answers2