Hello

I am trying to create a new container using JOIN

((([ICASEN]
INNER JOIN [ICASCST]
ON [ICASEN].[ASSMENSEQ] = [ICASCST].[ASSMENSEQ])
INNER JOIN [ICUNIT]
ON [ICASEN].[ITEMNO] = [ICUNIT].[ITEMNO] AND [ICASEN].[UNIT] = [ICUNIT].[UNIT])
INNER JOIN (SELECT [ICASENO].[ASSMENSEQ], VALUE AS MACHINEID FROM ICASENO WHERE OPTFIELD = 'MACHINEID') AS P1

ON P1.[ASSMENSEQ] = [ICASEN].[ASSMENSEQ])

When I execute the above I get a all records as expected.

After the applying the JOIN to the container. When I press the sample data, I get the sample. But, when I press the test button I get the following error.

[MICROSOFT][ODBC SQL Server Driver][SQL Server] incorrect syntax near the keyword 'AND'

Can any one help? Pleeease

Views: 111

Reply to This

Replies to This Discussion

Hello Again,

An update on above. After further investigations and trials; I found that the problem is with the WHERE statement. If I remove it the error disappears.

Is there something wrong in the SYNTAX?

Hi,

Try this.

((([ICASEN]
INNER JOIN [ICASCST]
ON [ICASEN].[ASSMENSEQ] = [ICASCST].[ASSMENSEQ])
INNER JOIN [ICUNIT]
ON [ICASEN].[ITEMNO] = [ICUNIT].[ITEMNO] AND [ICASEN].[UNIT] = [ICUNIT].[UNIT])
INNER JOIN ICASENO as P1 on
P1.ASSMENSEQ=[ICASEN].[ASSMENSEQ] AND P1.OPTFIELD='MACHINEID')

Thank you for your help. Using SQL profiler, I noticed that the Container Module is adding AND to the end of the query.

I removed the outermost brackets and it worked,

I appreciate your support.

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service