JOIN Issues in SQL

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!
LVL 3
KudzullcAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Joel CoehoornConnect With a Mentor Director of Information TechnologyCommented:
> 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
 
ZeonFlashCommented:
...what problem are you encountering?  Error in the syntax?  Incorrect result set?
0
 
KudzullcAuthor Commented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
YZlatCommented:
try getting rid of all those extra spaces
0
 
SanclerConnect With a Mentor Commented:
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
 
Joel CoehoornConnect With a Mentor Director of Information TechnologyCommented:
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
 
YZlatConnect With a Mentor Commented:
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
 
ShogunWadeConnect With a Mentor Commented:
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
 
YZlatCommented:
try using *= or += instead of left outer join
0
 
YZlatCommented:
0
 
KudzullcAuthor Commented:
>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
 
KudzullcAuthor Commented:
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
 
Joel CoehoornConnect With a Mentor Director of Information TechnologyCommented:
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
 
KudzullcAuthor Commented:
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
 
KudzullcAuthor Commented:
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
 
KudzullcAuthor Commented:
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
 
KudzullcAuthor Commented:
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
 
KudzullcAuthor Commented:
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
 
YZlatCommented:
there are no joins specified though...

Back to my original question, what is the source database?
0
 
YZlatConnect With a Mentor Commented:
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
 
KudzullcAuthor Commented:
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
 
KudzullcAuthor Commented:
table names and file names
meant to say
table names as file names
0
 
KudzullcAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.