Tommi
|
| Posted: 02/07/2004, 3:41 PM |
|
ASP / SQL / CCS 2.2
I'm having problems with the single field search, it does the search fine, however I can't get it to only show fields that eg. "ACTIVE" or "SPECIAL", under the STATUS field.
The single field search will return a full list of items regardless of the ACTIVE or SPECIAL requirements. (1) I have include a statement which I added, I presume shoudl work, and (2) I have included the original statement generated by CCS 2.2
- From the return/result table
(1) Modified, but does not work.
SELECT pcode, product,category, status
FROM tbproducts
WHERE pcode LIKE '%{s_keyword}%'
OR product LIKE '%{s_keyword}%'
OR category LIKE '%{s_keyword}%'
OR product LIKE '%{s_keyword}%'
(status='ACTIVE' OR status='SPECIAL')
ORDER BY lastupdate
(2) Original
SELECT pcode, product,category, status
FROM tbproducts
WHERE pcode LIKE '%{s_keyword}%'
OR product LIKE '%{s_keyword}%'
OR category LIKE '%{s_keyword}%'
OR product LIKE '%{s_keyword}%'
ORDER BY lastupdate
I'm not sure if this a bug, but I tried everything possible (i could think of). Can you please help me resolved as just about given up!!!!
|
|
|
 |
Edd
Posts: 547
|
| Posted: 02/07/2004, 3:50 PM |
|
Tommi
Quote :SELECT pcode, product,category, status
FROM tbproducts
WHERE pcode LIKE '%{s_keyword}%'
OR product LIKE '%{s_keyword}%'
OR category LIKE '%{s_keyword}%'
OR product LIKE '%{s_keyword}%'
(status='ACTIVE' OR status='SPECIAL')
ORDER BY lastupdate
Your SQL is the issue.
Try
OR product LIKE '%{s_keyword}%'
AND (status='ACTIVE' OR status='SPECIAL')
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
 |
|
Tommi
|
| Posted: 02/07/2004, 4:55 PM |
|
Thanks Edd,
I have tried that as well and the same issue occurs. That's why I'm also wondering if it is a bug somewhere in the codes or CCS...
|
|
|
|
CDS
|
| Posted: 02/08/2004, 5:49 AM |
|
Quote Tommi:
Thanks Edd,
I have tried that as well and the same issue occurs. That's why I'm also wondering if it is a bug somewhere in the codes or CCS...
Try putting that statement at the top of your parameters. I have had such issues, and it is usually fixed when moving a statement up the ladder.
|
|
|
|
Don Safar
|
| Posted: 02/08/2004, 10:25 AM |
|
The SQL as posted is missing an "AND" or "OR" statement.
SELECT pcode, product,category, status
FROM tbproducts
WHERE pcode LIKE '%{s_keyword}%'
OR product LIKE '%{s_keyword}%'
OR category LIKE '%{s_keyword}%'
OR product LIKE '%{s_keyword}%'
AND (status='ACTIVE' OR status='SPECIAL') <-- changed this line. I added AND
to the beginning.
ORDER BY lastupdate
If you run the sql in query anlyser, does it work?
"Tommi" <Tommi@forum.codecharge> wrote in message
news:64025779a61391@news.codecharge.com...
> ASP / SQL / CCS 2.2
>
> I'm having problems with the single field search, it does the search fine,
however I can't get it to only show fields that eg. "ACTIVE" or "SPECIAL",
under the STATUS field.
>
> The single field search will return a full list of items regardless of the
ACTIVE or SPECIAL requirements. (1) I have include a statement which I
added, I presume shoudl work, and (2) I have included the original statement
generated by CCS 2.2
>
> - From the return/result table
>
> (1) Modified, but does not work.
>
> SELECT pcode, product,category, status
> FROM tbproducts
> WHERE pcode LIKE '%{s_keyword}%'
> OR product LIKE '%{s_keyword}%'
> OR category LIKE '%{s_keyword}%'
> OR product LIKE '%{s_keyword}%'
> (status='ACTIVE' OR status='SPECIAL')
> ORDER BY lastupdate
>
> (2) Original
>
> SELECT pcode, product,category, status
> FROM tbproducts
> WHERE pcode LIKE '%{s_keyword}%'
> OR product LIKE '%{s_keyword}%'
> OR category LIKE '%{s_keyword}%'
> OR product LIKE '%{s_keyword}%'
> ORDER BY lastupdate
>
> I'm not sure if this a bug, but I tried everything possible (i could think
of). Can you please help me resolved as just about given up!!!!
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Edd
Posts: 547
|
| Posted: 02/08/2004, 1:14 PM |
|
The positioning has nothing to do with it - it is about bracketing:-
Your Code -
Quote :SELECT pcode, product,category, status
FROM tbproducts
WHERE pcode LIKE '%{s_keyword}%'
OR product LIKE '%{s_keyword}%'
OR category LIKE '%{s_keyword}%'
OR product LIKE '%{s_keyword}%'
(status='ACTIVE' OR status='SPECIAL')
ORDER BY lastupdate
Really should be
SELECT pcode, product,category, status
FROM tbproducts
WHERE ( pcode LIKE '%{s_keyword}%' <--- Bracket
OR product LIKE '%{s_keyword}%'
OR category LIKE '%{s_keyword}%'
OR product LIKE '%{s_keyword}%' ) AND <---- Close Brancket + AND
(status='ACTIVE' OR status='SPECIAL')
ORDER BY lastupdate
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
|
