IF gives AS problems - HELP - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

IF gives AS problems - HELP

Author

Topic

thorv
Starting Member

7 Posts

Posted - 2002-12-29 : 17:18:04

Can anyone see what i do wrong with this SQL !

The SQL:
SELECT MAX(totalt_poeng) AS Poeng, fh_navn, di_sum, (SELECT SUM(totalt_Poeng) / COUNT(fh_navn)) AS Gjennomsnitt
IF (SELECT MAX(totalt_Poeng) / di_sum)< 460
BEGIN
(SELECT '<hr align="left" width="' + (CONVERT(Varchar(10), (SELECT MAX(totalt_Poeng) / di_sum)) + '" color="#FFFF00" noshade size="20">')) AS Linje_Poeng2,
Else
IF (SELECT MAX(totalt_Poeng) / di_sum)> 460
(SELECT '<hr align="left" width="' + (CONVERT(Varchar(10), (SELECT MAX(totalt_Poeng) / di_sum)) + '" color="#FFFF00" noshade size="20">')) AS Linje_Poeng,
End
(SELECT '<hr align="left" width="' + CONVERT(Varchar(10), (SELECT SUM(totalt_Poeng) / COUNT(fh_navn) / di_sum)) + '" color="#FFFF00" noshade size="20">') AS Linje_Gjennomsnitt
FROM dbo.View_Deltakere_Poeng
GROUP BY fh_navn, di_sum

Gives me this answer:
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.

THANKS !

Thor

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-29 : 19:06:00

First of all I recommend moving the if's to your asp code.

CREATE TABLE #View_Deltakere_Poeng (fh_navn int, di_sum int, totalt_poeng int)

INSERT INTO #View_Deltakere_Poeng (fh_navn, di_sum, totalt_poeng) VALUES (1000,1,1)
INSERT INTO #View_Deltakere_Poeng (fh_navn, di_sum, totalt_poeng) VALUES (1000,1,2)
INSERT INTO #View_Deltakere_Poeng (fh_navn, di_sum, totalt_poeng) VALUES (1000,3,3000)
INSERT INTO #View_Deltakere_Poeng (fh_navn, di_sum, totalt_poeng) VALUES (1000,3,4000)
INSERT INTO #View_Deltakere_Poeng (fh_navn, di_sum, totalt_poeng) VALUES (1000,3,5000)

SELECT
MAX(A.totalt_poeng) As Poeng,
A.fh_navn,
A.di_sum,
(SELECT SUM(B.totalt_Poeng) / COUNT(B.fh_navn)
FROM #View_Deltakere_Poeng B
WHERE B.fh_navn = A.fh_navn AND B.di_sum = A.di_sum
GROUP BY fh_navn, di_sum) As Gjennomsnitt,
CASE
WHEN (MAX(A.totalt_poeng) / di_sum) < 460 THEN '<hr align="left" width="' + CONVERT(Varchar(10), MAX(totalt_Poeng) / di_sum) + '" color="#FFFF00" noshade size="20">'
END AS Linje_Poeng2,
CASE
WHEN (MAX(A.totalt_poeng) / di_sum) > 460 THEN '<hr align="left" width="' + CONVERT(Varchar(10), MAX(totalt_Poeng) / di_sum) + '" color="#FFFF00" noshade size="20">'
END AS Linje_Poeng,
'<hr align="left" width="' + CONVERT(Varchar(10), SUM(totalt_Poeng) / COUNT(fh_navn) / di_sum) + '" color="#FFFF00" noshade size=""20">' AS Linje_Gjennomsnitt

FROM #View_Deltakere_Poeng A
GROUP BY fh_navn, di_sum

DROP TABLE #View_Deltakere_Poeng

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-29 : 23:49:47

You can't include an if statement in a select.
The column names are fixed and cannot be optional as you seem to be trying to return.
Also you don't need all the select statements.
You could get this effect by using dynamic sql but is probably not necessary.

try something like

SELECT MAX(totalt_poeng) AS Poeng,
fh_navn,
di_sum,
SUM(totalt_Poeng) / COUNT(fh_navn) AS Gjennomsnitt
Linje_Poeng =
'<hr align="left" width="' + CONVERT(Varchar(10), MAX(totalt_Poeng) / di_sum) + '" color="#FFFF00" noshade size="20">'
flag = case when (MAX(totalt_Poeng) / di_sum)< 460 then 1 else 2 end ,
'<hr align="left" width="' + CONVERT(Varchar(10), SUM(totalt_Poeng) / COUNT(fh_navn) / di_sum) + '" color="#FFFF00" noshade size="20">' AS Linje_Gjennomsnitt
FROM dbo.View_Deltakere_Poeng
GROUP BY fh_navn, di_sum

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 12/29/2002 23:53:57

thorv
Starting Member

7 Posts

Posted - 2002-12-30 : 06:30:11

I did like this and now the SQL is like i wanted it :-)

Thanks, and Happy New Year to ValterBorgers and nr from Thor in Norway!!!

SELECT MAX(totalt_poeng) AS Poeng,
fh_navn,
di_sum,
SUM(totalt_Poeng) / COUNT(fh_navn) AS Gjennomsnitt,
Linje_Poeng = case when (MAX(totalt_Poeng) / di_sum)< 460 then '<hr align="left" width="' + CONVERT(Varchar(10), MAX(totalt_Poeng) / di_sum) + '" color="#FFFF00" noshade size="20">'
else '<hr align="left" width="460" color="#FFFF00" noshade size="20">' end ,
'<hr align="left" width="' + CONVERT(Varchar(10), SUM(totalt_Poeng) / COUNT(fh_navn) / di_sum) + '" color="#FFFF00" noshade size="20">' AS Linje_Gjennomsnitt
FROM dbo.View_Deltakere_Poeng
GROUP BY fh_navn, di_sum

Thor

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-31 : 00:21:36

Happy New Years to you too thorv.

Edited by - ValterBorges on 12/31/2002 00:23:44

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources