Rohit Bajaj
asked on
MYSQL query
Hi,
I constructed the following query :
SELECT ESD.search_type FROM extreme_search_data ESD where ESD.dep_date < curdate()+30 and ESD.search_type in ('O','R') ,(SELECT @rownum:=0) r INTO OUTFILE 'file2' FIELDS TERMINATED BY ','
This gives an error saying wrong syntax.
But if i remove the where clause It works properly.
Working query :
SELECT ESD.search_type FROM extreme_search_data ESD,(SELECT @rownum:=0) r INTO OUTFILE 'file2' FIELDS TERMINATED BY ','
How do i combine the where clause into the above working query
Thanks
I constructed the following query :
SELECT ESD.search_type FROM extreme_search_data ESD where ESD.dep_date < curdate()+30 and ESD.search_type in ('O','R') ,(SELECT @rownum:=0) r INTO OUTFILE 'file2' FIELDS TERMINATED BY ','
This gives an error saying wrong syntax.
But if i remove the where clause It works properly.
Working query :
SELECT ESD.search_type FROM extreme_search_data ESD,(SELECT @rownum:=0) r INTO OUTFILE 'file2' FIELDS TERMINATED BY ','
How do i combine the where clause into the above working query
Thanks
ASKER
Hi,
The query mentioned by you is giving an error :
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file2' FIELDS TERMINATED BY ','
FROM extreme_search_data ESD, (SEL' at line 4
I am assuming the SELECT @rownum:=0 part is selecting the first row from the output and dumping it into a file.
This was a query i got in a project. Which was working.
But i need to add where clause to some columns.
But when adding where clause it gives syntax error
The query mentioned by you is giving an error :
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE 'file2' FIELDS TERMINATED BY ','
FROM extreme_search_data ESD, (SEL' at line 4
I am assuming the SELECT @rownum:=0 part is selecting the first row from the output and dumping it into a file.
This was a query i got in a project. Which was working.
But i need to add where clause to some columns.
But when adding where clause it gives syntax error
Hmmm. The query runs fine for me. Double check it matches exactly what I posted.
The @rownum is a generated variable used to put a row number into the file - it has nothing to do with selecting anything. If your WHERE clause selects 10 records then they will be numbered 1,2,3,4 etc. It's just a row counter. I presumed you wanted that because I couldn't think of another reason why you included it in your query.
Something else I've just noticed. You seem to be adding 30 to the current date. That won't add 30 days to it, it will just add 30 to the numerical value of the date, so today plus 30 = 20131135 - 35th November!! You should use the DATE_ADD function in your query:
The @rownum is a generated variable used to put a row number into the file - it has nothing to do with selecting anything. If your WHERE clause selects 10 records then they will be numbered 1,2,3,4 etc. It's just a row counter. I presumed you wanted that because I couldn't think of another reason why you included it in your query.
Something else I've just noticed. You seem to be adding 30 to the current date. That won't add 30 days to it, it will just add 30 to the numerical value of the date, so today plus 30 = 20131135 - 35th November!! You should use the DATE_ADD function in your query:
SELECT ESD.search_type, @rownum:=@rownum + 1 row_number
INTO OUTFILE 'file2' FIELDS TERMINATED BY ','
FROM extreme_search_data ESD, (SELECT @rownum:=0) r
WHERE ESD.dep_date < DATE_ADD(CURDATE(), INTERVAL 30 DAY) AND ESD.search_type IN ('O','R');
If you need to add 1 month, then use INTERVAL 1 MONTH instead
ASKER
Hi,
Thanks for pointing out the month thing.
I just checked your query from an online mySql checker :
http://www.piliapp.com/mysql-syntax-check/
Please see if it gives you also the error on this online syntax checker.
I think i am copying the query correctly
Thanks for pointing out the month thing.
I just checked your query from an online mySql checker :
http://www.piliapp.com/mysql-syntax-check/
Please see if it gives you also the error on this online syntax checker.
I think i am copying the query correctly
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI,
The following query works :
SELECT CONCAT(CONCAT(CONCAT(CONCA T(CONCAT(C ONCAT(CONC AT(CONCAT( CONCAT(CON CAT(CONCAT (CONCAT
(if(@rownum=0,'<items>','' ),CONCAT(' <item>','< sku>')),CO NCAT(p.id, '</sku>')) ,
CONCAT('<departure_city>', p.origin)) ,CONCAT('< /departure _city>','< destinatio n_city>')) ,
CONCAT(p.destination,'</de stination_ city>')),C ONCAT('<tr avel_start _date>',p. dep_date)) ,
CONCAT('T00:00:00+05:30</t ravel_star t_date>',' <travel_en d_date>')) ,CONCAT(p. arrival_da te,'T00:00 :00+05:30< /travel_en d_date>')) ,
CONCAT('<price>',p.price)) ,
CONCAT('</price>','<flight _length>') ),
CONCAT(@rownum:=@rownum+1, '</flight_ length>')) ,
CONCAT('</item>',if(@rownu m=(select count(*) from extreme_search_data),'</it ems>','')) )
FROM extreme_search_data p ,(SELECT @rownum:=0) r where dep_date < DATE_ADD(CURDATE(), INTERVAL "+duration+" DAY) and search_type in ("+searchType+") INTO OUTFILE '"+ filename1 +"' FIELDS TERMINATED BY ','
The following query works :
SELECT CONCAT(CONCAT(CONCAT(CONCA
(if(@rownum=0,'<items>',''
CONCAT('<departure_city>',
CONCAT(p.destination,'</de
CONCAT('T00:00:00+05:30</t
CONCAT('<price>',p.price))
CONCAT('</price>','<flight
CONCAT(@rownum:=@rownum+1,
CONCAT('</item>',if(@rownu
FROM extreme_search_data p ,(SELECT @rownum:=0) r where dep_date < DATE_ADD(CURDATE(), INTERVAL "+duration+" DAY) and search_type in ("+searchType+") INTO OUTFILE '"+ filename1 +"' FIELDS TERMINATED BY ','
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for Robinsuri's comment #a39658522
for the following reason:
I found it myself
Accepted answer: 0 points for Robinsuri's comment #a39658522
for the following reason:
I found it myself
Seriously! That's the query you're going with. How does that query represent anything like you originally asked for.
Why use 4 lines of code when you can use 20.
Good luck maintaining that one.
Why use 4 lines of code when you can use 20.
Good luck maintaining that one.
ASKER
SELECT ESD.search_type FROM extreme_search_data ESD, (SELECT @rownum:=0) r where ESD.dep_date < curdate()+30 and ESD.search_type in ('O','R') INTO OUTFILE 'file2' FIELDS TERMINATED BY ','
The above is the exact query i meant. I posted a bigger query mistakenly.
This worked on my machine.
The above is the exact query i meant. I posted a bigger query mistakenly.
This worked on my machine.
Your query doesn't really make sense. From the original, you've just moved the (SELECT @rownum:=0) - which does absolutely nothing. What exactly are you expecting that to do!
If you don't need it, then just drop it.
Your query is also selecting dates less than 50th November!!!
Please have a read of this - http://support.experts-exc hange.com/ customer/p ortal/arti cles/48141 9 - and then explain the Grade C
Thanks
If you don't need it, then just drop it.
Your query is also selecting dates less than 50th November!!!
Please have a read of this - http://support.experts-exc
Thanks
ASKER
HI,
Having look at the link given by you.
Thx on the 50th November part. I agree with it.
This query is actually an already written query in a project.
and i was given to modify it.
will update shortly
Having look at the link given by you.
Thx on the 50th November part. I agree with it.
This query is actually an already written query in a project.
and i was given to modify it.
will update shortly
Open in new window