Reverse Engineering of an SQL Server query errors.

Startbeitrag von Jeff Graham am 11.12.2015 18:52

Here is a question for SQL experts in this group. After much googling, I came up with a SQL Server query that gives the data I need. I have to sum multiple rows in one table for only the most recent posting date.

So I tried to Reverse Engineer the Query and get the following: https://www.dropbox.com/s/gnoxhuon38pbq0m/SQL_InvValue.png?dl=0

I need to add selection parameters so I created another query that uses this query and a manually entered join linking on ID. However, that does not work so far. It runs the internal query and then does not do the join with or without parameters specified so the results window just goes away.

So the questions are:
How can I redo the SQL code so that it will reverse engineer?
Or how can I add parameters to the SQL code for other fields in the PART table?
Or how can I do the equivalent of the MAX(PostingDate) to get the most recent Inventory_Balance entries for a Part_ID in the query editor and then sum the fields so I end up with only one row per Part_ID?

Any suggestions would be greatly appreciated.

Antworten:

In some situations, normally with several sub-selects or specific db sintax or commands you can't use the query editor.
In this situations you have to construct your select in one string and run it using HExecuteSQLQuery if you use some kind of specific db sintax or commands you must use the hQueryWithoutCorrection constant.

von Paulo Oliveira - am 11.12.2015 19:13
Hi Jeff,

the helpfile noted at Sub-query:

Limits:
•The ORDER BY statement is not allowed in the sub-queries.
•The sub-queries cannot be created in the query editor.

So I suppose that's also the problem with reverse enginering ...

von Stefan Bentvelsen - am 12.12.2015 11:43
Hi Paulo,

It appears you are absolutely correct. I have to build my WHERE clause in the string instead of using parameters, but otherwise it works well with HExecuteSQLQuery.

I tried using my SQL Code query in another query and added parameters for a WHERE clause, but I have to specify a value and add single quotes around strings values.

Thanks for the help!

von Jeff Graham - am 12.12.2015 14:52
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.