Gupta Report Builder SQLQUERY

Discussion forum about all things Report Builder (all versions).
bryansnyder

Gupta Report Builder SQLQUERY

Post by bryansnyder » 11 Dec 2013, 22:10

I didn't see a forum section for Report Builder, so am posting this in the general section.

I use Infor Visual ERP and GRB for reports. It is setup where the primary sql query for the report is already prebuilt by Infor and then you have 1 additional query that can be added to get custom data, which is in the formula SQLQUERY.

I'm running into problems with this extended query where valid query commands don't work in the extended query. Specifically:

1) The SQLQUERY formula has to be entered with single quotes around the query. But any query that has a fixed text value also needs to be placed in quotes. For instace : SELECT owner.name FROM owner WHERE name = ‘Joe’ is a simple and perfectly valid sql query. But GRB needs the single quotes around the query in the formula window, so it becomes ‘SELECT owner.name FROM owner WHERE name = ‘Joe’‘ and GRB errors when it gets the single quote at the beginning of ‘JOE. Is there a formatting method that fixes this problem?

2) The second problem is also with SQLQUERY where it looks like when I try to do a nested query that the report input variables are not passed into the inner query. For example: SELECT QUERYA.* from (select part.id from part where id = :WO_BASE_ID ) QUERYA . The input variable :WO_BASE_ID works fine in a simple query, but not in a nested query where I get an error stating ‘symbol is undefined or unable to be referenced from current location :WO_BASE_ID’. In a standard query interface I can declare a local variable and run the query, but it looks like somehow with GRB the variable doesn’t pass to the inner query.

Anyone have ideas on these?

Thanks,
Bryan

VEspinal

Re: Gupta Report Builder SQLQUERY

Post by VEspinal » 11 Dec 2013, 23:38

Hi:

Can you please let us know the RB version that you are using?

Also, can you please provide us with a small reprocase (apt + qrp) in order to reproduce the issue?

Thanks in advance.

Regards,
Victor Espinal

bryansnyder

Re: Gupta Report Builder SQLQUERY

Post by bryansnyder » 12 Dec 2013, 01:34

Version is RB5.2

I've dug around but can't figure out what you mean by 'apt file'. I've attached a simplified qrp of the report I was working with. I stripped out everything except the basic nested query for the extended, though the full primary query is still all there. The one version that works is the non-nested version, the one that produces the error has a single nested sql select statement. Note that this is purely for an example, the nested statement obviously can be simplified and does not need to be nested. My real example is a much longer sql statement, but the simple example given does show the same error.
You do not have the required permissions to view the files attached to this post.

VEspinal

Re: Gupta Report Builder SQLQUERY

Post by VEspinal » 12 Dec 2013, 20:51

Hi:

The .qrp file that you have sent is begin called from an application that I assume it is a Team Developer 5.2 (Infor Visual ERP). They must have the .apt or .app files.

Therefore, I would recommend you to consult Infor.

Thanks

Regards,
Victor Espinal

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 1 guest