June 9, 2003 at 11:33 am
That's correct. I want ALL active courses AND ALL active courses that starts with the number 10. Using AND does not generate any errors, it just doesn't return the courses beginning with the number 10...
June 9, 2003 at 11:53 am
Wanda,
Perhaps, you can reduce the ambiguity by giving an example of the table contents and the result set you are expecting.
Ram
June 9, 2003 at 12:14 pm
Does this get you what you want?
SELECT LEDef_ActiveInd, LEDef_Cd
FROM LEDef_PK
WHERE (LEDef_ActiveInd = 1) OR
(LEDef_ActiveInd = 1) AND (LEDef_Cd LIKE '10%')
June 9, 2003 at 12:20 pm
I gave an example of what's in the LEDef_Cd field in my post of 6/6/03 at 12:06. The LEDef_ActiveInd field has either a 1 (for true) or a 0 (for false)
June 9, 2003 at 12:35 pm
quote:
That's correct. I want ALL active courses AND ALL active courses that starts with the number 10. ...
Sorry, Wanda, that is different from your original post. If that's the case, then the previous poster is correct. You want to use an OR expression, not AND.
June 9, 2003 at 12:53 pm
Thanks all...I'll give it a try and report back.
June 9, 2003 at 12:54 pm
Wanda,
I repeat : Give us an example of the table contents and the result set.
Your example should contain both - ' Table Contents" and 'Expected result-set'. It won't be hard.
I am sure, the good folks here can help out.
Ram
June 10, 2003 at 12:31 am
I have tried this way
create table xxx ( LEDef_Cd varchar(20))
insert into xxx values ('DB356')
insert into xxx values ('40ILTC0007')
insert into xxx values ('HW442')
insert into xxx values ('51NWAC000020')
insert into xxx values ('10-CSSR-2013')
insert into xxx values ('10-FESW-0002')
SELECT LEDef_cd FROM xxx WHERE LEDef_Cd Like '%10%'
It works fine in the query analysed
I could not simulate the esact query because the value of the other filed is not known.
The result is
LEDef_cd
--------------------
10-CSSR-2013
10-FESW-0002
(2 row(s) affected)
Could you please give the data for both the fields ?
Is your query working fine in Query analyser and not working in ASP code ?
Regards
Fred
June 10, 2003 at 10:10 am
Im' new at this and have actually taken over someone else's code so I'm not familiar with the Query analyser. I have two files that is a part of the search catalog that contains the following information:
"SELECT LEDef_ActiveInd, LEDef_Cd
FROM LEDef_PK
WHERE (LEDef_ActiveInd = 1) OR
(LEDef_ActiveInd = 1) AND (LEDef_Cd LIKE '10%')
AND
SELECT LEDef_Cd, LEDef_Name, LEDef_Desc, LEDef_ActiveInd, FROM LEDef (LEDef_ActiveInd = 1) OR
(LEDef_ActiveInd = 1) AND (LEDef_Cd LIKE '10%')"and LEDef_PK = " & Request.QueryString("id"),quote:
I can get it to recognize and return results for all active courses [LEDef_ActiveInd] field, but I can't get it to return just the courses that begin with "10" from the LEDef_Cd field.
Using the above, I'm getting all courses [active and inactive].
r_achar, I'm not sure if this answers your question but if not I'll try again. Just let me know.
Thanks everyone for helping me with this...
June 10, 2003 at 11:28 am
Here's the problem:
WHERE (LEDef_ActiveInd = 1) OR(LEDef_ActiveInd = 1) AND (LEDef_Cd LIKE '10%')
You are asking:
Give me LEDef_ActiveInd = 1, OR give me (LEDef_ActiveInd = 1) AND (LEDef_Cd LIKE '10%').
So, when it finds LEDef_ActiveInd = 1 it says okay...here's what you want, I quit. So the second part of the OR is never even looked at. Try:
WHERE (LEDef_ActiveInd = 1 AND LEDef_Cd LIKE '10%')
-SQLBill
June 10, 2003 at 1:25 pm
The correct expression is (OR), if you use (AND) you'll end up with courses that are
active and courses that begin with 10 only.
This reply by jpipes works if you change AND to OR
SELECT LEDef_PK FROM LEDef WHERE LEDef_ActiveInd = 1 AND LEDef_Cd LIKE '10-%'
If you still don't get your results (courses that begin with 10), then you may want to
use the LTRIM function
SELECT LEDef_PK
FROM LEDef
WHERE (LEDef_ActiveInd = 1 AND LTRIM(LEDef_Cd) LIKE '10-%')
MW
MW
June 10, 2003 at 1:38 pm
for one of my files, I have the following code. Where would the "WHERE" part of this code fit in.
<% Set r = createobject ("ADODB.Recordset") %>
<% r.Open "SELECT LEDef_Cd, LEDef_Name, LEDef_Desc, LEDef_ActiveInd, LEDef_PublicInd FROM LEDef WHERE LEDef_PK = " & Request.QueryString("id"), "UID=aspen;PWD=aspen;DSN=aspen" %>
Thanks...
June 11, 2003 at 7:18 am
Do you mean
<% r.Open "SELECT LEDef_Cd, LEDef_Name, LEDef_Desc, LEDef_ActiveInd, LEDef_PublicInd FROM LEDef WHERE (LEDef_ActiveInd = 1) OR (LTRIM(LEDef_Cd) LIKE '10%')", "UID=aspen;PWD=aspen;DSN=aspen" %>
Sorry had to edit as I got confused.
Edited by - davidburrows on 06/11/2003 07:24:18 AM
Far away is close at hand in the images of elsewhere.
Anon.
June 11, 2003 at 4:17 pm
quote:
I want ALL active courses AND ALL active courses that starts with the number 10
Then really you should just ask for "active courses". You can skip the active-10 part of the query, because the "active" criteria includes ALL active rows regardless of the value of the LEDef_Cd column. If you want inactive "10" rows then you should use the "OR".
For example: If you want a list of both "all men" AND "all men between the ages of 30-40 yrs", then you can just get a list of "all men" and the 30-40 year-olds will be in the list even though you didn't ask for them specifically by age.
-Dan
-Dan
June 12, 2003 at 6:27 am
Check with profiler that the SQL code you think is being passed to SQL Server is the code that is actually being sent to SQL Server from the asp page.
Also, think about using stored procs and parameters for these kind of affairs. Will definitely simplfy matters.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply