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!

Register Log in
  • Congratulations Mike Lewis 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

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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
how do I fix the above case?




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
I see this
I need to add a line of code that sets dbo.SubSection.Level = 0 unless dbo.SubSection.SectionID = 1.
Click to expand...
Looks like a simple update to me

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

Eh?



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




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
297
Andrzejek
Andrzejek
grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
510
mikrom
mikrom
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
254
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
Is &quot;IF EXISTS()...&quot; better than &quot;IF (SELECT COUNT...) &gt; 0&quot;
Replies
4
Views
1K
gmmastros
gmmastros
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
154
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top