Please help - rewrite query as left join for use in Terradata 12: 3706

[BillWestman]

SQL wizards: I have a query which needs to be written as with left joins so it will work with TD 12 (scalar subqueries do not work).  Here it is:  Thank you in advance for looking and hopefully soliving.  Currently the following yields: SELECT Failed. 3706:  Syntax error: expected something between '(' and the 'SELECT' keyword.  No explain available. 

 

select

      id,

      (select

            case

                  when Category_1 = b.MaxScore then 'Category_1'

                  when Category_2 = b.MaxScore then 'Category_2'

                  when Category_3 = b.MaxScore then 'Category_3'

                  else 'NA'

            end

      from myTable t where t.id = b.id

      ) as MaxScoreCategory,

      MaxScore

from (

      select

            id,

            max(Score) as MaxScore

      from (

      select id, 'Category_1', Category_1 from myTable union all

      select id, 'Category_2', Category_2 from myTable union all

      select id, 'Category_3', Category_3 from myTable

      ) a(id, Category, Score)

      group by id

) b

 

[kickstart]

Hi

 

Not sure which subselect you are trying to remove.

 

This removes 1 of them and does the job.

 

SELECT Z.id, Z.MaxScore,
CASE WHEN Z.MaxScore = Y.Category_1 THEN 'Category_1'
WHEN Z.MaxScore = Y.Category_2 THEN 'Category_2'
WHEN Z.MaxScore = Y.Category_3 THEN 'Category_3'
ELSE 'Error' END AS MaxScoreCategory
FROM (SELECT a.id, GREATEST( a.Category_1, a.Category_2, a.Category_3 ) AS MaxScore
FROM myTable a )Z
INNER JOIN myTable Y ON Z.id = Y.id

 

Removing all of the subselects (but not sure how efficient this will be as repeating a aggregate function)

 

SELECT id, GREATEST(Category_1, Category_2, Category_3) AS MaxScore, CASE WHEN GREATEST(Category_1, Category_2, Category_3) = Category_1 THEN 'Category_1'
WHEN GREATEST(Category_1, Category_2, Category_3) = Category_2 THEN 'Category_2'
WHEN GREATEST(Category_1, Category_2, Category_3) = Category_3 THEN 'Category_3'
ELSE 'Error' END AS MaxScoreCategory
FROM myTable

 

However these are both trying to bodge a way round a problem caused by incorrect table design. The scores should be hived off into a separate table.

 

All the best

 

Keith

[BillWestman]

Looks like the Greatest function is not supported in TD   sadness.  so far this works - but any advice about a 16 category solution would be helpful.

SELECT id,

CASE

WHEN category_1 > category_2 AND category_1 > category_3 THEN category_1

WHEN category_2 > category_3 THEN category_2

WHEN category_3 IS NOT NULL THEN category_3

ELSE 'NA'

END AS MaxScore,

CASE

WHEN category_1 > category_2 AND category_1 > category_3 THEN 'category_1'

WHEN category_2 > category_3 THEN 'category_2'

WHEN category_3 IS NOT NULL THEN 'category_3'

ELSE 'NA'

END AS MaxScoreCategory

FROM yourtable

 

 

[kickstart]

Hi

 

If you can redesign the table to split those columns off to be rows on a separate table.

 

If not then maybe use a SELECT to split them off (possibly use if for a view).

 

Set the following up as a view:-

 

SELECT Id, 'Category_1' AS Category, category_1 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_2' AS Category, category_2 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_3' AS Category, category_3 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_4' AS Category, category_4 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_5' AS Category, category_5 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_6' AS Category, category_6 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_7' AS Category, category_7 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_8' AS Category, category_8 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_9' AS Category, category_9 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_10' AS Category, category_10 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_11' AS Category, category_11 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_12' AS Category, category_12 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_13' AS Category, category_13 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_14' AS Category, category_14 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_15' AS Category, category_15 AS CategoryValue FROM yourtable
UNION
SELECT Id, 'Category_16' AS Category, category_16 AS CategoryValue FROM yourtable

 

Say call the above MaxCatView

 

SELECT a.Id, a.Category, a.CategoryValue
FROM MaxCatView a
INNER JOIN (SELECT Id, MAX(CategoryValue) AS MaxCategoryValue FROM MaxCatView GROUP BY Id) b
ON a.Id = b.Id AND a.CategoryValue = b.MaxCategoryValue

 

Not tested so please excuse any typos.

 

All the best

 

Keith