Help creating a SQL Join

Replies to This Discussion

Permalink Reply by Doug Walker on March 12, 2012 at 6:31pm

Nigel - Thanks for following up on this.

I copied your SQL code and just changed the Invoice Number to '9999999' as I have that one in the files. I Execute the code and get "Invalid sql query - extra/missing round brackets missing somewhere"

Looking at your code I thought maybe the column names where causing the error.

The Inventory file name is IM_ItemTransactionHistory where you have IM_TransactionHistory.

The actual Invoice number in the IM_ItemTransactionHistory is contained in the EntryNo field.

The Invoice in the AR_InvoiceHistoryHeader is in InvoiceNo .

So I adjusted the code to look like this:

* from

(select '9999999-IN' as EntryNo) as IM_ItemTransactionHistory

Inner

Join (select '9999999' as InvoiceNo, 'IN' as InvoiceType) as AR_InvoiceHistoryHeader ON

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

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

I still get the "Invalid sql query - extra/missing round brackets missing somewhere" error.

Thanks

Doug

• ▶ Reply

Permalink Reply by Craig Juta on March 13, 2012 at 9:35am

Hi Nigel

I also tried to use the query that you built and it returned the same error as Doug received. I even reduced the query to a very simple query and received the same error. This is the query I used:

select * from
(select '0000014-IN' as Invoice) as IM_TransactionHistory

Why does the MAS90 database not allow a sub-query. I posted a similar question in January but have not received a response from the community yet.....http://community.alchemex.com/forum/topics/mas90-subqueries

• ▶ Reply

Permalink Reply by Nigel Naicker on March 13, 2012 at 10:01am

what engine are u guys using?

• ▶ Reply

Permalink Reply by Nigel Naicker on March 13, 2012 at 10:07am

Hi Craig, mas 90 thats provide x and it uses UQE.. UQE does not support subqueries

 

• ▶ Reply

Permalink Reply by Doug Walker on March 13, 2012 at 5:43pm

So does this mean I won't be able to make a SQL join using substrings?

What about unchecking the "Execute with Universal Query Engine" in the Container?

• ▶ Reply

Permalink Reply by Nigel Naicker on March 14, 2012 at 8:51am

u can try, but in my experience UQE enhances the functional capability of the engine.

I don't think substrings are a problem in the UQE. I won't be able to verify this for you as I have no access to a provide x enviroment unless it were in a professional capacity.

 

 

 

• ▶ Reply

Permalink Reply by Doug Walker on March 15, 2012 at 2:14am

I turned off the UQE and still get the error.

What do you mean by "professional capacity"?

• ▶ Reply

Permalink Reply by Nigel Naicker on March 15, 2012 at 9:06am

In a case where there was a problem with a standard report, this would go through our support desk and then I would be able to investigate and provide a solution.      :-)

 

A way to test whether your subtring is the problem would be  to remove it from your join, and see if it gives you an error when you execute it.

 

• ▶ Reply

Permalink Reply by Doug Walker on March 20, 2012 at 11:24pm

I did it without the Sub-strings and did not receive the error. I also didn't get any useable data.

I take it what I am doing is not considered a "Standard" report.  Do you have any suggestions as to where I maybe able to go and get help with this issue?

Thanks

Doug

• ▶ Reply

Permalink Reply by Nigel Naicker on March 22, 2012 at 7:15am

The usually process would be for you to contact a Alchemex consultant.

Apologies though, I wish I could have asssited you further Doug.

• ▶ Reply

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