if / else statement?
-
Hi Vladan,
I ran your query and I get this error:
Invalid column name 'trueofficeID'.
I don't have a column called trueofficeID...do I need to create one?
select AOofficeName as Office, count(ARid) as totReferrals
from AdminResource AR
join ProfRequest PR on PR.prID = AR.ARprofrequestID
join (SELECT uname,
CASE WHEN uname = 'james2' THEN 999 ELSE uadminofficeID END as CO1
FROM Users) U on u.uname = PR.schoolworker
join AdminOffice AO on AO.aoID = U.trueofficeID
where ARenteredDate BETWEEN '01/01/2005' and '10/18/2005'
and ARcurrentStatus >= 0
group by AOofficeName
order by AOofficeName
-
No, the first query in my post is (or should be, if I didn't make any mistake) the same query you are using now - taken from your post, just translated into JOIN syntax.
Instead of this query, try to use the second one from my post... to avoid possible misunderstanding, this is what I think:
select AOofficeName as Office, count(ARid) as totReferrals
from AdminResource AR
join ProfRequest PR on PR.prID = AR.ARprofrequestID
join (SELECT uname,
CASE WHEN uname = 'james2' THEN 999 ELSE uadminofficeID END as trueofficeID
FROM Users) U on u.uname = PR.schoolworker
join AdminOffice AO on AO.aoID = U.trueofficeID
where ARenteredDate BETWEEN '01/01/2005' and '10/18/2005'
and ARcurrentStatus >= 0
group by AOofficeName
order by AOofficeName
The red part is the only thing that I changed - instead of "JOIN Users" there is "JOIN (query that returns needed columns from Users with modification to adminofficeID value for James2)". Make sure you use the appropriate ID instead of my dummy 999!
I didn't check the CASE and RGR knows more about efficiency than me anyway, so let's wait for his expert opinion. I'd think that the difference wouldn't be big either way.
To your question: No, this column is created using the alias in CASE... END as trueofficeID... You renamed the column to CO1 - so that's why it doesn't work. Just type trueofficeID instead of CO1 right after the END keyword.
-
Another solution would be to create a 'fake id' column. Call it something like FakeAOId. Fill it with the actual AOIds and then update James2 to some other AOId that shouldn't ever get duplicated. Then use the real column for any queries that don't care about the two different James and use the fake column for any queries that do need to recognize the different James.
-SQLBill
-
Thank you Vladan, I tried your code, and it runs, but instead of taking the values from James2 and adding them to the correct department, it just deletes James2 from the result set.
Here are results from the original query:
Astronomy Jim 12
Astronomy Paul 427
CIS % 128
CIS Cedric 482
CIS Ysami 1849
CIS Harold 4096
CIS Jes 772
CIS Ron 910
CIS JT 1308
CIS Robert 1744
CIS Casey 1220
CIS Nicole 3192
CIS Judy 3173
CIS Smithy 1678
CIS James1 3262
CIS James2 459 <---should count in Astronomy
CIS Stace 2082
Here are results from the new query (James2 is missing):
Astronomy Jim 12
Astronomy Paul 427
CIS % 128
CIS Cedric 482
CIS Ysami 1849
CIS Harold 4096
CIS Jes 772
CIS Ron 910
CIS JT 1308
CIS Robert 1744
CIS Casey 1220
CIS Nicole 3192
CIS Judy 3173
CIS Smithy 1678
CIS James1 3262
CIS Stace 2082
-
SQL Bill,
Oh I see. So modify the user table, and add another column as you say. The column would be exactly like the real one, but James2 would be different?
So then I could use my original query, and instead of
and UadminofficeID = AOid
I would put
and UfakeadminofficeID = AOid
is that what you mean?
Thanks!
-
Would this work for what you are trying to get?:
select
case when username = 'James1' then 'CIS' when username = 'James2' then 'Astronomy' else AOofficeName end as Office,
count(ARid) as totReferrals from AdminResource, ProfRequest, Users, AdminOffice
where ARenteredDate BETWEEN '01/01/2005' and '10/18/2005'
and ARcurrentStatus >= 0
and ARprofrequestID = PRid
and PRschoolworker = Uname
and UadminofficeID = AOid
group by case when username = 'James1' then 'CIS' when username = 'James2' then 'Astronomy' else AOofficeName end
order by case when username = 'James1' then 'CIS' when username = 'James2' then 'Astronomy' else AOofficeName end
Sam
-
Sam T, thank you
I don't think this would work with my query. Because I am not directly pulling the office names, I am linking them up through the user...
here:
UadminofficeID = AOid , UadminofficeID is a column in the user table that relates to this primary key of the office table.
-
Right...you will still perform the link but will manipulate the data that comes back. You can also change the column you are linking on to use the case statement so in your original statement:
UadminofficeID = AOid
becomes:
AOid = (case when username = 'James1' then [uadminofficeID for James1] when username = 'James2' then [uadminofficeID for James1] else UadminofficeID end)
You can reduce this to just a single when and an else, but I can't remember which department was on James' rows in the user table.
Sam
-
A new column has pros and cons... If later more people have to be reported sometimes here and sometimes there, like James now, it will be easier to implement it. But you have to fill the column with data and keep the data up to date.
However, I would suggest to do it so that this new column will have NULL value for all people except those where you want to display them in a different way (that is, James2). Otherwise, remember that you have to populate the new fake column every time a new employee is added - and update it every time you the standard adminoffice is used. Of course you can have triggers do that, but we should be looking for a SIMPLE solution.
Now that I think about it again, probably the new column would bring too many complications ... if the problem is just with this one report, modify the report. I don't know why you're getting this error with my code... can you please post the SQL precisely as you ran it, and post also the values of ID for Astronomy and CIS departments (result of select aoID, aoofficename from adminoffice where aoofficename in ('Astronomy','CIS')
Maybe you simply forgot to modify the "999", which is a placeholder? Instead of 999, you have to write the aoID of Astronomy.
-
Jurushia,
Some of the best minds at SSC are working on your problem. However, I'd like to make a couple of suggestions. You've posted a query that references four tables - please post the DDL for the tables. Although I recognize the naming convention you are using for column names, it is best to qualify columns in a multiple-table query with the table name or a local alias to eliminate any confusion. It seems there may be a design flaw in your database if there is a requirement to handle users with multiple departments. While temporarily hard coding special cases may be acceptable for queries you need right away, I would seriously consider making other more permanent changes to your data model to handle the situation generally.
-
Jurushia,
Yes that is what I meant.
-SQLBill
-
Hi!!!
RGR'us.....
will that works..
select AOofficeName as Office, count(ARid) as totReferrals ,case when UserName IN ('james1', 'james2') THEN 'James' ELSE UserName END AS UserName
from AdminResource, ProfRequest, Users, AdminOffice
where ARenteredDate BETWEEN '01/01/2005' and '10/18/2005'
and ARcurrentStatus >= 0
and ARprofrequestID = PRid
and PRschoolworker = Uname
and (UadminofficeID = AOid OR UadminofficeID = (SELECT AOid FROM TABLE_NAME WHERE USERNAME = 'james1' OR UadminofficeID = (SELECT AOid FROM TABLE_NAME WHERE USERNAME = 'james2')
group by AOofficeName, case when UserName IN ('james1', 'james2') THEN 'James' ELSE UserName END
order by AOofficeName
Regards,
Papillon
-
Can't tell without sample data... that's why this thread is still running.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply