Query Generator -- Does It Work?

[#1]

Hi:

I have been seeing some really strange things, in trying to learn the Query Generator.

First, the results change simply whenever you select more fields. It's as if the system is interpreting the adding of fields as a change of the table joins.

Secondly, the Generator does not display "LEFT OUTER JOIN" or even "INNER JOIN" when you add tables. For someone who comes from a Crystal and SQL background, that is very weird.

Does this tool work exactly like a reporting tool should?

It seems like it's only good for ad-hoc reporting -- one table at a time.

Thanks!

John

[LoHa]

Hi John,

it is how yo said. The generator only knows "inner joins". If you want to change it you have to write it by hand.

A small function makes it a little bit easier to get the joins with the right primary keys.

If you add a tbale at first and then have a look in the table fields you can find some field in bold.

When you know "grab" the with a left click and hold it pull it to left (table-column) and drop it there.

You will receive a join then.

regards Lothar

[Johan_Hakkesteegt]

Hi John,

"Does this tool work exactly like a reporting tool should? It seems like it's only good for ad-hoc reporting -- one table at a time."

No. The query generator generally understands all MS sql or HANA sql. However there is some parsing involved, so you can use [%0] parameters, and there is the /* select * from OITM x */ syntax, to allow the combination of @parameters and [%0]. If I am not mistaken, it will also deny the use of INSERT, UPDATE, and DELETE. Personally I have successfully run even rather complex queries without any problems.

The generator is a tool to help build queries, by offering descriptions to tables and fields, and automatically creating INN JOINs on the correct fields, when there is a known relationship, which is great when you are new to sql. It is however somewhat simpleminded, and will only offer INNER JOINs for example. When you have prior skills in sql, you may want to use other query parsers like MS SQL Server Management Studio, to create queries, which you can then copy-paste into B1.

When a query returns less results than expected, it can be due to NULL values in on of the matching fields. For example:

FROM OITM i
LEFT OUTER JOIN [@TABLE] t ON i.U_udf = t.Code

if i.U_udf contains NULL values, the join may return more or less results than expected. This problem can be caught with ISNULL / IFNULL.

"...the results change simply whenever you select more fields. It's as if the system is interpreting the adding of fields as a change of the table joins."

Please provide an example, because as you describe the issue here (or as I understand your description), you do not get more or less records, by adding or deleting fields in the SELECT clause.

Regards,

Johan