July 5, 2012 at 1:06 pm
I have the following code within a select:
p.Degree1id,
p.Degree2id,
p.Degree3id,
case
when degree3id > isnull(degree2id, '' '') and isnull(degree3id, '' '') > isnull(degree1id, '' '') then (select d.Name from AcademicExperienceTypes d where p.Degree3id=d.Id)
when degree2id > isnull(degree3id, '' '') and isnull(degree2id, '' '') > isnull(degree1id, '' '') then (select d.Name from AcademicExperienceTypes d where p.Degree2id=d.Id)
when degree1id > isnull(degree3id, '' '') and isnull(degree1id, '' '') > isnull(degree2id, '' '') then (select d.Name from AcademicExperienceTypes d where p.Degree1id=d.Id)
end
as degree,
A user enters a Degree value via @Degree.
if (@Degree <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Degree1id =' + cast(@Degree as varchar(10))
End
else if (@Degree <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Degree2id =' + cast(@Degree as varchar(10))
End
else if (@Degree <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Degree3id =' + cast(@Degree as varchar(10))
End
The code always selects p.Degree1id as equal to the entered value(@Degree).
How do i get the highest value if it is in ANY of the three IF tests?
Thanks
July 5, 2012 at 1:11 pm
Follow the logic of your If Else statements.
First, you test if @Degree is not equal to 0, then you have an Else If that tests the same thing, and another Else If that again tests the same thing.
So, if @Degree does not equal 0, it will test positive on the first check, and the Else statements will never come up. If @Degree does equal 0, then it will check each Else If, find none of them are true, and do nothing at all.
I can't tell from this what you want to do, but that's what you have told it to do, and only the first If will ever get run, or none of them at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 5, 2012 at 1:34 pm
I want to first test if the @Degree <> 0
then get the highest degree a candidate has obtained.
So i want to use something similar to the Select CASE logic, but within the IF test, and i am just not sure how to do it.
case
when degree3id > isnull(degree2id, '' '') and isnull(degree3id, '' '') > isnull(degree1id, '' '') then (select d.Name from AcademicExperienceTypes d where p.Degree3id=d.Id)
when degree2id > isnull(degree3id, '' '') and isnull(degree2id, '' '') > isnull(degree1id, '' '') then (select d.Name from AcademicExperienceTypes d where p.Degree2id=d.Id)
when degree1id > isnull(degree3id, '' '') and isnull(degree1id, '' '') > isnull(degree2id, '' '') then (select d.Name from AcademicExperienceTypes d where p.Degree1id=d.Id)
end
July 5, 2012 at 1:47 pm
Basically i want to do this, but the syntax is wrong for p.Degree1id, p.Degree2id and p.Degree3id. I keep getting a 'multipart identifier could not be bound' error.
if (@Degree <> 0)
if p.Degree1id > p.Degree2id and p.Degree1id > p.Degree3id
Begin
set @QueryCondition = @QueryCondition + ' and p.Degree1id =' + cast(@Degree as varchar(10))
End
July 5, 2012 at 2:00 pm
I got the syntax to work, but the results are incorrect.
if (@Degree <> 0)
Begin
if (' p.Degree1id ' > ' p.Degree2id ' and ' p.Degree1id ' > ' p.Degree3id ')
--Begin
set @QueryCondition = @QueryCondition + ' and p.Degree1id =' + cast(@Degree as varchar(10))
End
if (@Degree <> 0)
Begin
if (' p.Degree2id ' > ' p.Degree3id ' and ' p.Degree2id ' > ' p.Degree1id ')
--Begin
set @QueryCondition = @QueryCondition + ' and p.Degree2id =' + cast(@Degree as varchar(10))
End
if (@Degree <> 0)
Begin
if (' p.Degree3id ' > ' p.Degree2id ' and ' p.Degree3id ' > ' p.Degree1id ')
--Begin
set @QueryCondition = @QueryCondition + ' and p.Degree3id =' + cast(@Degree as varchar(10))
End
July 5, 2012 at 3:06 pm
isuckatsql (7/5/2012)
I got the syntax to work, but the results are incorrect.if (@Degree <> 0)
Begin
if (' p.Degree1id ' > ' p.Degree2id ' and ' p.Degree1id ' > ' p.Degree3id ')
--Begin
set @QueryCondition = @QueryCondition + ' and p.Degree1id =' + cast(@Degree as varchar(10))
End
if (@Degree <> 0)
Begin
if (' p.Degree2id ' > ' p.Degree3id ' and ' p.Degree2id ' > ' p.Degree1id ')
--Begin
set @QueryCondition = @QueryCondition + ' and p.Degree2id =' + cast(@Degree as varchar(10))
End
if (@Degree <> 0)
Begin
if (' p.Degree3id ' > ' p.Degree2id ' and ' p.Degree3id ' > ' p.Degree1id ')
--Begin
set @QueryCondition = @QueryCondition + ' and p.Degree3id =' + cast(@Degree as varchar(10))
End
You still haven't fixed your if conditions. All three are checking for the same thing. If this is what you want you should combine them into a single condition.
Inside each of them you have if conditions that make no sense. You are checking for hardcoded string values. Perhaps you should post some more details about the tables, data and what you are trying to do. From what I can tell it seems you are greatly over complicating this. Another set of eyes, or dozens, can often present a new approach that is much easier.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 5, 2012 at 4:12 pm
Sample Profiles table:
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Profiles](
[Degree1id] [int] NULL,
[Degree2id] [int] NULL,
[Degree3id] [int] NULL,
CONSTRAINT [PK_profiles] PRIMARY KEY CLUSTERED
(
[Idnew] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Sample profiles data:
Degree1idDegree2idDegree3id
5NULLNULL
4NULLNULL
5NULLNULL
NULLNULLNULL
2NULLNULL
765
NULLNULLNULL
5NULLNULL
655
56NULL
54NULL
NULLNULLNULL
5NULLNULL
54NULL
NULLNULLNULL
NULLNULLNULL
NULLNULLNULL
65NULL
Sample Degree Table:
IdName
1None
2Vocational
3High School
4Associate's Degree
5Bachelor's Degree
6Master's Degree
7Doctorate
8Bachelors Degree
9Masters Degree
10Associate Degree
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AcademicExperienceTypes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](25) NULL,
CONSTRAINT [PK_AcademicExperienceTypes2] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
If a users enters @Degree data, that mean that they want all the candidates with a specific degree.
The problem is that since people may have up to three degrees, i have to check all 3 columns for the specific degree.
Thanks
July 5, 2012 at 8:10 pm
If a users enters @Degree data, that mean that they want all the candidates with a specific degree.
The problem is that since people may have up to three degrees, i have to check all 3 columns for the specific degree.
This is why you if at all possible you should normalize this data. You should not have columns Degree1, Degree2, Degree3. Instead you should have a column for Degree and a column to link that row to a person. This way a person can have as many degrees as they want. With the structure you have you can't have a 4th degree without some major rework.
Now to the problem at hand. You don't need all this complicated logic. To find all the people with a specific degree you only need something like this.
select [Columns]
from Profiles
where Degree1id = @DegreeID
or Degree2id = @DegreeID
or Degree3id = @DegreeID
There is no need for a case statement etc. Just get any row that has your @DegreeID in any of those columns.
I can't put together a query on the tables you posted because they appear to be incomplete and the sample data is not insert statements. It did however provide enough information to figure out how to "skin your cat". Hope this helps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 6, 2012 at 3:39 am
Sean,
Thanks for you help!
A case of 'you can't see the woods, for the trees!'
Ian
July 6, 2012 at 7:28 am
isuckatsql (7/6/2012)
Sean,Thanks for you help!
A case of 'you can't see the woods, for the trees!'
Ian
You're welcome. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 6, 2012 at 9:10 am
Sean,
The IF statement works fine like this:
if (@Degree <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Degree1id =' + cast(@Degree as varchar(10)) +
' or p.Degree2id =' + cast(@Degree as varchar(10)) +
' or p.Degree3id =' + cast(@Degree as varchar(10))
End
.... but i cant get the select to give me the data in one degree field. I think i am having a senior moment :w00t:
case
when degree1id = "@Degree" then (select d.Name from AcademicExperienceTypes d where p.Degree1id=d.Id)
when degree2id = @Degree then (select d.Name from AcademicExperienceTypes d where p.Degree2id=d.Id)
when degree3id = @Degree then (select d.Name from AcademicExperienceTypes d where p.Degree3id=d.Id)
as degree,
I tried this but because it is an SP, i keep getting an @Degree error!
Thanks
July 6, 2012 at 12:15 pm
Well your first case condition is wrapped with double quotes. If that doesn't fix it you need to provide ddl and sample data that is readily consumable (meaning I can hit F5 in SSMS and have populated tables) and a clear explanation of what your query needs to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 6, 2012 at 12:29 pm
case
when degree1id = "@Degree" then (select d.Name from AcademicExperienceTypes d where p.Degree1id=d.Id)
when degree2id = @Degree then (select d.Name from AcademicExperienceTypes d where p.Degree2id=d.Id)
when degree3id = @Degree then (select d.Name from AcademicExperienceTypes d where p.Degree3id=d.Id)
as degree,
Are @Degree and p.Degree#id the same data type?
If so, you can just do this:
SELECT
..., d.Name
FROM dbo.tablename p
LEFT OUTER JOIN dbo.AcademicExperienceTypes d ON
d.Id = @Degree
since that is the net effect of what you were doing anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 6, 2012 at 12:34 pm
case
when degree1id = "@Degree" then (select d.Name from AcademicExperienceTypes d where p.Degree1id=d.Id)
when degree2id = @Degree then (select d.Name from AcademicExperienceTypes d where p.Degree2id=d.Id)
when degree3id = @Degree then (select d.Name from AcademicExperienceTypes d where p.Degree3id=d.Id)
as degree,
Are @Degree and p.Degree#id the same data type?
If so, you can just do this:
SELECT
..., d.Name
FROM dbo.tablename p
LEFT OUTER JOIN dbo.AcademicExperienceTypes d ON
d.Id = @Degree
since that is the net effect of that code.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply