Solved

JOIN Issues in SQL

Posted on 2007-12-05
23
1,319 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
[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
  • 11
  • 6
  • 3
  • +3
23 Comments
 
LVL 17

Expert Comment

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

Author Comment

by:Kudzullc
ID: 20413088
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
ID: 20413113
try getting rid of all those extra spaces
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 34

Assisted Solution

by:Sancler
Sancler earned 50 total points
ID: 20413337
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
ID: 20413429
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
ID: 20413636
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
ID: 20413638
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
ID: 20413657
try using *= or += instead of left outer join
0
 
LVL 35

Expert Comment

by:YZlat
ID: 20413664
0
 
LVL 3

Author Comment

by:Kudzullc
ID: 20413947
>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
ID: 20414212
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
 
LVL 18

Assisted Solution

by:jcoehoorn
jcoehoorn earned 340 total points
ID: 20414352
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
ID: 20415255
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
ID: 20415264
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
ID: 20415325
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
ID: 20415379
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
ID: 20420650
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
ID: 20420722
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
ID: 20420751
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
ID: 20421025
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
ID: 20421102
table names and file names
meant to say
table names as file names
0
 
LVL 18

Accepted Solution

by:
jcoehoorn earned 340 total points
ID: 20421121
> 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
ID: 20427288
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 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