Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

If in a query

Status
Not open for further replies.

MichaelF81

Programmer
Sep 20, 2005
178
US
I have a query

p
Code:
SELECT TOP 100 PERCENT dbo.Section.sectionID, dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder, dbo.Qustn.QustnText, dbo.Qustn.QustnType,                        dbo.Section.sectionDesc, dbo.SubSection.SubSectionText, dbo.TargetSection.targetId
FROM dbo.SubSection INNER JOIN                       dbo.Qustn ON dbo.Qustn.SubSectionId = dbo.SubSection.SubSectionID INNER JOIN                       dbo.Section ON dbo.SubSection.SectionID = dbo.Section.sectionID INNER JOIN                       dbo.TargetSection ON dbo.Section.sectionID = dbo.TargetSection.sectionID
WHERE dbo.TargetSection.targetId = '121'
ORDER BY dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder

and What I need is to add 1 thing, but how is a problem.

There is a field called dbo.SubSection.Level that is always 0, EXCEPT when dbo.SubSection.SectionID = 1. I need to add a line of code that sets dbo.SubSection.Level = 0 unless dbo.SubSection.SectionID = 1.

Ideas?




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
I know about cases, I tried

Code:
SELECT TOP 100 PERCENT dbo.Section.sectionID, dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder, dbo.Qustn.QustnText, dbo.Qustn.QustnType,                        dbo.Section.sectionDesc, dbo.SubSection.SubSectionText, dbo.TargetSection.targetId
CASE 'cc'
when dbo.subsection.sectionid = '1' then
FROM dbo.SubSection INNER JOIN                       dbo.Qustn ON dbo.Qustn.SubSectionId = dbo.SubSection.SubSectionID INNER JOIN                       dbo.Section ON dbo.SubSection.SectionID = dbo.Section.sectionID INNER JOIN                       dbo.TargetSection ON dbo.Section.sectionID = dbo.TargetSection.sectionID
WHERE dbo.TargetSection.targetId = '121' and dbo.subsection.[level] = '1'
ORDER BY dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder
else
FROM dbo.SubSection INNER JOIN                       dbo.Qustn ON dbo.Qustn.SubSectionId = dbo.SubSection.SubSectionID INNER JOIN                       dbo.Section ON dbo.SubSection.SectionID = dbo.Section.sectionID INNER JOIN                       dbo.TargetSection ON dbo.Section.sectionID = dbo.TargetSection.sectionID
WHERE dbo.TargetSection.targetId = '121'
ORDER BY dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder
end 'cc'

but that does not work.




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
how do I fix the above case?




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Here is the modified query with CASE statement. Is this what you are looking for?

Code:
SELECT TOP 100 PERCENT dbo.Section.sectionID, dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder, dbo.Qustn.QustnText, dbo.Qustn.QustnType,                        
dbo.Section.sectionDesc, dbo.SubSection.SubSectionText, dbo.TargetSection.targetId,
CASE 
when  dbo.subsection.sectionid =  '1' then
dbo.subsection.[level]
else
'0'
end 'cc'

FROM dbo.SubSection 
INNER JOIN                       dbo.Qustn ON dbo.Qustn.SubSectionId = dbo.SubSection.SubSectionID
 INNER JOIN                       dbo.Section ON dbo.SubSection.SectionID = dbo.Section.sectionID 
INNER JOIN                       dbo.TargetSection ON dbo.Section.sectionID = dbo.TargetSection.sectionID
WHERE dbo.TargetSection.targetId = '121'
ORDER BY dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder

Sunil
 
Code:
SELECT TOP 100 PERCENT dbo.Section.sectionID, dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder, dbo.Qustn.QustnText, dbo.Qustn.QustnType,                        
dbo.Section.sectionDesc, dbo.SubSection.SubSectionText, dbo.TargetSection.targetId, dbo.SubSection.Level,
CASE
when  dbo.subsection.sectionid = '1' or
dbo.subsection.sectionid =  '20' OR
dbo.subsection.sectionid =  '126' OR
dbo.subsection.sectionid =  '138' then
dbo.subsection.[level]
else
'0'
end 'cc'

FROM dbo.SubSection
INNER JOIN                       dbo.Qustn ON dbo.Qustn.SubSectionId = dbo.SubSection.SubSectionID
 INNER JOIN                       dbo.Section ON dbo.SubSection.SectionID = dbo.Section.sectionID
INNER JOIN                       dbo.TargetSection ON dbo.Section.sectionID = dbo.TargetSection.sectionID
WHERE dbo.TargetSection.targetId = '121'
ORDER BY dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder

Ok, this works but doe snot do what I need it to.

WHat I need is for
[/code]when dbo.subsection.sectionid = '1' or
dbo.subsection.sectionid = '20' OR
dbo.subsection.sectionid = '126' OR
dbo.subsection.sectionid = '138'[/code]
dbo.subsection.[level] = 1
otherwise,
dbo.subsection.[level] = 0

This explain it better?





"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
I see this
I need to add a line of code that sets dbo.SubSection.Level = 0 unless dbo.SubSection.SectionID = 1.
Looks like a simple update to me

Code:
update dbo.SubSection set dbo.SubSection.Level = 0 where dbo.SubSection.SectionID != 1

Eh?



<.
 
except it is not an update statement, simply a select




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
I am not sure what you are trying to do. Are you trying to update the table or just writing a SELECT SQL to retrieve data? It will be heplful if you could give us some sample data and expected result of the query.

Sunil
 
Code:
SELECT TOP 100 PERCENT dbo.Section.sectionID, dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder, dbo.Qustn.QustnText, dbo.Qustn.QustnType,                         dbo.Section.sectionDesc, dbo.SubSection.SubSectionText, dbo.TargetSection.targetId, case when  dbo.subsection.sectionid in( '1','20','126','138') then 1 else 0 end as [level]
FROM dbo.SubSection INNER JOIN                       dbo.Qustn ON dbo.Qustn.SubSectionId = dbo.SubSection.SubSectionID  INNER JOIN                       dbo.Section ON dbo.SubSection.SectionID = dbo.Section.sectionID INNER JOIN                       dbo.TargetSection ON dbo.Section.sectionID = dbo.TargetSection.sectionID
WHERE dbo.TargetSection.targetId = '121' AND dbo.subsection.[level] = [level]
ORDER BY dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder

I added
Code:
WHERE dbo.TargetSection.targetId = '121' AND dbo.subsection.[level] = [level]
is this correct?

Also,

Issue is that this is being used as a recordset for an .asp page. When I update the code on the asp page, it still runs and shows me the QustnText for where level is 1, 2, 3, and 4.

Ideas?






"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
What is happening is that in the newly created [level] field, it is just changing the actual level from 2, 3, 4 to 1. SO it is pulling them all, but it is actually pulling the wrong data.

I tried

Code:
SELECT     TOP 100 PERCENT dbo.SubSection.[Level], dbo.Section.sectionID, dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder, dbo.Qustn.QustnText, 
                      dbo.Qustn.QustnType, dbo.Section.sectionDesc, dbo.SubSection.SubSectionText, dbo.TargetSection.targetId, 
                      CASE WHEN dbo.subsection.sectionid IN ('1', '20', '126', '138') THEN dbo.usersowpp.grade ELSE 0 END AS [level2]
FROM         dbo.SubSection INNER JOIN
                      dbo.Qustn ON dbo.Qustn.SubSectionId = dbo.SubSection.SubSectionID INNER JOIN
                      dbo.Section ON dbo.SubSection.SectionID = dbo.Section.sectionID INNER JOIN
                      dbo.TargetSection ON dbo.Section.sectionID = dbo.TargetSection.sectionID INNER JOIN
                      dbo.UsersOWPP ON dbo.TargetSection.targetId = dbo.UsersOWPP.empNum
WHERE     (dbo.TargetSection.targetId = '2088')
ORDER BY dbo.SubSection.SubSectionID, dbo.Qustn.QustnOrder, dbo.SubSection.SubSectionText

adding the 'usersOWPP' table, and 'dbo.usersOWPP.Grade' = 'dbo.SubSection.Level'

Updating the case with that did not help :(

Ideas?




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top