simple select query - need help

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

Transact-SQL (2005)

simple select query - need help

Author

Topic

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 10:37:32

i want if sectionid =0 then code ''
else if sectionid =1 then code='schools/child care centers'
and also these codes r related to code_type..emans if code_type = 'categories' then code='' or code='schools/child care center

SELECT categories =
if (CODE_TYPE = 'CATEGORIES')
begin
if (section_id = 0)
code = ''
else
code = 'Schools / Child Care Centers'
end
end )

FROM myPagetable

but it gives me error..incorrect syntax if..near categories..

don't know can anybody help me...like this if statements i have to use same way to get all colums..categories is first column...

can anybody help me???

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 10:40:18

Use CASE instead of IF.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 10:41:43

[code]SELECT CASE
WHEN CODE_TYPE = 'Categories' AND Section_ID <> 0 THEN 'Schools / Child Care Centers'
ELSE ''
END
FROM myPagetable[/code]

E 12°55'05.25"
N 56°04'39.16"

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 10:56:01

thanks for ur answer.

i have columns in my pagetable -
defid,sectionid,code_type,code,cell

in code_type column values r :
CATEGORIES,
TOPICS,
FORMAT,
AUDIENCE,
SOURCE,
SOURCENOT,
SECTIONTYPE

and in code column related to code_type column values r :

CODE_TYPE = 'CATEGORIES' AND (CODE = 'Schools / Child Care Centers')

CODE_TYPE = 'TOPICS' AND (CODE IN ('LUNG-ASCPG','LUNG-ASTHM'))

CODE_TYPE = 'FORMAT' AND (CODE IN ('PUBS-RECIP','pubs+other'))

CODE_TYPE = 'AUDIENCE' AND (CODE IN ('public', 'prof+publi'))

CODE_TYPE = 'SOURCE' AND (CODE IN ('PAR/CH/TEA','LATINO/PTC'))

CODE_TYPE = 'SOURCENOT' AND (CODE IN 'PAR/CH/TEA','LATINO/PTC'))

CODE_TYPE = 'SECTIONTYPE' AND (CODE = 'PUBS')

i want results using sproc..like these:

categories topics format audience source sourcenot sectiontype
lung pubs-re publ-pro par/ch pubs
schols/centr lung pubs-re publ-pro par/ch pubs

means first row results : is if sectionid=0
and second row results : is if sectionid=1

can anybody help me to create store proce by getting this results.

thanks.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 11:08:01

I HAVE TO USE COLUMNS INSTEAD OF VALUES...AS EVERYTIME THE VALUES WILL BE CHANGED SO I HAVE DONE LIKE THESE FOR FIRST COLUMN.....

CREATE PROCEDURE MYPAGE
AS
BEGIN
SELECT

CATEGORIES = CASE
WHEN CODE_TYPE = 'CATEGORIES' AND Section_ID =1 THEN CODE
ELSE ''
END

FROM MYPAGETAB
WHERE CODE_TYPE='CATEGORIES'

INSTEAD OF GETTING ME JUST ONE ROW I M GETTING 2ROWS...CODE='SCHOOLS/CARE CENTER' AND CODE='NONE'

CAN ANYONE TELL ME THE REASON?
THANKS

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 11:45:27

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 12:22:05

CREATE PROCEDURE MYPAGESECDEF
@SECTION_ID INT
AS

BEGIN

SET NOCOUNT ON

INSERT INTO MYPAGESECDEFTAB
(

CATEGORIES,
TOPICS,
FORMAT,
AUDIENCE,
SOURCE,
SOURCENOT,
SECTIONTYPE

)

SELECT

CASE
WHEN (CODE_TYPE = 'Categories' and Section_ID = 0) THEN ' '
WHEN (CODE_TYPE = 'Categories' and Section_ID = 1) THEN code
END,

CASE
WHEN (CODE_TYPE = 'TOPICS' and Section_ID = 0 ) THEN code
WHEN (CODE_TYPE = 'TOPICS' and Section_ID = 1 ) THEN code

END,

CASE
WHEN (CODE_TYPE = 'FORMAT' and Section_ID = 0 ) THEN code
WHEN (CODE_TYPE = 'FORMAT' and Section_ID = 1 ) THEN code

END,

CASE
WHEN (CODE_TYPE = 'AUDIENCE' and Section_ID = 0 ) THEN code
WHEN (CODE_TYPE = 'AUDIENCE' and Section_ID = 1 ) THEN code

END,

CASE
WHEN (CODE_TYPE = 'SOURCE' and Section_ID = 0 ) THEN ' '
WHEN (CODE_TYPE = 'SOURCE' and Section_ID = 1 ) THEN code

END,

CASE
WHEN (CODE_TYPE = 'SOURCENOT' and Section_ID = 0 ) THEN code
WHEN (CODE_TYPE = 'SOURCENOT' and Section_ID = 1 ) THEN ' '

END,

CASE
WHEN (CODE_TYPE = 'SECTIONTYPE' and Section_ID = 0 ) THEN ' '
WHEN (CODE_TYPE = 'SECTIONTYPE' and Section_ID = 1 ) THEN code
END

FROM myPageSectionDefinition PSD

WHERE (CODE_TYPE = 'Categories' AND CODE = 'Schools / Child Care Centers')
OR (CODE_TYPE = 'TOPICS' AND CODE IN ('LUNG-ASCPG','LUNG-ASTHM'))
OR (CODE_TYPE = 'FORMAT' AND CODE IN ('PUBS-RECIP','pubs+other','pubs-dcifs','SLIDESHOWS','MISC','POSTER', 'misc','video/cdro','PUBS-NHLBI','PUBS-NONNH','PUBS-OTHER'))
OR (CODE_TYPE = 'AUDIENCE' AND CODE IN ('public', 'prof+publi'))
OR (CODE_TYPE = 'SOURCE' AND CODE IN ('PAR/CH/TEA','LATINO/PTC','LATINOS-B','LATINOS-E','LATINOS-S','LATINS/PTC','LATINB/PTC'))
OR (CODE_TYPE = 'SOURCE' AND CODE IN ('PAR/CH/TEA','LATINO/PTC','LATINOS-B','LATINOS-E','LATINOS-S','LATINS/PTC','LATINB/PTC'))
OR (CODE_TYPE = 'SECTIONTYPE' AND CODE = 'PUBS')

END

i m getting first code values into 2nd code..and 2nd into3rd code..its mix match...can u tell me the correct solution using if or case statement..

thanks

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 13:42:35

first case is working properly..from second case statement code column is getting values null..can anybody help me plz?????????

thanks

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 14:15:36

Ok, it seems to appear as though you want to PIVOT the data. Under that assumption, I've crafted an update to your select. If you have multiple CODE_TYPEs per Section_ID (2+ CODE_TYPE = 'FORMAT' for instance) this select will pick the first one in ALPHA order, so be careful of that.

SELECT
Section_ID,

CATEGORIES = MAX(CASE
WHEN S.CODE_TYPE = 'Categories' AND Section_ID = 0 THEN ' '
WHEN S.CODE_TYPE = 'Categories' AND Section_ID = 1 THEN S.CODE
END),

TOPICS = MAX(CASE
WHEN S.CODE_TYPE = 'TOPICS' AND Section_ID IN (0, 1) THEN S.CODE
END),

FORMAT = MAX(CASE
WHEN S.CODE_TYPE = 'FORMAT' AND Section_ID IN (0, 1) THEN S.CODE
END),

AUDIENCE = MAX(CASE
WHEN S.CODE_TYPE = 'AUDIENCE' AND Section_ID IN (0, 1) THEN S.CODE
END),

SOURCE = MAX(CASE
WHEN S.CODE_TYPE = 'SOURCE' AND Section_ID = 0 THEN ' '
WHEN S.CODE_TYPE = 'SOURCE' AND Section_ID = 1 THEN S.CODE
END),

SOURCENOT = MAX(CASE
WHEN S.CODE_TYPE = 'SOURCENOT' AND Section_ID = 1 THEN ' '
WHEN S.CODE_TYPE = 'SOURCENOT' AND Section_ID = 0 THEN S.CODE
END),

SECTIONTYPE = MAX(CASE
WHEN S.CODE_TYPE = 'SECTIONTYPE' AND Section_ID = 0 THEN ' '
WHEN S.CODE_TYPE = 'SECTIONTYPE' AND Section_ID = 1 THEN S.CODE
END)

FROM @myPageSectionDefinition S
WHERE (CODE_TYPE = 'Categories' AND CODE = 'Schools / Child Care Centers')
OR (CODE_TYPE = 'TOPICS' AND CODE IN ('LUNG-ASCPG','LUNG-ASTHM'))
OR (CODE_TYPE = 'FORMAT' AND CODE IN ('PUBS-RECIP','pubs+other','pubs-dcifs','SLIDESHOWS','MISC','POSTER', 'misc','video/cdro','PUBS-NHLBI','PUBS-NONNH','PUBS-OTHER'))
OR (CODE_TYPE = 'AUDIENCE' AND CODE IN ('public', 'prof+publi'))
OR (CODE_TYPE = 'SOURCE' AND CODE IN ('PAR/CH/TEA','LATINO/PTC','LATINOS-B','LATINOS-E','LATINOS-S','LATINS/PTC','LATINB/PTC'))
OR (CODE_TYPE = 'SOURCE' AND CODE IN ('PAR/CH/TEA','LATINO/PTC','LATINOS-B','LATINOS-E','LATINOS-S','LATINS/PTC','LATINB/PTC'))
OR (CODE_TYPE = 'SECTIONTYPE' AND CODE = 'PUBS')
GROUP BY Section_ID

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 14:31:38

thanks for ur reply....but i m getting one row as a result with errormessage

Null value is eliminated by an aggregate or other SET operation.

one row result is correct but not getting other rows...

thanks..

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 14:34:28

oops, forgot to take of the @ on @myPageSectionDefinition. Maybe you got that. I can't do much more w/o knowing the contents of myPageSectionDefinition.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 14:46:41

sectionid code code_type
----------------------------------------------------------
1 Schools / Child Care Centers categories
1 lung-asthm topic
1 pubs-recip format
1 public audience
1 per/ch/tea source
1 pubs sectiontype
1 lung-acapg topic
1 prof+publi audience

so many rows like these in mypagesectiondefinition table

anyways, thanx for ur reply.

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 14:51:47

Is section_id ever null? Also, I left a little work on the select to you. You are passing in a section_id into the stored proc. You need to add that to your WHERE clause. Something like this should work:

WHERE Section_ID = @Section_ID And
((CODE_TYPE = 'Categories' AND CODE = 'Schools / Child Care Centers')
OR (CODE_TYPE = 'TOPICS' AND CODE IN ('LUNG-ASCPG','LUNG-ASTHM'))
OR (CODE_TYPE = 'FORMAT' AND CODE IN ('PUBS-RECIP','pubs+other','pubs-dcifs','SLIDESHOWS','MISC','POSTER', 'misc','video/cdro','PUBS-NHLBI','PUBS-NONNH','PUBS-OTHER'))
OR (CODE_TYPE = 'AUDIENCE' AND CODE IN ('public', 'prof+publi'))
OR (CODE_TYPE = 'SOURCE' AND CODE IN ('PAR/CH/TEA','LATINO/PTC','LATINOS-B','LATINOS-E','LATINOS-S','LATINS/PTC','LATINB/PTC'))
OR (CODE_TYPE = 'SOURCE' AND CODE IN ('PAR/CH/TEA','LATINO/PTC','LATINOS-B','LATINOS-E','LATINOS-S','LATINS/PTC','LATINB/PTC'))
OR (CODE_TYPE = 'SECTIONTYPE' AND CODE = 'PUBS'))

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 15:05:13

section_id is not null...it is 0,1,2....values

my sproc is :
CREATE PROCEDURE MYPAGESECDEF
@SECTION_ID INT
AS

BEGIN

SET NOCOUNT ON

SELECT
Section_ID,

CATEGORIES = MAX(CASE
WHEN S.CODE_TYPE = 'Categories' AND Section_ID = 0 THEN ' '
WHEN S.CODE_TYPE = 'Categories' AND Section_ID = 1 THEN S.CODE
END),

TOPICS = MAX(CASE
WHEN S.CODE_TYPE = 'TOPICS' AND Section_ID IN (0, 1) THEN S.CODE
END),

FORMAT = MAX(CASE
WHEN S.CODE_TYPE = 'FORMAT' AND Section_ID IN (0, 1) THEN S.CODE
END),

AUDIENCE = MAX(CASE
WHEN S.CODE_TYPE = 'AUDIENCE' AND Section_ID IN (0, 1) THEN S.CODE
END),

SOURCE = MAX(CASE
WHEN S.CODE_TYPE = 'SOURCE' AND Section_ID = 0 THEN ' '
WHEN S.CODE_TYPE = 'SOURCE' AND Section_ID = 1 THEN S.CODE
END),

SOURCENOT = MAX(CASE
WHEN S.CODE_TYPE = 'SOURCENOT' AND Section_ID = 1 THEN ' '
WHEN S.CODE_TYPE = 'SOURCENOT' AND Section_ID = 0 THEN S.CODE
END),

SECTIONTYPE = MAX(CASE
WHEN S.CODE_TYPE = 'SECTIONTYPE' AND Section_ID = 0 THEN ' '
WHEN S.CODE_TYPE = 'SECTIONTYPE' AND Section_ID = 1 THEN S.CODE
END)

FROM myPageSectionDefinition S

WHERE Section_ID = @Section_ID and
((CODE_TYPE = 'Categories' AND S.CODE = 'Schools / Child Care Centers')
OR (CODE_TYPE = 'TOPIC' AND S.CODE IN ('LUNG-ASCPG','LUNG-ASTHM'))
OR (CODE_TYPE = 'FORMAT' AND S.CODE IN ('PUBS-RECIP','pubs+other','pubs-dcifs','SLIDESHOWS','MISC','POSTER', 'misc','video/cdro','PUBS-NHLBI','PUBS-NONNH','PUBS-OTHER'))
OR (CODE_TYPE = 'AUDIENCE' AND S.CODE IN ('public', 'prof+publi'))
OR (CODE_TYPE = 'SOURCE' AND S.CODE IN ('PAR/CH/TEA','LATINO/PTC','LATINOS-B','LATINOS-E','LATINOS-S','LATINS/PTC','LATINB/PTC'))
OR (CODE_TYPE = 'SOURCENOT' AND S.CODE IN ('PAR/CH/TEA','LATINO/PTC','LATINOS-B','LATINOS-E','LATINOS-S','LATINS/PTC','LATINB/PTC'))
OR (CODE_TYPE = 'SECTIONTYPE' AND S.CODE = 'PUBS'))

GROUP BY SECTION_ID

END

if i m adding Section_ID = @Section_ID in where clause then it gives me 0 rows...
and if i am not adding then when i am pasing @section_id =0,1,2 one bye one then it gives only 1 row and same everytime..

thanks

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 15:11:23

Exec MYPAGESECDEF 1
Exec MYPAGESECDEF 2
Exec MYPAGESECDEF 3

That should give you 1 row per call and will be different each time (at least section_id will be different). It will return a PIVOTED view of your myPageSectionDefinition table where the PIVOT column is Section_ID. If you want ALL the Section_IDs pivoted, then take off the section_id from the WHERE and from the proc params.

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 15:36:04

i takeoff section_id from where and sproc parameters but still it gives me only one row

and when i have take off max then it gives me all rows but null values..

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 15:37:58

You should get 1 row PER SECTION_ID with that query. Are you sure that you have more than 1 distinct value of section_id in your table?

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 15:40:47

What do you get with this query:

Select Section_id, Max(code_type)
From myPageSectionDefinition
Group By Section_id

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 15:49:06

it gives me

1 topic

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-07 : 15:50:04

min

1 audience

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 15:57:15

Then you only have 1 value for section_id in your table. It is 1. Because you only have 1 distinct value of section_id in your table, you will only ever get 1 row from the query. What are you expecting from your query? Mock up some data for me.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources