When I design a query in SQL I can add the same table twice and when I do SQL aliases the second copy of the table.
When I attempt to replicate that query in Sage Intelligence it chokes and spits out unbound exception errors.
Can someone help me understand the syntax needed to achieve this in Sage Intelligence?
I can preview and sample the following but can't Check/Test the expressions without receiving errors:
dbo.ICITEM AS ICITEM INNER JOIN
dbo.ICBOMH AS ICBOMH ON ICITEM.ITEMNO = ICBOMH.ITEMNO INNER JOIN
dbo.ICBOMD AS ICBOMD ON ICBOMH.ITEMNO = ICBOMD.ITEMNO AND ICBOMH.BOMNO = ICBOMD.BOMNO INNER JOIN
dbo.ICITMV AS ICITMV ON ICBOMD.COMPONENT = ICITMV.ITEMNO INNER JOIN
dbo.ICILOC AS ICILOC ON ICBOMD.COMPONENT = ICILOC.ITEMNO INNER JOIN
dbo.ICITEM AS ICITEM_1 ON ICBOMD.COMPONENT = ICITEM_1.ITEMNO
Sage Intelligence is using the standard ODBC SQL driver in its connector module, so the syntax you use should be the same. If the SQL code u added above is what u have in your container join, and it samples fine then that indicates your join is working correctly and your problems arise from one of your expressions.
That being said, if I was in your position, i would create a report of this broken container, then switch output mode in the report manager (right click on home> switch output mode> SQL Query). Take the SQL query generated by my report and run it in regular SQL server, if it fails, get it working in SQL server, then replicate the fixes you made in the container.
This may sound like the same thing you just did, however, the report manager constructs a full SQL query from the container join and expressions, report manager filters etc. Doing what i mentioned above may help u isolate the problem a bit quicker.
Thank you for your assistance Nigel.
I switched to SQL Debug Mode. When the SQL Debugger appears I select 'Test SQL' and receive 'SQL Executed OK'
When I select 'Continue' I receive this error:
"Item cannot be found in the collection corresponding to the requested name or ordinal'
The part I find quite weird is that the report source container contains many more fields than are in the data container as configured in the connector. How would the additional fields become added to the report container when they are not in the connector's container. When I sample the data in the connector I receive exactly the fields that are configured, nothing more.
Just to check, I closed the report manager the opened it again and ran the report.
The error that is produced states (in summary):
"The specified display field(s) ... do not exist in the data container"
hmmm, I think that error basically means there is a mismatch between the columns in your report manager report and the source container. I would try copying the source container, deleting all the expressions, re-adding them, then add them to a report in the report manager. then try to run that out. If that doesn't work maybe send me the report I'l try to get it to run on the SAMLTD demo company on my end.
Thank you Nigel,
When I copy the container it adds every field from every table even though the query only contains specific fields. I believe this is where the real issue lies.
Now, there is no easy way to delete the expressions. I need to right click on each individually and click delete: that's an absolutely awful user experience.
Now I am in the process of doing this and am looking for a needle to poke myself in the eye with so I have some joy from this experience.
The must be a better way than right click, delete, OK then repeat ...
Especially when Microsoft in all it's infinite wisdom refocuses the mouse on the pop-up window so I need to refocus it on the next expression.
After all of that, I receive exactly the same error
I have attached the report file for your reference
Give this a try, i see you had some of your expressions aliased in your container sql query, but in the expressions on the container itself u were using the original name and not the aliased name. Also the name of the expression is what gets used in the excel data dump process, so your aliased name must be the same as your expression name and expression source name.( for sql query container) ... Give the attached a try, ( i got no data when i tried running it)
Thank you Nigel
I imported the file you attached but that also failed.
I have however figured out the issue. What I have found is that Sage Intelligence does not like the field aliased in the query. It is much happier when the field is aliased in the container. Now that I have done that the report runs through to the end.
Thank you for pointing me in the right direction.
awesome, great news.