Case Statement error. 1

[BSG75]

I am trying to run a case statement in a select statement. i am trying to do Case Priority when priority =1 then 'low', but it is not working in keep getting incorrect syntax error messages on kewords like Case and then on the '=' sign. The select statement is as follows any help on this issue of why it is showing up and how to correct it would be appreciated.:
"SELECT HPD_HelpDesk.Submitted_By, DateAdd(s,HPD_HelpDesk.Assign_Time,'12/31/1969 18:00:00 PM') as Ticket_Assigned_Time, HPD_HelpDesk.Case_ID_, HPD_HelpDesk.SubItem, HPD_HelpDesk.Assigned_To_Individual_, HPD_HelpDesk.Priority FROM REMEDY_SUPPORT.dbo.HPD_HelpDesk " & _
"WHERE (HPD_HelpDesk.Assigned_To_Individual_='Leticia Najera' OR HPD_HelpDesk.Assigned_To_Individual_='Loretta G. Villarreal' OR HPD_HelpDesk.Assigned_To_Individual_='Virginia S. Nevarez' OR HPD_HelpDesk.Assigned_To_Individual_='Robert G. Villa' OR HPD_HelpDesk.Assigned_To_Individual_='Calvin D. Drummond') Select HPD_HelpDesk.Priority Case Priority ==1 Then 'Low' End " & _
"AND (HPD_HelpDesk.SubItem <> 'New Printer ID Assignments' AND HPD_HelpDesk.SubItem <> 'EFS Install' AND HPD_HelpDesk.SubItem <> 'Changes to Location Table' AND HPD_HelpDesk.SubItem <> 'Needs more Instant Data Reports Added to site' AND HPD_HelpDesk.SubItem <> 'Setup') " & _
"AND (DATEADD(s, Assign_Time, '12/31/1969 18:00:00 PM') > '" & Date & "')" & "ORDER BY DATEADD(s, Assign_Time, ' 12/31/1969 18:00:00 PM ') DESC"
Adodc1.Refresh

 

[ChewDoggie]

It's difficult to decipher your code but I'll give it a shot. You can't (to my knowledge) insert a "Select Case" statement into a sql statement. You may want to try something like this:

sql = "SELECT HPD_HelpDesk.Priority, HPD_HelpDesk.Submitted_By, DateAdd(s,HPD_HelpDesk.Assign_Time,'12/31/1969 18:00:00 PM') as Ticket_Assigned_Time, HPD_HelpDesk.Case_ID_, HPD_HelpDesk.SubItem, HPD_HelpDesk.Assigned_To_Individual_, HPD_HelpDesk.Priority FROM REMEDY_SUPPORT.dbo.HPD_HelpDesk WHERE (HPD_HelpDesk.Assigned_To_Individual_='Leticia Najera' OR HPD_HelpDesk.Assigned_To_Individual_='Loretta G. Villarreal' OR HPD_HelpDesk.Assigned_To_Individual_='Virginia S. Nevarez' OR HPD_HelpDesk.Assigned_To_Individual_='Robert G. Villa' OR HPD_HelpDesk.Assigned_To_Individual_='Calvin D. Drummond')

Select Case HPD_HelpDesk.Priority
Case 1
sql = sql & something
Case 2
sql = sql & something else
Case else
msgbox "Error condition"
end select

' execute sql statement...

HTH

AMACycleLoony

 

[ChewDoggie]

On the other hand, you have a double equals sign in there, try making it a single equals sign.

change "=="

TO

"="

AMA

 

[gmmastros]

Couple things....

1. Looks like you have your case statement in the where clause.
2. You don't have an else for the case statement.

Here's a code snippet to get you started.

SELECT 	HPD_HelpDesk.Submitted_By, 
	DateAdd(s,HPD_HelpDesk.Assign_Time,'12/31/1969 18:00:00 PM') as Ticket_Assigned_Time, 
	HPD_HelpDesk.Case_ID_, 
	HPD_HelpDesk.SubItem, 
	HPD_HelpDesk.Assigned_To_Individual_, 
	HPD_HelpDesk.Priority,
	Case 	When HPD_HelpDesk.Priority = 1
		Then 'Low'
		Else 'High'
		End As Priority

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BSG75]

I put this into my statement and did not get any errors, but when i run the program and in the column labled priority it still shows 1 and does not display Low or High I am trying to get it to change the numbers 0-4 to Low, medium, high, urgent. there is no table in the that has value description for Priority, so i need to set it myself.
I thought using the case statement would work.

Case When HPD_HelpDesk.Priority = 1 Then 'Low' Else 'High' End as Priority

this is an example of the data grid and information

Case id|Submitted by|analyst|Subitem|Priority|Time Assigned|
764632 network id name Lois 1 3:25pm

 

[gmmastros]

My suggestion would be to create a priority table with the numbers and words hard coded. i.e.

PriorityValue|PriorityText
0|Low
1|Medium
2|High
3|Urgent

Then a simple join will return the words you are really looking for.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BSG75]

Thanks gmmastros i will try it that way.

 

[chiph]

Star to gmmastros for suggesting normalizing the database.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[BSG75]

Thank you so much for your help gmmastros. My only problem is that i don't think i am communicating effectively what the problem is. The database is called Remedy Support and it is a SQL Database. A table in the database is called hpd helpdesk. This database is on a server and i just pull the information from the database into VB with a select statement choosing what i want and then put that information into a datagrid. I make the connection to the database using an Adodc. I can't make any changes or additions to the database and the information that is in there is not updateable. I have to work with what is in the database and within the table. I have searched the database for a table that would have a value description for the Priority column, but there is not one. That column only has values from 0-3, it is left up to me to turn that information into something that can be easily read by others ie Low, Medium, high, urgent. That is why i thought maybe a case statement would help. If you have any other suggestions on this issue i would appreciate it i have been on this for two weeks.

 

[gmmastros]

Change the select to....

SELECT     HPD_HelpDesk.Submitted_By,
    DateAdd(s,HPD_HelpDesk.Assign_Time,'12/31/1969 18:00:00 PM') as Ticket_Assigned_Time,
    HPD_HelpDesk.Case_ID_,
    HPD_HelpDesk.SubItem,
    HPD_HelpDesk.Assigned_To_Individual_,
    HPD_HelpDesk.Priority,
    Case When HPD_HelpDesk.Priority = 0 Then 'Low'
         When HPD_HelpDesk.Priority = 1 Then 'Medium'
         When HPD_HelpDesk.Priority = 2 Then 'High'
         When HPD_HelpDesk.Priority = 3 Then 'Urgent'
         End As PriorityText

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BSG75]

I put in the case statement and ran it with no errors, which is great, but it did not return any the text in the Priority column, I guess i can't use the case statement.