How to use a single statement ?

Question

Post reply

How to use a single statement ?

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

September 6, 2006 at 11:23 am

#658817

And folks, that's why you should always post the code you put on the forums.

But I'm sure you can figure out why this doesn't work by yourself now.

ganeshc27 SSC Eights! Points: 922 More actions · Answer 2

ganeshc27

SSC Eights!

Points: 922

September 6, 2006 at 7:12 pm

#658924

Will this help..?

SELECT COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END) AS SLA_OK,

CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS Total

FROM TABLE

Regards,

Ganesh

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 3

Pedro Reis-267786

Mr or Mrs. 500

Points: 590

September 7, 2006 at 3:04 am

#658999

Yes. Thanks!

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 4

And if i want to compare the value for total , for example total > 80 ?

If i do so i get an error... it must be because, its a temporary variable , is possible to do it ?

SELECT cliente, COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END) AS SLA_OK,

CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS Total

FROM

processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

'PCOSTA','RUP', 'SASUM')

group by cliente

order by cliente

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

September 7, 2006 at 9:31 am

#659132

You have 2 columns name Total... that might be the problem.

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 6

Changed the last column to "PERC", but is still get the message:

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name 'PERC'.

Server: Msg 207, Level 16, State 1, Line 1

Invalid column name 'PERC'.

Server: Msg 207, Level 16, State 1, Line 1

Invalid column name 'PERC'.

Server: Msg 207, Level 16, State 1, Line 1

Invalid column name 'PERC'.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us

SSC Guru

Points: 294069

September 7, 2006 at 9:40 am

#659134

Please post the entire query... most message by themselves are not very easy to debug... unless you only want vague pointers .

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 8

SELECT cliente, COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END) AS SLA_OK,

CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS PERC

FROM

processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

'PCOSTA','RUP', 'SASUM')

group by cliente

order by cliente

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Ninja's_RGR'us

SSC Guru

Points: 294069

September 7, 2006 at 10:00 am

#659141

There's no syntaxe error in that query. Post the query that is giving you the error.

SQLBill SSC Guru Points: 51440 More actions · Answer 10

SQLBill

SSC Guru

Points: 51440

September 7, 2006 at 10:03 am

#659144

If you are getting a syntax error on that, run this and give us the results:

SELECT SERVERPROPERTY('productlevel'),

SERVERPROPERTY('productversion'),

SERVERPROPERTY('edition')

-SQLBill

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 11

SELECT cliente, COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END) AS SLA_OK,

CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS PERC

FROM

processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

'PCOSTA','RUP', 'SASUM') and PERC < 80

group by cliente

order by cliente

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 12

Pedro Reis-267786

Mr or Mrs. 500

Points: 590

September 7, 2006 at 10:24 am

#659154

Result of

SELECT SERVERPROPERTY('productlevel'),

SERVERPROPERTY('productversion'),

SERVERPROPERTY('edition')

SP4 8.00.2039 Enterprise Edition

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

SELECT cliente, COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END) AS SLA_OK,

CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS PERC

FROM

processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

'PCOSTA','RUP', 'SASUM')

group by cliente

having CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) < 80

order by cliente

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 14

Great. The statement works just fine.

Now I want to use the result of this query as an input to select certain rows ..a subquery….

I have tried to use the following clauses : any , exists , in … but nothing works ….i thinks the more appropriate to this case is exists, maybe I have forgot something… could you take a look:

select cliente, processo from processos where dataabertura >='2006-01-01' and

--cliente=any

cliente exists

--cliente in

(

SELECT cliente, COUNT(cdu_slaok) AS Total , SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END) AS SLA_OK,

CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) AS PERC

FROM

processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

'PCOSTA','RUP', 'SASUM')

group by cliente

having CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) < 80

--order by cliente)

)

Error with exists:

Server: Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'exists'.

Error with any:

Server: Msg 116, Level 16, State 1, Line 1

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Error with in:

Server: Msg 116, Level 16, State 1, Line 1

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Pedro Reis-267786 Mr or Mrs. 500 Points: 590 More actions · Answer 15

Ok i found the problem, now it works :

select cliente, processo, cdu_slaok from processos where dataabertura >='2006-01-01' and

cdu_fechado='1' and

--cliente=any

--cliente exists

cliente in

(

SELECT cliente

FROM

processos where cdu_fechado='1'and dataabertura >='2006-01-01'and

cliente not in ('PRIMAVERA', 'TESTES', '_ESCOLAS', 'AGERE', 'CMFUNDAO', 'FIRST', 'IPCA',

'PCOSTA','RUP', 'SASUM')

group by cliente

having CAST(SUM(CASE WHEN cdu_slaok = '1' THEN 1 ELSE 0 END)AS DECIMAL)*100 / COUNT(cdu_slaok) < 80

)

order by cliente