Getting an error for this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting an error for this query

Author

Topic

Suhas
Starting Member

3 Posts

Posted - 2008-05-17 : 12:26:07

Help me

Select Library_code, Repeats From (
Select LEFT(LIBRARY,4) as "Library_code", Count(LEFT(LIBRARY,4)) as "Repeats"
From LIBRARYPROFILE
where BUY=1 and ACTIVE >=1
Group by LEFT(LIBRARY,4)
order by Count( LEFT(LIBRARY,4))
)

When I run this query I get an error
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: ;);AS
Message: An unexpected token "" was found following ")". Expected tokens may include: "AS".

Can anybody help

Appreciate you reply

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-17 : 12:56:04

Select Library_code, Repeats From (
Select LEFT(LIBRARY,4) as "Library_code", Count(LEFT(LIBRARY,4)) as "Repeats"
From LIBRARYPROFILE
where BUY=1 and ACTIVE >=1
Group by LEFT(LIBRARY,4)
order by Count( LEFT(LIBRARY,4))
) as temp

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 15:12:22

quote:
Originally posted by Suhas

Help me

Select Library_code, Repeats From (
Select LEFT(LIBRARY,4) as "Library_code", Count(LEFT(LIBRARY,4)) as "Repeats"
From LIBRARYPROFILE
where BUY=1 and ACTIVE >=1
Group by LEFT(LIBRARY,4)
order by Count( LEFT(LIBRARY,4))
)

When I run this query I get an error
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: ;);AS
Message: An unexpected token "" was found following ")". Expected tokens may include: "AS".

Can anybody help

Appreciate you reply

There's no need of using quotes ("") for alias names.

Suhas
Starting Member

3 Posts

Posted - 2008-05-17 : 15:50:51

The below answer did not work

Select Library_code, Repeats From (
Select LEFT(LIBRARY,4) as "Library_code", Count(LEFT(LIBRARY,4)) as "Repeats"
From LIBRARYPROFILE
where BUY=1 and ACTIVE >=1
Group by LEFT(LIBRARY,4)
order by Count( LEFT(LIBRARY,4))
) as temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 16:13:29

quote:
Originally posted by Suhas

The below answer did not work

Select Library_code, Repeats From (
Select LEFT(LIBRARY,4) as "Library_code", Count(LEFT(LIBRARY,4)) as "Repeats"
From LIBRARYPROFILE
where BUY=1 and ACTIVE >=1
Group by LEFT(LIBRARY,4)
order by Count( LEFT(LIBRARY,4))
) as temp

Did you try removing quotes around aliases?

Suhas
Starting Member

3 Posts

Posted - 2008-05-18 : 19:47:46

quote:
Originally posted by visakh16

quote:
Originally posted by Suhas

The below answer did not work

Select Library_code, Repeats From (
Select LEFT(LIBRARY,4) as "Library_code", Count(LEFT(LIBRARY,4)) as "Repeats"
From LIBRARYPROFILE
where BUY=1 and ACTIVE >=1
Group by LEFT(LIBRARY,4)
order by Count( LEFT(LIBRARY,4))
) as temp

Did you try removing quotes around aliases?

yes and it did not work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-18 : 23:46:45

quote:
Originally posted by Suhas

quote:
Originally posted by visakh16

quote:
Originally posted by Suhas

The below answer did not work

Select Library_code, Repeats From (
Select LEFT(LIBRARY,4) as "Library_code", Count(LEFT(LIBRARY,4)) as "Repeats"
From LIBRARYPROFILE
where BUY=1 and ACTIVE >=1
Group by LEFT(LIBRARY,4)
order by Count( LEFT(LIBRARY,4))
) as temp

Did you try removing quotes around aliases?

yes and it did not work

I think its because of the order by inside. Either try removing order by or do like this:-

Select Library_code, Repeats From (
Select TOP 100 PERCENT LEFT(LIBRARY,4) as Library_code, Count(LEFT(LIBRARY,4)) as Repeats
From LIBRARYPROFILE 
where BUY=1 and ACTIVE >=1
Group by LEFT(LIBRARY,4)
order by Count( LEFT(LIBRARY,4))
) as temp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-19 : 04:19:53

BTW, this seems to be DB2 query. If it is then you are posting on wrong site.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 04:24:18

[code]SELECT LEFT(Library, 4) AS Library_code,
COUNT(Library) AS Repeats
FROM LibraryProfile
WHERE Buy = 1
AND Active >=1
GROUP BY LEFT(Library, 4)
ORDER BY COUNT(Library)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources