T-SQL syntax for query in SQL server

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)

T-SQL syntax for query in SQL server

Author

Topic

anglianthon
Starting Member

18 Posts

Posted - 2006-01-18 : 01:16:06

Hi guys,

I'm new to SQL server in general. I have the following SQL statement that I used in generating query in MS Access.

SELECT TOP 1 IS.TIME, Sum([EST]!NLAPA)+Sum([EST]!TOT) AS NLAPAGLOTOTsum, Sum([EST]! SUCC)+Sum([EST]! 2RESUCC) AS SUCCsum, ([SUCCsum]/[NLAPAGLOTOTsum])*100 AS [Success Rate %]
FROM IS INNER JOIN EST ON IS.ID = EST.ID
WHERE (((IS.RPL)=5) AND ((IS.NE)<>'IM0'))
GROUP BY IS.TIME
HAVING (((IS.TIME)>=Date()-7 And (IS.TIME)<Date()))
ORDER BY Sum([EST]!NLAPA)+Sum(EST]!TOT) DESC;

But when I run it on SQL query analyzer, there's syntax error near '!'

Can anyone tell me what's wrong with it? or where can I read up on detailed explanation of all the syntax?

Thank you.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-18 : 01:21:58

Hi,
Replace '!' with '.'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-18 : 01:32:51

Learn SQL
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

anglianthon
Starting Member

18 Posts

Posted - 2006-01-18 : 04:58:53

THX guys!! that works!

I have another question if you don't mind. say I've created a column in the query

SELECT SUM(IS.GRA) AS 'GRA_SUM'

how do I call for that column in the same query. Say, I want to add GRA_SUM to another column from table IS.

In MS Access, all I need to do is as follow:

SELECT SUM(IS.GRA) AS 'GRA_SUM', ([GRA_SUM + IS!OTO]) AS 'GRAOTO'

but query analyzer doesn't allow the above statement. What should I change??

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-18 : 05:09:23

quote:
say I've created a column in the query

SELECT SUM(IS.GRA) AS 'GRA_SUM'

You did not created a column. You just assign a alias to a column.

quote:
Say, I want to add GRA_SUM to another column from table IS.

Not directly. you have to specify the coloumn again

SELECT SUM(IS.GRA) AS 'GRA_SUM', (SUM(IS.GRA) + SUM(IS.OTO)) AS 'GRAOTO'

-----------------
'KH'

anglianthon
Starting Member

18 Posts

Posted - 2006-01-18 : 22:51:52

ok thanks! that works.

but I can't seem to display the decimal points. I tried this:

convert(decimal(2,2),(100*(Sum(IS.SUCC)+ Sum(IS.2RE))/(Sum(IS.OTO)))

but somehow it doesn't show the numbers after the decimal points. say it only displayed 12.00, when the result is supposed to be 12.25

Please help.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-18 : 23:03:04

You should learn about integer math, and casting of data types in calculations. You can read about these subjects in SQL Server Books Online.

Then you should be able to figure out why this statement returns the results it does:


select x= 5/3 ,y=1/2


Results:
x           y           
----------- ----------- 
1           0

(1 row(s) affected)

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-18 : 23:06:56

this is because the division result is being converted to into int before your convert. As all your columns (SUCC, 2RE, OTO) must be int column
Try these code and you will see it.

select (2 + 3) / 3, (2 + 3) / 3.0, convert(decimal(5,2), (2 + 3) / 3), convert(decimal(5,2), (2 + 3) / 3.0)

This should do the trick. Change 100 to 100.0. This will force the result of your sum & division to convert to decimal

convert(decimal(2,2),(100.0*(Sum(IS.SUCC)+ Sum(IS.2RE))/(Sum(IS.OTO)))

-----------------
'KH'

anglianthon
Starting Member

18 Posts

Posted - 2006-01-20 : 01:21:00

yesyes..it worked! I thought SQL will convert it automatically and I thought the convert() function is to help it convert. Didn't even realise the convert() function is only to specify the number of decimal points.

Thanks again!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-20 : 01:26:57

For your case, the result of the computation

(100*(Sum(IS.SUCC)+ Sum(IS.2RE))/(Sum(IS.OTO))

is actually as int. So when converting and int to decimal(2,2) will definately result in xxx.00

-----------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-20 : 01:50:51

>>convert(decimal(2,2),(100.0*(Sum(IS.SUCC)+ Sum(IS.2RE))/(Sum(IS.OTO)))

If variable is used, you can multiply it by 1.0

convert(decimal(2,2),(100*1.0*(Sum(IS.SUCC)+ Sum(IS.2RE))/(Sum(IS.OTO)))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources