All Forums Database
mkua 1 post Joined 11/12
08 Nov 2012
Volatile table trouble

I'm trying to pass information from a Volatile table to the FROM statement in the query below.
The SDATE and EDATE pass just fine, but having trouble with siteA and siteB.
It keeps expecting something between the word siteA and the "," in the FROM statement.
Any help would be appreciated.
 
CREATE VOLATILE TABLE VAR AS

(
SELECT
 '2012-06-01' AS SDATE,
 '2012-06-30' AS EDATE,
 'DTZ' AS siteA,
 'TYO' AS siteB
)
WITH DATA PRIMARY INDEX (SDATE, EDATE )
ON COMMIT PRESERVE ROWS
;

Select
  Extract (MONTH from creation_dt) as Mnth
, Extract (YEAR from creation_dt) as Yr
, Orignl_Agent_AAA
, Orignl_Agent_Sine
, count(*) as Bookings

FROM Vw_Pnr WHERE  Orignl_Agent_AAA IN ( VAR.siteA, VAR.siteB )  and creation_dt between VAR.SDATE and VAR.EDATE

group by 1, 2, 3, 4
;

ulrich 816 posts Joined 09/09
09 Nov 2012

IN has two different interpretations 
1. LIST of values
2. Subquery
You mix it which is not working.
The best performance might (in case Orignl_Agent_AAA is not the PI of the VW_Pnr table) be achived via

CREATE VOLATILE TABLE VAR AS

(
SELECT
1 as id,
 '2012-06-01' AS SDATE,
 '2012-06-30' AS EDATE,
 'DTZ' AS siteA,
 'TYO' AS siteB
)
WITH DATA unique PRIMARY INDEX (id)
ON COMMIT PRESERVE ROWS
;

Select
  Extract (MONTH from creation_dt) as Mnth
, Extract (YEAR from creation_dt) as Yr
, Orignl_Agent_AAA
, Orignl_Agent_Sine
, count(*) as Bookings

FROM Vw_Pnr 
WHERE ( (select siteA from var where id = 1) = Orignl_Agent_AAA
                or (select siteb from var where id = 1) = Orignl_Agent_AAA)
                and  (select sdate from var where id = 1) <= creation_dt
                and  (select edate from var where id = 1) >= creation_dt
group by 1, 2, 3, 4
;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.