Sql query array? - 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 2005 Forums

Analysis Server and Reporting Services (2005)

Sql query array?

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-10-21 : 10:07:10

Hi,

Im quite new to the complex sql query side. I know in java or c# you can put all the strings in a field in a hashmap/array and check for values. Is there a way I can do that in sql?

For instance user has in same field but on different dates; y,y,n,y how can I check that he has atleast one 'n' or none at all.

Thanks

Fahad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 10:09:29

you need to use GROUP BY for that and them use aggregated function to check it. for example, in above case it will be

GROUP BY user
HAVING SUM(CASE WHEN field='n' THEN 1 ELSE 0 END) >0

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-10-21 : 10:34:33

Hi,

I get error 'Error near identifier SUM, Expecting Cast.

Any thoughts?

quote:
Originally posted by visakh16

you need to use GROUP BY for that and them use aggregated function to check it. for example, in above case it will be
GROUP BY user
HAVING SUM(CASE WHEN field='n' THEN 1 ELSE 0 END) >0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 11:38:00

quote:
Originally posted by cipriani1984

Hi,

I get error 'Error near identifier SUM, Expecting Cast.

Any thoughts?

quote:
Originally posted by visakh16

you need to use GROUP BY for that and them use aggregated function to check it. for example, in above case it will be
GROUP BY user
HAVING SUM(CASE WHEN field='n' THEN 1 ELSE 0 END) >0

not much thoughts until i see code you used. can you post it?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-10-21 : 11:40:51

SELECT stmbiogr.student_id, dbo.sttdstud.acad_period, dbo.sttdstud.week_no,

SUM(CASE WHEN dbo.sttdstud.absence_code = 'X' THEN 'N'
WHEN dbo.sttdstud.absence_code = 'O' THEN 'N'
WHEN dbo.sttdstud.absence_code = 'W' THEN 'N'
WHEN dbo.sttdstud.absence_code = 'T' THEN 'N' ELSE 'Y' END AS Sum)

FROM "ignore too long"
WHERE (stmaos.stage_ind = 'E') AND (stmaos.stage_code = 'ENR')
GROUP BY dbo.sttdstud.absence_code, dbo.sttdstud.acad_period, dbo.sttdstud.register_id, dbo.sttdstud.register_group, stmbiogr.student_id, dbo.sttrgprf.day_of_week, dbo.sttdstud.week_no
HAVING (dbo.sttdstud.acad_period = '08/09')

quote:
Originally posted by visakh16

quote:
Originally posted by cipriani1984

Hi,

I get error 'Error near identifier SUM, Expecting Cast.

Any thoughts?

quote:
Originally posted by visakh16

you need to use GROUP BY for that and them use aggregated function to check it. for example, in above case it will be
GROUP BY user
HAVING SUM(CASE WHEN field='n' THEN 1 ELSE 0 END) >0

not much thoughts until i see code you used. can you post it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 11:56:08

you cant SUM on a varchar value. didnt understand what you're trying to do with query above. if you can explain with some sample data that will help.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-10-22 : 04:42:59

Read the very first post,

User has stats but on different time stamps. There could be 5 different rows in the same week with; y y y n y.

Just trying to figure out how to group and check value in the grouping if there is atleast one n or none at all to output an overall value.

quote:
Originally posted by visakh16

you cant SUM on a varchar value. didnt understand what you're trying to do with query above. if you can explain with some sample data that will help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 06:40:31

then should be it be like below?

SELECT absence_code, acad_period, register_id, register_group, student_id, day_of_week, week_no
FROM
(
SELECT stmbiogr.student_id, dbo.sttdstud.acad_period, dbo.sttdstud.week_no,dbo.sttdstud.absence_code, dbo.sttdstud.acad_period, dbo.sttdstud.register_id, dbo.sttdstud.register_group, stmbiogr.student_id, dbo.sttrgprf.day_of_week, dbo.sttdstud.week_no, 

CASE WHEN dbo.sttdstud.absence_code = 'X' THEN 'N' 
WHEN dbo.sttdstud.absence_code = 'O' THEN 'N' 
WHEN dbo.sttdstud.absence_code = 'W' THEN 'N' 
WHEN dbo.sttdstud.absence_code = 'T' THEN 'N' ELSE 'Y' END AS Status

FROM "ignore too long"
WHERE (stmaos.stage_ind = 'E') AND (stmaos.stage_code = 'ENR')
AND (dbo.sttdstud.acad_period = '08/09')
)t
GROUP BY absence_code, acad_period, register_id, register_group, student_id, day_of_week, week_no
HAVING SUm(CASE WHEN Status='N' THEN 1 ELSE 0 END)>0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources