SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

Analytics
Enthusiast

SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

I am troubling over this error as I am not sure what the cause might be. Any help would be greatly appreciated.

select Id, (select
case
when Fall_score = b.MaxScore then 'Fall'
when Spring_score = b.MaxScore then 'Spring'
from sandbox.testtable t where t.Id = b.Id
) as MaxScoreCategory,MaxScore
from (select Id, max(Score) as MaxScore
from (select Id, 'Fall', Fall_score from sandbox.testtable union all
select Id, 'Spring', Spring_score from sandbox.testtable)
a (Id, Category, Score)
group by Id

) b

Result:
SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.
Tags (2)
20 REPLIES
Enthusiast

Re: SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

Which Teradata version are you using? Scalar subqueries in the select list were not supported until 13.0.
Enthusiast

Re: SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

The DB version is 12 (SQL asst is 13). can you think of a way to rewrite this?
Enthusiast

Re: SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

IF possible it will anyone could rewrite with LEFT JOIN include the N/A and end which I forgot -- I know Dieter has some examples. Any help is greatly appeciated-- again - this will NOT work in TD 12. 3706
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 YourTable t where t.id = b.id
) as MaxScoreCategory,
MaxScore
from (
select
id,
max(Score) as MaxScore
from (
select id, 'Category_1', Category_1 from YourTable union all
select id, 'Category_2', Category_2 from YourTable union all
select id, 'Category_3', Category_3 from YourTable
) a(id, Category, Score)
group by id
) b
Senior Apprentice

Re: SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

Looks like you want the max of three columns.
Your query is overly complex, you don't need any Scalar Subquery or Left Join to get the result set:

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

Dieter

Enthusiast

Re: SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

Dieter - perfect. this works. I made the example a little too simple. Would you use the same strategy for 10 categories? This query totally works! Thanks! If you would use something different, than what would that be
Senior Apprentice

Re: SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

A huge CASE just looks ugly, but it's probably the most efficient way, so i'd stick with it for much more than 10 categories.
I once wrote a batch which created a nested case with a few thousand values based on the data in a column. This resulted in more than 200kb souce code :-)

Dieter

Enthusiast

Re: SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

Just another way to do it could be:-

sel a.* from
(select Id, 'Fall' as category, Fall_score score from sandbox.testtable union all
select Id, 'Spring' , Spring_score from sandbox.testtable)A
QUALIFY ROW_NUMBER () OVER (PARTITION BY a.id ORDER BY a.score DESC) = 1;

Since this may mean, 1 additional roll of the the same table for each category, it may not be as efficient as dieter suggested.

Correct Dieter ;)
N/A

Re: SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

Hello i have the same problem but in update the inner selet goes its self but update dont:

UPDATE tsp_project_0002

FROM

(SELECT 

x.analisys_period  AS analisys_period

, CAST (CASE WHEN LEFT(TRIM(x.analisys_period) , 6) = 'YTD' THEN z.cy_first_month WHEN LEFT( TRIM(x.analisys_period) , 6)  = 'MTD' THEN  z.current_month ELSE CAST(LEFT( TRIM(x.analisys_period) , 6)  AS DECIMAL (6,0)) END AS DECIMAL (6,0)) period_from

FROM

 intccr02_support.tsp_project_0002 x

CROSS JOIN tsp_project_dates z

)v

SET analisys_from = v.period_from

WHERE v.analisys_period = tsp_project_0002.analisys_period

Can you help me ?

Enthusiast

Re: SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

HI Dieter,

I have some questions on DBC space ?

First of all i need to increatse space on DBC .(Here we can't add more disk sapce )only the option is we can reduce space from other db's or users ,then the space is  should be increase on DBC ? Right ?

And we use some query  i.e ,

SELECT tablename, SUM(currentperm), SUM(peakperm) FROM dbc.tablesize WHERE databasename = 'dbc' GROUP BY 1 QUALIFY RANK() OVER (ORDER BY SUM(currentperm) DESC) <= 20;

Result is : 

TransientJournal 210,030,166,016.00 210,030,166,016.00;EventLog 6,782,329,856.00 6,782,329,856.00;DBQLogTbl 3,166,243,840.00 3,166,243,840.00;DBQLSqlTbl 1,779,373,056.00 1,779,373,056.00;SW_Event_Log 1,551,524,864.00 1,551,524,864.00

;RCEvent 40,189,952.00 40,189,952.00;TVFields 39,965,184.00 45,026,816.00;

TVM 25,444,352.00 27,667,456.00;AccessRights 9,964,032.00 10,184,192.00

;DataBaseSpace 7,659,520.00 7,669,248.00;TextTbl 5,172,224.00 8,936,448.00

;Indexes 1,152,000.00 1,177,600.00;ResUsageSps 1,106,944.00 1,106,944.00

;ResUsageSvpr 864,256.00 864,256.00;ErrorMsgs 797,696.00 797,696.00

;ResUsageIvpr 403,456.00 403,456.00;ConstantDefs 333,824.00 333,824.00

;ConstantValues 294,912.00 294,912.00;PasswordRestrictions 163,840.00 163,840.00

ResUsageSawt 120,832.00 120,832.00

Like this i got result ..First question is  this query is Right or not ? To find this Result ? ?

in this what are all the delete from dictonary tables.Once we delete some space  from above result, and we get some space add in to DBC Right ? ?

Can you pelase help me inthis query ?

How can i delete space  to get more increase in dbc ? ?

Please help me ? any one 

Thanks in advance