OR CLAUSE IN SQL 1

[abyinsydney]

greetings fellow citizens
i'm trying to create a search from the database using OR CLAUSE .WHAT IS TH ESYNTAX OF THE or CLAUSE


select* from financial_transactions_archive_extract where financial_transactions_archive_extract.date_transaction BETWEEN '04072001''20072001' OR financial_transactions_archive_extract.processdate_trans BETWEEN '4072001' '7072001'& financial_transactions_archive_extract.cif_no='0000578876'


THE ABOVE QUERY IS GIVING AN ERROR CAN ANY BODY SUGGEST HOW TO USE OR CLAUSE
VENU YOUR INPUT WOULD BE AWAITED

THANX A OT FOR THE SAME

ABY

 

[sedj]

You have several errors in your syntax. This is how it should be :

SELECT *  FROM financial_transactions_archive_extract 
WHERE
financial_transactions_archive_extract.date_transaction BETWEEN '04072001' AND '20072001' 
OR
financial_transactions_archive_extract.processdate_trans BETWEEN '4072001' AND '7072001'
AND
financial_transactions_archive_extract.cif_no = '0000578876';

 

[abyinsydney]

sedj
the query you have written is giiving me sql error could you please have a look at the query i'm using mysql database

 

[sedj]

It must be the way you are typing it, I am using MySQL, and get no syntactical errors. My test statement sequence was :

create table qqq (aaa varchar(20), bbb varchar(20), ccc varchar(20));

insert into qqq values ('11111' ,'22222', '33333');

insert into qqq values ('11111a' ,'22222a', '33333a');

SELECT *  FROM qqq 
WHERE
qqq.aaa BETWEEN '04072001' AND '20072001' 
OR
qqq.bbb BETWEEN '4072001' AND '7072001'
AND
qqq.ccc = '0000578876';

Of course you may want to constrict the OR statements like this :

SELECT *  FROM qqq 
WHERE
(qqq.aaa BETWEEN '04072001' AND '20072001' 
OR
qqq.bbb BETWEEN '4072001' AND '7072001')
AND
qqq.ccc = '33333a';

 

[abyinsydney]

Sedj
in reply to your answer


SELECT * FROM qqq
WHERE
(qqq.aaa BETWEEN '04072001' AND '20072001'
OR
qqq.bbb BETWEEN '4072001' AND '7072001')
AND
qqq.ccc = '33333a';


if i leave the field qqq.bbb blank like



query1. SELECT * FROM qqq
WHERE
(qqq.aaa BETWEEN '04072001' AND '20072001'
OR
qqq.bbb BETWEEN ' ' AND ' ')
AND
qqq.ccc = '33333a';

query2.SELECT * FROM qqq
WHERE
(qqq.aaa BETWEEN '04072001' AND '20072001'
OR
qqq.bbb BETWEEN '4072001' AND '7072001')
AND
qqq.ccc = '33333a';

then i get a differernt answer

i want that among the fields if user puts in one of the dates between two text fields then it should give me the same result as mentioned by you .

query 1 should give me the result between two dates however it is also comparing the values between qqq.bbb it should only compare qqq.aaa and give me the result

could you please help me out on this

aby

 

[sedj]

Aby, sorry I'm that that good in SQL, I suggest asking in one the forums here, like forum220

sedj