Link to home
Create AccountLog in
Avatar of amillyard
amillyardFlag for United Kingdom of Great Britain and Northern Ireland

asked on

sql OR statement

seem to having troubles with the OR statement within the attached SQL scripting.

if take out x3 of the selection criteria's (use anyone of them individually) -- no problem with the select statement -- as soon as the OR statement is used, no searching works

what is it with my OR's ?
SELECT [ClientCase_ID], [MasterAccount_ID], [DisplayNameApp1] FROM [ClientCases] WHERE (([ClientCase_ID] = @ClientCase_ID) OR ([MasterAccount_ID] = @MasterAccount_ID) OR ([FirstNameApp1] = @FirstNameApp1) OR ([LastNameApp1] = @LastNameApp1))

Open in new window

Avatar of pollock_d
pollock_d

could it be because your SELECT does not include [LastNameApp1] ?
nevermind
Avatar of chapmandew
SELECT  [ClientCase_ID],
        [MasterAccount_ID],
        [DisplayNameApp1]
FROM    [ClientCases]
WHERE   [ClientCase_ID] = @ClientCase_ID
        OR [MasterAccount_ID] = @MasterAccount_ID
        OR [FirstNameApp1] = @FirstNameApp1
        OR [LastNameApp1] = @LastNameApp1
just had to get rid of brackets?
I think so....looks right to me anyways...

I hate brackets.
I'd say...
Avatar of amillyard

ASKER

chapmandew:  have updated the sql statement as indicated

still not working -- the result I am looking to achieve is type search criteria in any of the x4 text fields -- and the sql select will check against all 4 options in the db to find a record(s)

is the OR the correct approach?
OR is the correct approach...be careful with your values from the text boxes.

The SQL statement I provided is correct.
chapmandew: i have used exactly the correct value in order to bring up a record.  when I take away x3 of the text fields search criteria within sql statement -- it works fine -- soon as I introduce x2 more search combinations in scripting stops working altogether.
can you post the actual values that you are giving to the statement when it returns no values and that statement when it does return a value..?
chapmandew:  something I noted just earlier -- when using the test query within the vs sql builder ...

is that where asked for values to test -- the first come upas and int32 and the other x3 are defaulting as 'blank' type.

the MasterAccount and ClientCases are integer fields within the db and the other 2 text fields are varchar in db

does this have an effect?   obviously all the fields within the asp.net page are .text be default...is there an issue here perhaps with entering via a text field and the db is holding int32 values -- is there a real-time type conversion required or does asp.net cover this automatically in these forms of sql statements?
I would say there is certainly something funky w/ those values.  If you have one true statement in 1000 different OR statements, the value is true,and records are returned.....something is for sure going on before it gets to the database.
chapmandew: ok, I think there is some progress...

have discovered that when I enter the firstname and lastname (i.e. use both text fields) -- there record(s) display (i.e. search found a result)...but if I enter only 1 of the fields -- no search found.

but that's puzzling me now... surely the OR is either one of those could be blank or not correctly entered -- as long as 1 is ok, then return a record(s)
SELECT  [ClientCase_ID],
        [MasterAccount_ID],
        [DisplayNameApp1]
 
FROM    [ClientCases]
 
WHERE [FirstNameApp1] = @FirstNameApp1
        OR [LastNameApp1] = @LastNameApp1

Open in new window

Again...please post the actual data being sent to the server from your app for these variables.  Without it, I cannot help you.
chapmandew:  how do I capture that information please?
You could run SQL Profiler to capture that information as the procedure is ran....

http://www.sql-server-performance.com/tips/sql_server_profiler_tips_p1.aspx

one question....when this SQL by hand in query analyzer...with values in place of the variables, do you get results?

SELECT  [ClientCase_ID],
        [MasterAccount_ID],
        [DisplayNameApp1]
FROM    [ClientCases]
WHERE   [ClientCase_ID] = @ClientCase_ID
        OR [MasterAccount_ID] = @MasterAccount_ID
        OR [FirstNameApp1] = @FirstNameApp1
        OR [LastNameApp1] = @LastNameApp1
how do I start the profiler -- the above hyperlinked article doesn't explain that part
IN SQL Server Management Studio, go to Tools (top menu) and then select SQL Server Profiler.
gone to tool, options -- SQL Server Profiler is not an option visible (or anywhere).
it appears that I do not have the SQL server profiler software -- and cannot find anywhere to download the tool.
one question....when this SQL by hand in query analyzer...with values in place of the variables, do you get results?

Yes -- I think so.... when I use the query builder (within Visual Studio 2008 Pro)  -- when testing you are requested to give the sample variable ... when I enter values in any combination within the 4 fields -- works perfectly everytime.

just that when I am entering via the web-page -- stops ... the only way I can get it to work via the web-page is too enter all 4 fields -- then instead of getting 1 record back, I get all that have the same surname...thats the only result I get (to work that is) via the web-entry side of things.

but locally...works perfectly -- what can affect this?
No idea....if it works perfectly everytime when you enter combinations then it seems there is some sort of problem on the visual studio side when it hanldles the data.  You can wanto to ask another question on the VS zone to see what may be happening.  Looks OK from the DB end though.
Since you're using the OR statement the processing would hamper as the Query has to try all the combination of ORs in your query. How big is your table content?

I know you've tried only with one OR (FirstName OR LastName), what's the probability that your table has either NULL for FirstName or the LastName?
there are definately NULL's in the table.
This means that if you're passing null to FirstName or the LastName then any row either having FirstName or the LastName NULL values are also eligible for the output, a performance impact.
I see .. is there a way in the SQL select statement to reject/ignore any null entries?
may be you will have to do

WHERE ([FirstName] <> NULL AND [FirstName] = @ FirstName) OR
([LastName] <> NULL AND [LastName] = @LastName)

or the best is construct your query in C# and then first this against the database. this way you can avoid unwanted filter criteria say search only on FirstName if the LastName is tested to be NULL in code

Novice_Novice:  have updated the sql select statement as follows -- even stripped down to a single text box entry -- no search results are coming at all now (previously was working with 1 text field)
SELECT [MasterAccount_ID], [ClientCase_ID], [DisplayNameApp1] 
 
FROM [ClientCases] 
 
WHERE ([LastNameApp1] <> NULL AND [LastNameApp1] = @LastNameApp1)

Open in new window

but, if I use the attach sql statement -- it works ok (as long as just using the single text field) -- if I add another field like FirstNameApp1 for example (or any other text field , including those that have no nulls in db -- like an index number) .
 WHERE (LastNameApp1 = @LastNameApp1)

Open in new window

what's the value you're passing in @LastNameApp1 and how many records did it return when you used one criteria?
the returns records contained anything with the smae lastname (in this context) -- on this test x2 names were returned -- i.e. x2 records, but x2 different records as x2 differnet account holders -- difference was the first names of acocunts etc when checking the datagrid list

the sql select statement pull all the same lastname'd accounts.

in that field -- text content such 'smith' for example
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
chapmandew:  that statement is working fine on a single text field box entry -- will expand and try on multiple box entries
WHERE ([LastNameApp1] IS NOT NULL AND [LastNameApp1] = @LastNameApp1)

Open in new window

chapmandew: thank you very much for your patience and time with trying to get this right :-))