Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Mysql

Posted on 2013-11-06
16
Medium Priority
?
381 Views
Last Modified: 2013-11-10
Below is code I am using to get data from some mysql tables.  Below the code is and error I am getting.  Any Ideas? in the line, report date is a string (0000-00-00);


        String command =
                "SELECT "
                + "SELLERID, HEAD, DESCRIP, "
                + "ROUND(WEIGHT/HEAD) AS AVG_WGT, "
                + "WEIGHT, PRICE, "
                + "(SELECT name FROM seller "
                + "WHERE date= '" + reportDate + "' FROM sales WHERE sel=1) "
                + "AND number=trans.sellerid AND length(seller.name)>0 ) AS NAME, "
                + "trans.type AS sexcode "
                + "FROM trans "
                + "WHERE date= '" + reportDate + "' FROM sales WHERE sel=1) "
                + "ORDER BY NAME ASC sexcode DESC";


I get a syntax error near - FROM sales WHERE sel=1) AND number=trans.sellerid AND length(seller.name)>0 AS' at line  1

Does it have something to do with the reportDate string?
0
Comment
Question by:sargent240
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
  • 2
  • +2
16 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39629399
The error is within this statement:
(SELECT name FROM seller WHERE date= '" + reportDate + "' FROM sales WHERE sel=1)

Open in new window

As you see you used FROM twice. FROM seller, and then FROM sales.
It is unclear what you have tried to achieve by this. I assume it is just a cut-n-paste error.
Most likely this line should be:
(SELECT name FROM seller WHERE date= '" + reportDate + "' )

Open in new window

or
(SELECT name FROM seller WHERE date= '" + reportDate + "' AND sel=1)

Open in new window

You will see the same line further when you are retrieving data from trans.
FROM trans WHERE date= '" + reportDate + "' FROM sales WHERE sel=1

Open in new window

This is also incorrect. Please correct it accordingly.
On a side note, surround the columns that are reserved words with `
In your statement column with the name of "date" should be written in SQL as:
`date`

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39629417
refer this

[Link Deleted]

---

The terms of EE do not allow the posting of links to competing sites. Please have a read through the guidelines:

http://support.experts-exchange.com/customer/portal/articles/1162518-using-off-site-content-on-experts-exchange

Thanks for your understanding

ChrisStanyon - Topic Advisor
0
 
LVL 25

Expert Comment

by:chaau
ID: 39629422
@pratima_mcs: please be careful with the answers like this. When I posted one like this it was deleted as a "some kind of link" message. Basically, there is a rule somewhere that the answer should be a normal answer, not just a blind link with an answer from another website
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 5

Expert Comment

by:Kwoof
ID: 39629427
I agree with chaau...make sure you review all 3 lines to the "as NAME" to make sure that part is correct.  try executing just that select to make sure you get what you intended for NAME.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39629435
At a guess "FROM sales WHERE sel=1)" is a errant piece of text and both can be removed (this is an assumption). If that is true it looks as if the following would provide the seller name without needing a subquery.
SELECT    
      SELLERID
    , HEAD
    , DESCRIP
    , ROUND(WEIGHT/HEAD) AS AVG_WGT
    , WEIGHT
    , PRICE
    , seller.name
    , trans.type AS sexcode    
FROM trans  
left join seller on trans.sellerid = seller.number  
WHERE trans.`date` = '  + reportDate +    '  
ORDER BY NAME ASC, sexcode DESC

Open in new window

note there was a comma missing the order by clause.

MySQL does allow use of date as a fieldname but it is better to use `date` (or better still not to use that word as a fieldname).
MySQL permits some keywords to be used as unquoted identifiers because many people previously used them.
http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

I've used left join because this is the same behaviour as a subquery in the select clause.

I don't think you achieve much by this:
AND length(seller.name)>0

     if the length of name is > zero you get name
     if the name is NULL then length(NULL) is NULL and you get NULL as name

if you leave out this test you will get the same result I think (maybe you have "empty strings you are trying to avoid, not sure)
0
 

Author Comment

by:sargent240
ID: 39635812
Thanks PortletPaul.  What a helpful response.  I will  be back where I can work this out tomorrow and will respond.
0
 

Author Comment

by:sargent240
ID: 39636510
PortletPaul - I am trying to avoid empty strings.  I typed the command in at the mysql prompt and upon hitting the return key I simply got the > prompt with no other info from mysql.
0
 

Author Comment

by:sargent240
ID: 39636512
This is the string and the result

mysql> SELECT sellerID, head, descrip, ROUND(weight/head) as avg_wgt, weight, price, seller.name, trans.type as sexcode FROM trans left join seller on trans.sellerid = seller.number WHERE trans." + 'date' = '  + reportDate + ' ORDER BY name ASC, sexcode DESC;
    ">
0
 

Author Comment

by:sargent240
ID: 39636515
PortletPaul - I see an error in the line I posted above.  There is a double quote in the trans.+'date part of the command and I removed it and tried it again this time there was an error.

mysql> SELECT sellerID, head, descrip, ROUND(weight/head) as avg_wgt, weight, price, seller.name, trans.type as sexcode FROM trans left join seller on trans.sellerid = seller.number WHERE trans. + 'date' = '  + reportDate + ' ORDER BY name ASC, sexcode DESC;
ERROR 1064 (42000): 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 '+ 'date' = '  + reportDate + ' ORDER BY name ASC, sexcode DESC' at line 1
mysql>
0
 

Author Comment

by:sargent240
ID: 39636520
Well, here is some more stuff.  I have changed the command to read as follows.  This is the command and the error now.


mysql> SELECT sellerID, head, descrip, ROUND(weight/head) as avg_wgt, weight, price, seller.name, trans.type as sexcode FROM trans left join seller on trans.sellerid = seller.number WHERE trans.date + 'reportDate' + ' ORDER BY name ASC, sexcode DESC';
ERROR 1052 (23000): Column 'head' in field list is ambiguous
mysql>


I am attaching a file with a description of the trans table;
transTable
0
 

Author Comment

by:sargent240
ID: 39636544
Boy I hope I am not throwing everything into overload hear but I think I have the command properly assigned to a string now.  As you can see in last line of the following code I print the string so I can be sure it is what I want.  I have put the result below the string assignment for you to see.


        String command =
                "SELECT "
                + "SELLERID, HEAD, DESCRIP, "
                + "ROUND(WEIGHT/HEAD) AS AVG_WGT, "
                + "WEIGHT, PRICE, "
                + "seller.name, "
                + "trans.type AS sexcode "
                + "FROM trans "
                + "left join seller on trans.sellerid = seller.number "
                + "WHERE trans.date= '" + reportDate + "' "
                + "ORDER BY NAME ASC, sexcode DESC";
System.out.println(command);  

 SELECT SELLERID, HEAD, DESCRIP, ROUND(WEIGHT/HEAD) AS AVG_WGT, WEIGHT, PRICE, seller.name, trans.type AS sexcode FROM trans left join seller on trans.sellerid = seller.number WHERE trans.date= '2013-04-11' ORDER BY NAME ASC, sexcode DESC;

ERROR 1052 (23000): Column 'HEAD' in field list is ambiguous
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39636555
>>ERROR 1052 (23000): Column 'HEAD' in field list is ambiguous
both the tables `trans` and `seller` contain the field `head`

so, you need to specify which one you want:

select ...,`trans`.`head`

or

select ...,`seller`.`head`

use of backticks (or not) is up to you.
0
 

Author Comment

by:sargent240
ID: 39637204
Great!  This is what ended up with and it works except I can't get the date from trans.  I get an error stating there is not such a column as date.  The rs must be referring to the seller table and the date is in the trans table.  If I can get an idea at to how to get date from the trans table I'm off and running.  What do you think?  Thanks!

       
       
        String command =
                "SELECT "
                + "t.sellerID, t.HEAD, t.DESCRIP, "
                + "ROUND(t.WEIGHT/t.HEAD) AS AVG_WGT, "
                + "t.WEIGHT, t.PRICE, "
                + "s.name, "
                + "t.type AS sexcode "
                + "FROM trans AS t "
                + "LEFT JOIN seller AS s ON t.sellerid = s.number "
                + "WHERE t.date='" + reportDate + "' "
                + "ORDER BY NAME ASC, sexcode DESC";
       
        try {
            st = (Statement) con.createStatement();
            rs = st.executeQuery(command);

       rs.next();
       System.out.println(rs.getDate("date"));
0
 

Author Comment

by:sargent240
ID: 39637213
A little on the slow side but I got it.  After looking in the code in my previous post I see I did not have t.date above the FROM in the command string.  I put it in and the problem went away.  My thanks to you for hanging in there.  I learned a lot here.  again THANKS!
0
 

Author Closing Comment

by:sargent240
ID: 39637215
FIRST CLASS HELP>  THANKS!!
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39637508
You are welcome. Thanks for the grading. Cheers, Paul
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

604 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