TD16 PIVOT BUG?

Database
Highlighted
Enthusiast

TD16 PIVOT BUG?

Hi all.  Been working with the new TD16 PIvot function and I have run into something that I want to see if anyone else has run into.  I think it might be a bug but I am not sure.  I have a table which gives the following structure from a select *:

 

Ref_Prod_Location,Trans_Date,Trans_Hour,Trans_Min,Trans_Timestamp,Tag_ID,Tag_Value,Last_Update_Dttm
Pascagoula,2013-11-02,20,24,2013-11-02 20:24:00,11TC1220,,2019-05-06 14:47:45
Pascagoula,2013-11-02,16,34,2013-11-02 16:34:00,11AI2389,,2019-05-06 14:48:06
Pascagoula,2013-11-02,10,34,2013-11-02 10:34:00,11PI0201,,2019-05-06 14:47:49
Pascagoula,2013-11-02,10,6,2013-11-02 10:06:00,CU1_STAB_ONSTS,,2019-05-06 14:48:26
Pascagoula,2013-11-02,23,51,2013-11-02 23:51:00,11TI0222,,2019-05-06 14:47:55
Pascagoula,2013-11-02,6,10,2013-11-02 06:10:00,CU1_STAB_ONSTS,,2019-05-06 14:48:26
Pascagoula,2013-11-02,1,3,2013-11-02 01:03:00,11PI1262,,2019-05-06 14:47:40
Pascagoula,2013-11-02,6,19,2013-11-02 06:19:00,11PI0201,,2019-05-06 14:47:49
Pascagoula,2013-11-02,3,1,2013-11-02 03:01:00,11PI1262,,2019-05-06 14:47:40
Pascagoula,2013-11-02,19,6,2013-11-02 19:06:00,11PI0201,,2019-05-06 14:47:49
Pascagoula,2013-11-02,1,43,2013-11-02 01:43:00,11TC1021,,2019-05-06 14:48:00
Pascagoula,2013-11-02,12,51,2013-11-02 12:51:00,11PI1262,,2019-05-06 14:47:40
Pascagoula,2013-11-02,4,17,2013-11-02 04:17:00,11TI8567,,2019-05-06 14:48:13

 

Note that the tag_value column here shows all nulls in this output but that is not usually the case.

 

When I report data from this table, it is common to pivot the data so that we show something like the average tag_value per day for a specific set of tags.  Back before TD16 I would do this in CASE statements where I specified the columns explicitly like so:

 

SELECT trans_date,
AVG(CASE WHEN tag_id='18C9236PV_CV_SSTarget' THEN tag_value END) AS "18C9236PV_CV_SSTarget",
AVG(CASE WHEN tag_id='11C2160PV_CV_OpHiLim' THEN tag_value END) AS "11C2160PV_CV_OpHiLim",
AVG(CASE WHEN tag_id='11TI8567' THEN tag_value END) AS "11TI8567",
AVG(CASE WHEN tag_id='11PI0201' THEN tag_value END) AS "11PI0201"
FROM pi
WHERE tag_id IN ('18C9236PV_CV_SSTarget','11C2160PV_CV_OpHiLim','11TI8567','11PI0201')

and trans_date > '2014/06/03'
GROUP BY trans_date
ORDER BY trans_date;

 

Easy peasy, works fine in both TD15 and TD16.  Gives an output like this:

 

Trans_Date,18C9236PV_CV_SSTarget,11C2160PV_CV_OpHiLim,11TI8567,11PI0201
2014-06-04,,,436.62886104166665,9.045306736111112
2014-06-05,,,437.38247666666666,9.176348333333333
2014-12-10,13.644751458333333,179.0,,
2014-12-11,17.147640972222224,179.0,,
2015-01-03,,,433.4057971527778,10.047038680555556
2015-01-04,,,433.27078222222224,9.512084861111111
2015-01-05,,,290.85355590277777,64.2539679861111
2015-01-06,,,105.59260861111112,105.07251583333333
2015-01-07,,,71.5487770138889,105.07370388888889

 

With TD 16 though I wanted to use the new Pivot function to not only make the syntax simpler but also to make the column list dynamic and this is where I have run into a problem.  If I use the SQL below with specified column names in the pivot, I can replicate the above output with no problem:

 

SELECT *
FROM (SELECT trans_date,tag_id,tag_value FROM pi WHERE trans_date > '2014/06/03') AS pitemp
PIVOT (AVG(tag_value) FOR tag_id IN (
SELECT DISTINCT tag_id FROM pi
WHERE tag_id IN ('18C9236PV_CV_SSTarget','11C2160PV_CV_OpHiLim','11TI8567','11PI0201')
))AS PIPivot
ORDER BY trans_date;

 

Where this gets strange though is if I replace the explicit column list for tag_id with a dynamic SQL statement like the following:

 

SELECT *
FROM (SELECT trans_date, tag_id, tag_value FROM pi WHERE trans_date > '2014/06/03') AS pitemp
PIVOT (AVG(tag_value) FOR tag_id IN (
SELECT DISTINCT tag_id
FROM pi
--WHERE tag_id IN ('18C9236PV_CV_SSTarget','11C2160PV_CV_OpHiLim','11TI8567','11PI0201')
))AS PIPivot
ORDER BY trans_date;

 

I get an output that looks like this:

Trans_Date,PIVOTAGGR1
2014-06-04,
2014-06-05,
2014-12-10,
2014-12-11,
2015-01-03,
2015-01-04,
2015-01-05,
2015-01-06,
2015-01-07,

 

but if I uncomment row 6 in my dynamic SQL, it works fine.  It should be noted that there are roughly 124 distinct tag_id values and ~3M total rows in the table.

 

Anyone have any idea what is happening here?  This feels like a bug but maybe there is some buffer that I am overruning because I have too many columns in my distinct select?  All help would be appreciated.


Accepted Solutions
Teradata Employee

Re: TD16 PIVOT BUG?

Hi brandda,

 

In 16.20, the in-list sub-select is limited to only 16 rows.

It's a nice to have feature but it feels incomplete : the lack of order by and this restriction makes the usability very restricted.

 

1 ACCEPTED SOLUTION
2 REPLIES 2
Teradata Employee

Re: TD16 PIVOT BUG?

Hi brandda,

 

In 16.20, the in-list sub-select is limited to only 16 rows.

It's a nice to have feature but it feels incomplete : the lack of order by and this restriction makes the usability very restricted.

 

Enthusiast

Re: TD16 PIVOT BUG?

Well heck.  Now I feel a little silly.  The 16 column limit is right there in the documentation.  Agreed with you though that it feels pretty restrictive.  I had pretty much decided even without this problem that I would stick with syntax.  It's a bit cumbersome but very flexible.

 

Anyway, thanks for the help.