Solved

JOIN Issues in SQL

Posted on 2007-12-05
23
1,275 Views
Last Modified: 2008-02-01
Hello Experts!
I am having an issue with a SQL statement and am sure this has a simple solution.  I have tried it all sort of ways but to no end.

Here is the SQL:

Dim cmdOpenMAS As New Odbc.OdbcCommand("
SELECT    
 ARN_InvHistoryHeader.SOCustomerPONumber,
 ARN_InvHistoryHeader.InvoiceNumber,
 ARN_InvHistoryHeader.InvoiceDate,
 ARN_InvHistoryHeader.CustomerNumber,
 ARN_InvHistoryHeader.SOSalesOrderNumber,
 ARN_InvHistoryHeader.SOBillToName,
 ARN_InvHistoryHeader.SOShipViaRate,
 ARN_InvHistoryHeader.SOSlspersonCode,
 ARN_InvHistoryHeader.SOOrderDate,
 ARO_InvHistoryDetail.SODescription,
 ARO_InvHistoryDetail.LineType,
 ARO_InvHistoryDetail.SOItemNumber,
 ARO_InvHistoryDetail.SOQtyOrdered,
 ARO_InvHistoryDetail.SOQtyShipped,
 ARO_InvHistoryDetail.SOUnitPrice,
 ARO_InvHistoryDetail.SOExtChargeAmount,
 IM1_InventoryMasterfile.ProductLine

FROM

  ARN_InvHistoryHeader

LEFT OUTER JOIN  ARO_InvHistoryDetail
      ON  ARN_InvHistoryHeader.InvoiceNumber = ARO_InvHistoryDetail.InvoiceNumber  

INNER JOIN IM1_InventoryMasterfile
      ON  IM1_InventoryMasterfile.ItemNumber = ARO_InvHistoryDetail.SOItemNumber

WHERE    
   ARO_InvHistoryDetail.LineType = '4' AND  ARN_InvHistoryHeader.InvoiceDate >= {d '" & Format(startDate, "yyyy-MM-dd") & "'} AND ARN_InvHistoryHeader.InvoiceDate <= {d '" & Format(finishDate, "yyyy-MM-dd") & "'"

, cnOdbc)

This command is in VB.NET.  I have separated the statement for your viewing pleasure.  

Any ideas?

Thanks for your time!
0
Comment
Question by:Kudzullc
  • 11
  • 6
  • 3
  • +3
23 Comments
 
LVL 17

Expert Comment

by:ZeonFlash
Comment Utility
...what problem are you encountering?  Error in the syntax?  Incorrect result set?
0
 
LVL 3

Author Comment

by:Kudzullc
Comment Utility
Sorry!

Forgot the most important part:

System.Data.OdbcException:ERROR [3700][ProvideX][ODBC Driver] Unexpected extra token: LEFT

This is the error I receive.  Thanks for the quick reply

Lucas
0
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
try getting rid of all those extra spaces
0
 
LVL 34

Assisted Solution

by:Sancler
Sancler earned 50 total points
Comment Utility
The closing curly bracket is missing in this bit

....
MM-dd") & "'} AND ARN_InvHistoryHeader.InvoiceDate <= {d '" & Format(finishDate, "yyyy-MM-dd") & "'"

Not sure if that's all.  But that's wrong.

Roger
0
 
LVL 18

Assisted Solution

by:jcoehoorn
jcoehoorn earned 340 total points
Comment Utility
The SQL logic is likely wrong, as well.

You use a LEFT JOIN to allow records from the joined table to be NULL.  After that you INNER JOIN another table based on the joined table.  This invalidates the reason for choosing a LEFT JOIN the first time.

0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 70 total points
Comment Utility
most importantly is what database are you connecting to?

LEFT OUTER JOIN works for SQL Server, for Oracle or Informix Lfet join is done differently
0
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 40 total points
Comment Utility
As JC mentions,  the query logic is wrong   X LEFT JOIN y INNER JOIN z is the same as x INNER JOIN y INNER JOIN z..

What odbc driver are you using? (version of mdac ,etc)   Some drivers will object to the LEFT then INNER join syntax.
0
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
try using *= or += instead of left outer join
0
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
0
 
LVL 3

Author Comment

by:Kudzullc
Comment Utility
>The closing curly bracket is missing in this bit
Yes, it was missing!  Good eye.  
>Database type
SQL 2000

I am trying a few things from the comments.  Thanks for the responses.
0
 
LVL 3

Author Comment

by:Kudzullc
Comment Utility
Here is an adjusted SQL statement from above with connection string.

Dim cnOdbc As New Odbc.OdbcConnection("DSN=SOTAMAS90;UID=XXXXXX;PWD=XXXXXX;Company=XXXXXXX;")

SELECT  ARN_InvHistoryHeader.SOCustomerPONumber, ARN_InvHistoryHeader.InvoiceNumber, ARN_InvHistoryHeader.InvoiceDate, ARN_InvHistoryHeader.CustomerNumber, ARN_InvHistoryHeader.SOSalesOrderNumber, ARN_InvHistoryHeader.SOBillToName, ARN_InvHistoryHeader.SOShipViaRate, ARN_InvHistoryHeader.SOSlspersonCode, ARN_InvHistoryHeader.SOOrderDate, ARO_InvHistoryDetail.SODescription, ARO_InvHistoryDetail.LineType, ARO_InvHistoryDetail.SOItemNumber, ARO_InvHistoryDetail.SOQtyOrdered, ARO_InvHistoryDetail.SOQtyShipped, ARO_InvHistoryDetail.SOUnitPrice, ARO_InvHistoryDetail.SOExtChargeAmount, IM1_InventoryMasterfile.ProductLine FROM ARO_InvHistoryHeader INNER JOIN  ARO_InvHistoryDetail ON  ARN_InvHistoryHeader.InvoiceNumber = ARO_InvHistoryDetail.InvoiceNumber  INNER JOIN IM1_InventoryMasterfile ON  IM1_InventoryMasterfile.ItemNumber = ARO_InvHistoryDetail.SOItemNumber         WHERE   ARO_InvHistoryDetail.LineType = '4' AND  ARN_InvHistoryHeader.InvoiceDate >= {d '2007-11-05'} AND ARN_InvHistoryHeader.InvoiceDate <= {d '2007-12-05'}

This SQL 2000 database has been setup through the ODBC Data Source Manager on my production computer.  This query is a combination of two separate queries in which was created by a Crystal Report Generator.  I took the SQL and called myself combining it.  The object of this query is to attach the ProductLine record to the original report which runs fine.  

Original SQL:
Main Pull
SELECT    ARN_InvHistoryHeader.SOCustomerPONumber, ARN_InvHistoryHeader.InvoiceNumber, ARN_InvHistoryHeader.InvoiceDate, ARN_InvHistoryHeader.CustomerNumber, ARN_InvHistoryHeader.SOSalesOrderNumber, ARN_InvHistoryHeader.SOBillToName, ARN_InvHistoryHeader.SOShipViaRate, ARN_InvHistoryHeader.SOSlspersonCode, ARN_InvHistoryHeader.SOOrderDate,   ARO_InvHistoryDetail.SODescription,ARO_InvHistoryDetail.LineType, ARO_InvHistoryDetail.SOItemNumber, ARO_InvHistoryDetail.SOQtyOrdered, ARO_InvHistoryDetail.SOQtyShipped, ARO_InvHistoryDetail.SOUnitPrice, ARO_InvHistoryDetail.SOExtChargeAmount FROM  { oj ARN_InvHistoryHeader ARN_InvHistoryHeader LEFT OUTER JOIN ARO_InvHistoryDetail ARO_InvHistoryDetail ON        ARN_InvHistoryHeader.InvoiceNumber = ARO_InvHistoryDetail.InvoiceNumber} WHERE    ARO_InvHistoryDetail.LineType = '4' AND  ARN_InvHistoryHeader.InvoiceDate >= {d '2007-11-05'} AND ARN_InvHistoryHeader.InvoiceDate <= {d '2007-12-05'}

SQL statement to retrieve Product Line:
SELECT    IM1_InventoryMasterfile.ItemNumber, IM1_InventoryMasterfile.ProductLine FROM   IM1_InventoryMasterfile     WHERE    IM1_InventoryMasterfile.ItemNumber = ?

I tried to use the Product Line query as subquery but could not resolve.

Hope this helps!

Thanks!

Lucas
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Assisted Solution

by:jcoehoorn
jcoehoorn earned 340 total points
Comment Utility
What error are you getting now, or what is it doing incorrectly?

You're using SQL Server and .Net- why not use the .Net Native provider in system.net.sqlclient instead of system.net.odbc?  The only code changes are simple change to your connection string and a replace all to use sqlclient.sql... instead of odbc.odbc....   For this you'll get more specific errors, potentially better performance, and no longer need to install an odbc datasource.  
0
 
LVL 3

Author Comment

by:Kudzullc
Comment Utility
jcoehoorn,

I changed the connection string and command construct to find a new error.  I need to make a correction to the original problem.  I am using a ProvideX Driver database used for a MAS200 accounting system as the source.  The information pulled from the source database is imported into a SQL 200 database.  This is why I had to use the ODBC environment.

I apologize for the confusion!

Also, I am getting the same error with the SQL statement in my last post.

Thanks again for all your help on this.

Lucas
0
 
LVL 3

Author Comment

by:Kudzullc
Comment Utility
To clarify once more, when I spoke of the SQL database I was mistakenly thinking about the destination database and not the source.

Sorry!

:/
0
 
LVL 3

Author Comment

by:Kudzullc
Comment Utility
RunTime SQL using second INNER JOIN as a Subquery:

SELECT  ARN_InvHistoryHeader.SOCustomerPONumber, ARN_InvHistoryHeader.InvoiceNumber, ARN_InvHistoryHeader.InvoiceDate, ARN_InvHistoryHeader.CustomerNumber, ARN_InvHistoryHeader.SOSalesOrderNumber, ARN_InvHistoryHeader.SOBillToName, ARN_InvHistoryHeader.SOShipViaRate, ARN_InvHistoryHeader.SOSlspersonCode, ARN_InvHistoryHeader.SOOrderDate, ARO_InvHistoryDetail.SODescription, ARO_InvHistoryDetail.LineType, ARO_InvHistoryDetail.SOItemNumber, ARO_InvHistoryDetail.SOQtyOrdered, ARO_InvHistoryDetail.SOQtyShipped, ARO_InvHistoryDetail.SOUnitPrice, ARO_InvHistoryDetail.SOExtChargeAmount,(SELECT IM1_InventoryMasterfile.ProductLine FROM IM1_InventoryMasterfile WHERE IM1_InventoryMasterfile.ItemNumber = ARO_InvHistoryDetail.SOItemNumber) as ProdLine FROM ARO_InvHistoryHeader INNER JOIN  ARO_InvHistoryDetail ON  ARN_InvHistoryHeader.InvoiceNumber = ARO_InvHistoryDetail.InvoiceNumber    WHERE   ARO_InvHistoryDetail.LineType = '4' AND  ARN_InvHistoryHeader.InvoiceDate >= {d '2007-11-05'} AND ARN_InvHistoryHeader.InvoiceDate <= {d '2007-12-05'}

Error:
System.Data.Odbc.OdbcException: ERROR [3700][ProvideX][ODBC Driver] Expected lexical element not found: <identifier>
then stack trace...

0
 
LVL 3

Author Comment

by:Kudzullc
Comment Utility
Changed:
FROM ARO_InvHistoryHeader INNER JOIN  ARO_InvHistoryDetail ON  ARN_InvHistoryHeader.InvoiceNumber = ARO_InvHistoryDetail.InvoiceNumber    WHERE   ARO_InvHistoryDetail.LineType = '4' AND  ARN_InvHistoryHeader.InvoiceDate >= {d '2007-11-05'} AND ARN_InvHistoryHeader.InvoiceDate <= {d '2007-12-05'}

To:
FROM ARO_InvHistoryHeader INNER JOIN  ARN_InvHistoryDetail ON  ARN_InvHistoryHeader.InvoiceNumber = ARO_InvHistoryDetail.InvoiceNumber    WHERE   ARO_InvHistoryDetail.LineType = '4' AND  ARN_InvHistoryHeader.InvoiceDate >= {d '2007-11-05'} AND ARN_InvHistoryHeader.InvoiceDate <= {d '2007-12-05'}


Same Error.


0
 
LVL 3

Author Comment

by:Kudzullc
Comment Utility
So, after thinking about this all night I decided this morning to break it down and every Query ran fine so I combined them into a simple mutliple table Select.  As I open the connection, I do not receive an error which lets me know the syntax is correct but the query is now taking longer than before I added the IM1_InventoryMasterFile table.  This is a definite issue, I can not hog all the resources during the day which limits my query time for debugging purposes.

I will be able to complete the query during lunch.

Here is the New Query:

SELECT     ARN_InvHistoryHeader.SOCustomerPONumber, ARN_InvHistoryHeader.InvoiceNumber, ARN_InvHistoryHeader.InvoiceDate, ARN_InvHistoryHeader.CustomerNumber, ARN_InvHistoryHeader.SOSalesOrderNumber, ARN_InvHistoryHeader.SOBillToName, ARN_InvHistoryHeader.SOShipViaRate, ARN_InvHistoryHeader.SOSlspersonCode, ARN_InvHistoryHeader.SOOrderDate, ARO_InvHistoryDetail.SODescription, ARO_InvHistoryDetail.LineType, ARO_InvHistoryDetail.SOItemNumber, ARO_InvHistoryDetail.SOQtyOrdered, ARO_InvHistoryDetail.SOQtyShipped, ARO_InvHistoryDetail.SOUnitPrice, ARO_InvHistoryDetail.SOExtChargeAmount, IM1_InventoryMasterfile.ProductLine FROM ARN_InvHistoryHeader, ARO_InvHistoryDetail, IM1_InventoryMasterfile WHERE       ARO_InvHistoryDetail.LineType = '4' AND  ARN_InvHistoryHeader.InvoiceDate >= {d '2007-11-06'} AND ARN_InvHistoryHeader.InvoiceDate <= {d '2007-12-06'} AND  IM1_InventoryMasterfile.ItemNumber = ARO_InvHistoryDetail.SOItemNumber

any first impressions?  seems simple enough.

Syntax looks fine and it runs.  I just dont know when the records would be pulled.  Will know more in a few hours.

Thanks!

Lucas
0
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
there are no joins specified though...

Back to my original question, what is the source database?
0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 70 total points
Comment Utility
try changing this

SELECT     ARN_InvHistoryHeader.SOCustomerPONumber, ARN_InvHistoryHeader.InvoiceNumber, ARN_InvHistoryHeader.InvoiceDate, ARN_InvHistoryHeader.CustomerNumber, ARN_InvHistoryHeader.SOSalesOrderNumber, ARN_InvHistoryHeader.SOBillToName, ARN_InvHistoryHeader.SOShipViaRate, ARN_InvHistoryHeader.SOSlspersonCode, ARN_InvHistoryHeader.SOOrderDate, ARO_InvHistoryDetail.SODescription, ARO_InvHistoryDetail.LineType, ARO_InvHistoryDetail.SOItemNumber, ARO_InvHistoryDetail.SOQtyOrdered, ARO_InvHistoryDetail.SOQtyShipped, ARO_InvHistoryDetail.SOUnitPrice, ARO_InvHistoryDetail.SOExtChargeAmount, IM1_InventoryMasterfile.ProductLine FROM ARN_InvHistoryHeader, ARO_InvHistoryDetail, IM1_InventoryMasterfile WHERE       ARO_InvHistoryDetail.LineType = '4' AND  ARN_InvHistoryHeader.InvoiceDate >= {d '2007-11-06'} AND ARN_InvHistoryHeader.InvoiceDate <= {d '2007-12-06'} AND  IM1_InventoryMasterfile.ItemNumber = ARO_InvHistoryDetail.SOItemNumber

to

SELECT     ARN_InvHistoryHeader.SOCustomerPONumber, ARN_InvHistoryHeader.InvoiceNumber, ARN_InvHistoryHeader.InvoiceDate, ARN_InvHistoryHeader.CustomerNumber, ARN_InvHistoryHeader.SOSalesOrderNumber, ARN_InvHistoryHeader.SOBillToName, ARN_InvHistoryHeader.SOShipViaRate, ARN_InvHistoryHeader.SOSlspersonCode, ARN_InvHistoryHeader.SOOrderDate, ARO_InvHistoryDetail.SODescription, ARO_InvHistoryDetail.LineType, ARO_InvHistoryDetail.SOItemNumber, ARO_InvHistoryDetail.SOQtyOrdered, ARO_InvHistoryDetail.SOQtyShipped, ARO_InvHistoryDetail.SOUnitPrice, ARO_InvHistoryDetail.SOExtChargeAmount, IM1_InventoryMasterfile.ProductLine
FROM ARN_InvHistoryHeader, ARO_InvHistoryDetail, IM1_InventoryMasterfile
WHERE ARN_InvHistoryHeader.InvoiceNumber = ARO_InvHistoryDetail.InvoiceNumber
AND IM1_InventoryMasterfile.ItemNumber = ARO_InvHistoryDetail.SOItemNumber
AND ARO_InvHistoryDetail.LineType = '4' AND  ARN_InvHistoryHeader.InvoiceDate >= {d '2007-11-06'}
AND ARN_InvHistoryHeader.InvoiceDate <= {d '2007-12-06'}
0
 
LVL 3

Author Comment

by:Kudzullc
Comment Utility
YZ,

When I ask the support team for the Software all I get is ODBC.  It is a series of folders with .soa files with table names and file names that need an ODBC Data Source installed to access.  Otherwise, the files are unaccessible.

Does this help?

I will re-arange my where clause like you commented and try again first thing this afternoon.  

Thanks!

-Lucas
0
 
LVL 3

Author Comment

by:Kudzullc
Comment Utility
table names and file names
meant to say
table names as file names
0
 
LVL 18

Accepted Solution

by:
jcoehoorn earned 340 total points
Comment Utility
> I am using a ProvideX Driver database used for a MAS200 accounting system as the source.

I'm thinking a big part of the problem here is that you're using a less common dbms.  Every dbms has it's own little dialect of SQL.  Of course, you can't just pick a different database- MAS200 is MAS200.  But in this case you might have slightly better luck looking for a MAS200 or Sage specific forum.
0
 
LVL 3

Author Comment

by:Kudzullc
Comment Utility
Thanks everyone for the effort!

After speaking with a Sage DB Manager, I now know there is not natural link between the tables I am referencing.  The IM1_InventoryMasterFile is a table from another Data Source which means my SQL will never work and makes sense on why they had it as a Sub Report using the Crystal Report Designer.

I will try to disperse points for comments leaning toward this discovery!

Thanks again for all your time!

Lucas
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 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

10 Experts available now in Live!

Get 1:1 Help Now