Query always returns 0 - 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)

Query always returns 0

Author

Topic

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-05-27 : 15:25:10

I have the following query, and no matter what I do, the Pen field always returns 0


SELECT qryRG_02aa_StudentBookedCount.sce_scjc, qryRG_02aa_StudentBookedCount.SumOfTCB, qryRG_02ba_StudentAchCount.SumOfsmr_mcrd, ((SumOfsmr_mcrd/SumOfTCB) *100) AS Pen
FROM qryRG_02aa_StudentBookedCount LEFT JOIN qryRG_02ba_StudentAchCount ON qryRG_02aa_StudentBookedCount.sce_scjc = qryRG_02ba_StudentAchCount.sce_scjc

Some sample data

qryRG_02aa_StudentBookedCount


sce_scjc        TCB
0122803/1   	1600
0136696/1   	2000
0145456/1   	200
0145572/1   	200

qryRG_02ba_StudentAchCount


sce_scjc        smr_mcrd
0122803/1   	800
0136696/1   	600
0300005/1   	700
0300371/1   	200

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-27 : 15:28:49

Your query is referring to columns that do not exist in your sample data. Such as SumOfTCB and SumOfsmr_mcrd.

Could you post the CREATE TABLE statements for your two tables and the sample data in the form of INSERT INTO statements? That would help us help you answer this question faster.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-27 : 15:34:04

You might just need to convert one of the columns in Pen to DECIMAL if both the columns are INT. That way you get the decimal points out of the division.

((CONVERT(DECIMAL(18,10), SumOfsmr_mcrd)/SumOfTCB) *100) AS Pen

Tara

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-05-27 : 15:50:47

Worked a treat, cheers. How do I get it down to a format of nnn.nn? If I try CONVERT(DECIMAL(5,2), I get the following error;

"Arithmetic overflow error converting numeric to data type numeric"!!

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-27 : 15:51:57

CONVERT the whole PEN to DECIMAL and change the scale and precision.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-27 : 15:55:14

CONVERT(DECIMAL(5, 2), ((CONVERT(DECIMAL(18,10), SumOfsmr_mcrd)/SumOfTCB) * 100)) AS Pen

Tara

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-05-27 : 15:59:45

Excellent, thanks very much for taking the time to help me. I've got the great task of converting somebodies Access queries littered with Access functions (like IIf, etc) to SQL views for use with Reporting Services

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources