Help creating a SQL Join

Replies to This Discussion

Permalink Reply by Nigel Naicker on February 21, 2012 at 10:35am

Hi ,

Based on the information provided this should solve your problem.

Replace the below column names with your table column names and should be good to go.

 

Inner Join AR_InvoiceHistoryHeader ON
Substring(IM_TransactionHistory.Invoice,1,7) = AR_InvoiceHistoryHeader.InvoiceNumber and

Substring(IM_TransactionHistory.Invoice,9,11) = AR_InvoiceHistoryHeader.InvoiceType

• ▶ Reply

Permalink Reply by Doug Walker on February 21, 2012 at 10:36pm

Nigel - Thank you for responding to my question.

I took your code and replaced the field names and it looks like this:

Inner Join AR_InvoiceHistoryHeader

ON Substring(IM_ItemTransactionHistory.EntryNo,1,7) = AR_InvoiceHistoryHeader.InvoiceNo and

Substring(IM_ItemTransactionHistory.EntryNo,9,2) = AR_InvoiceHistoryHeader.InvoiceType

I Executed it and received the following error:

error [37000][providex][odbc driver]Expected lexical element not found: <identifier>

To make this easier I do not need to Substring out the Invoice Type as the existing field (IM_ItemTransactionHistory.InvoiceType) will contain the type.

I created a Graphical Join linking the IM_ItemTransactionHistory to the AR_InvoiceHistoryHeader using the EntryNo and InvoiceType fields just to get the SQL Syntax.

It gave me the following:

(IM_ItemTransactionHistory
INNER JOIN AR_InvoiceHistoryHeader
  ON IM_ItemTransactionHistory.EntryNo = AR_InvoiceHistoryHeader.InvoiceNo  AND IM_ItemTransactionHistory.InvoiceType = AR_InvoiceHistoryHeader.InvoiceType)

I took the above and added the Substring code as follows:

(IM_ItemTransactionHistory
INNER JOIN AR_InvoiceHistoryHeader
  ON Substring(IM_ItemTransactionHistory.EntryNo,1,7) = AR_InvoiceHistoryHeader.InvoiceNo  AND IM_ItemTransactionHistory.InvoiceType = AR_InvoiceHistoryHeader.InvoiceType)

this also gave me the ODBC Error listed above.

What is causing the error?

Thanks

Doug

• ▶ Reply

Permalink Reply by Nigel Naicker on March 1, 2012 at 5:04pm

Apologies for the late reply

what kind of datatype is this column ?  'AR_InvoiceHistoryHeader.InvoiceNo ' .

Is it the same as IM_ItemTransactionHistory.EntryNo?

• ▶ Reply

Permalink Reply by Doug Walker on March 1, 2012 at 5:33pm

Hey Nigel - They are both 'String' type fields.

• ▶ Reply

Permalink Reply by Nigel Naicker on March 2, 2012 at 3:14pm

hmmm, can u send the value for each of the fields u are trying to join , so i can see if I can get the same error as u without needing ur database.

• ▶ Reply

Permalink Reply by Doug Walker on March 2, 2012 at 7:08pm

Nigel- Here is the output of the 2 files that I am using.

I need to link the records from the IM_TransactionHistory files that have the TransactionCode = "SO".

Attachments:

• SQLData.xlsx, 20 KB

• ▶ Reply

Permalink Reply by Nigel Naicker on March 7, 2012 at 10:03am

Hi Doug

I was unable to replicate the error using hardcoded values. Would you be able to hardcode some values in and see whether u still get the error message? If u do replicate it, then could u send me that sql statement to debug.

 

Thanks

• ▶ Reply

Permalink Reply by Doug Walker on March 8, 2012 at 2:41am

Nigel - Not sure of what you mean by "Hardcode" some values in. Can you give me an example?

• ▶ Reply

Permalink Reply by Nigel Naicker on March 8, 2012 at 9:40am

Hardcoding is when u replace a column name in your sql query with a actual value.

For example.

Select 'a'

 

• ▶ Reply

Permalink Reply by Doug Walker on March 9, 2012 at 7:10pm

Nigel - Still not sure how to code the SQL to do a hard select in the container. How did you do it? Did you do it with the SMI using the MAS data? Can you give me the syntax for me to test it on my system?

Thanks

Doug

• ▶ Reply

Permalink Reply by Nigel Naicker on March 12, 2012 at 4:54pm

select

* from

(

select '0000014-IN' as Invoice) as IM_TransactionHistory

Inner

Join (select '0000014' as InvoiceNumber, 'IN' as InvoiceType) as AR_InvoiceHistoryHeader ON

Substring

(IM_TransactionHistory.Invoice,1,7) = AR_InvoiceHistoryHeader.InvoiceNumber and

Substring

(IM_TransactionHistory.Invoice,9,11) = AR_InvoiceHistoryHeader.InvoiceType

Permalink Reply by Nigel Naicker on March 12, 2012 at 4:55pm

I created this dummy query in sql server . it ran ok.

 

• ‹ Previous
• 1
• 2
• Next ›
• Page