Solved

MYSQL query

Posted on 2013-11-05
11
330 Views
Last Modified: 2013-11-21
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
0
Comment
Question by:Rohit Bajaj
  • 6
  • 5
11 Comments
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39623887
What do you intend to do with the rownum - you seem to be trying to select it you're not doing anything with it. Assuming you want it to be a row counter and output to the file as well, you'll also need to increment it. Have a look at this:

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 < CURDATE()+30 AND ESD.search_type IN ('O','R');

Open in new window

0
 

Author Comment

by:Rohit Bajaj
ID: 39623944
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
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39623984
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:

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');

Open in new window

If you need to add 1 month, then use INTERVAL 1 MONTH instead
0
 

Author Comment

by:Rohit Bajaj
ID: 39624022
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
0
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39624066
Yeah, the Syntax checker balks, but only the INTO OUTFILE bit - not on the WHERE clause - stilll, it runs fine on my system :)

Remove the INTO line just to check your query is returning what you need. Also, there's no real need to alias your ESD table:

SELECT search_type, @rownum:=@rownum + 1 row_number
FROM extreme_search_data, (SELECT @rownum:=0) r
WHERE dep_date < DATE_ADD(CURDATE(), INTERVAL 1 MONTH)
AND search_type IN ('O','R');

Open in new window

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Rohit Bajaj
ID: 39658522
HI,
The following query works :

SELECT CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT
      (if(@rownum=0,'<items>',''),CONCAT('<item>','<sku>')),CONCAT(p.id,'</sku>')),
      CONCAT('<departure_city>',p.origin)),CONCAT('</departure_city>','<destination_city>')),
      CONCAT(p.destination,'</destination_city>')),CONCAT('<travel_start_date>',p.dep_date)),
      CONCAT('T00:00:00+05:30</travel_start_date>','<travel_end_date>')),CONCAT(p.arrival_date,'T00:00:00+05:30</travel_end_date>')),
      CONCAT('<price>',p.price)),
      CONCAT('</price>','<flight_length>')),
      CONCAT(@rownum:=@rownum+1,'</flight_length>')),
      CONCAT('</item>',if(@rownum=(select count(*) from extreme_search_data),'</items>','')))
      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 ','
0
 

Author Comment

by:Rohit Bajaj
ID: 39661560
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
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39659029
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.
0
 

Author Comment

by:Rohit Bajaj
ID: 39661559
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.
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39661918
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-exchange.com/customer/portal/articles/481419 - and then explain the Grade C

Thanks
0
 

Author Comment

by:Rohit Bajaj
ID: 39665052
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now