Query

[richself]

I have the following query:

Shift consist of Blue, Green, Red and Yellow
Equipment consist of 7301, 7302 and 7303


SELECT Downtime, Shift, Equipment
FROM Report
WHERE (Shift = ?) OR
(Equipment = ?)

Lets say I ask for Yellow and don't put in anything for equipment. I get back all yellow and all equipment. Works fine until I add the equipment, then I get back all shifts along with all related equipment. I have tried changing the ' OR ' to an ' AND ' but then it requires me to fill in both fields. If I put in a shift thats not one of the 4 then I get back nothing. Ideas?
The query is built through a VB.Net windows form. I hope this is still relivent to this forum.

Thanks

 

[Erics44]

Use AND if you just want to get all the yellow shifts with the equipment 7301 for instance

where shift = 'Yellow' and equipment = '7301'

the results would be

Shift Equipment
-------------------
Yellow 7301

if you use OR from you example you would get something like

Shift Equipment
-------------------
Yellow 7301
Yellow 7302
Yellow 7303
Blue 7301
Red 7301

 

[richself]

The problem seems to be when I don't put in one or the other. Say I just put in Shift, it won't return anything. Is there an inputed wildcard? I understood that the % was it but I'm guessing that doesn't work when it's inputed instead of lets say 'yellow'.

 

[Erics44]

I am very familiar with windows forms in visual studio .net but i am not sure how you have built the SQL in your form so I cant help, if you only want to return results from your table with criteria on just one field then this is the only field you would include in your where clause

sorry if I am not understanding your question

 

[Pattycake245]

If you are passing these two values as variables from a web app you can use case statements. Something like:

SELECT    Downtime, Shift, Equipment
FROM      Report
WHERE     Shift = case when @shift<>'' then @shift else shift end 
AND        Equipment=case when @equipment<>'' then @equipment else equipment end

Tim

 

[richself]

Erics - I build the SQL statement when I configure the DataAdapter. Just by right clicking on it and going through the wizard.

 

[Erics44]

rich

when using a data adapter to fill a dataset i would use code something like

Dim conn As New SqlConnection("data source=server_name;initial catalog=Database_Name;integrated security=SSPI;persist security info=False")

Dim ad As New SqlDataAdapter(strSQL, conn)
Dim ds As New DataSet()
ad.Fill(ds)

conn = Nothing
ad = Nothing
ds = Nothing

so then it would all depend on what my form looks like and what controls were on it to how I built the "strSQL" statement.