Volatile table trouble

Database
N/A

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

;

1 REPLY
Senior Supporter

Re: Volatile table trouble

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
;