Solved

Mysql

Posted on 2013-11-06
16
368 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
  • 9
  • 3
  • 2
  • +2
16 Comments
 
LVL 24

Expert Comment

by:chaau
Comment Utility
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
Comment Utility
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 24

Expert Comment

by:chaau
Comment Utility
@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
 
LVL 5

Expert Comment

by:Kwoof
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:sargent240
Comment Utility
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
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
FIRST CLASS HELP>  THANKS!!
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
You are welcome. Thanks for the grading. Cheers, Paul
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

771 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

11 Experts available now in Live!

Get 1:1 Help Now