• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1382
  • Last Modified:

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!
0
Kudzullc
Asked:
Kudzullc
  • 11
  • 6
  • 3
  • +3
7 Solutions
 
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
 
YZlatCommented:
try getting rid of all those extra spaces
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SanclerCommented:
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 CoehoornDirector 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
 
YZlatCommented:
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
 
ShogunWadeCommented:
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 CoehoornDirector 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
 
YZlatCommented:
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
 
Joel CoehoornDirector 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
 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 11
  • 6
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now