Solved

Enable bind variables for SQL Quick Reports in IFS EE

  • 25 January 2024
  • 6 replies
  • 179 views
M
Rank
Userlevel 5
Badge +10

Hi,

 

We are keeping this to YES so currently rewriting all of our Quick Report in UPD20.

For most we can do by Concating (||) the variable with a ‘%’ to have the parameter as a wildcard.

The issue is if we have any variable, wildcard or not, in the group statement. The error  NOT A GROUP BY EXPRESSION is given.

Has anyone experienced this / rewritten sucessfully?

icon

Best answer by Charith Epitawatta 26 January 2024, 21:46

View original

6 replies

Buddhika Kurera
Rank
Userlevel 6
Badge +13

@Matthew From what I have understood on the information provided, your syntax of the SQL statement is not in order. That is why in general ORA-00979: not a GROUP BY expression error pops-up.

On the other hand, it makes no sense using an user entered value in the GROUP BY section of the statement as it is required enter a column (or set of columns) in the GROUP BY section and as well as in the columns section, as the following example.

SELECT t.contract, t.part_no,

       COUNT(t.part_no)

FROM   shop_ord t

WHERE  t.state = '&State'

GROUP  BY t.contract, t.part_no
 

If I got the question incorrect, please attach the GROUP BY section of your code here.

Hope this helps !

 

--Buddhika Kurera (buku) -- linkedin.com/in/bckurera/
Charith Epitawatta
Rank
Userlevel 7
Badge +31

 Hi @Matthew,

As @Buddhika Kurera has mentioned above, bind variables should go in the WHERE clause of the SQL statement, which would then act as user input parameters for a given Quick Report. You should not specify bind variables in the GROUP BY clause. 

If you need to allow wildcards for Quick Report parameters, you do not need to use PL/SQL string concatenation and the ‘%’ character. You can easily achieve that and more using Query Flags. You can read on how to use Query Flags in SQL Quick Reports in the documentation, which can be found here:

https://docs.ifs.com/techdocs/foundation1/050_development/026_br_and_a/005_adhoc_reporting/070_dev_qrep/default.htm

Hope this helps!

Charith - https://charith.xyz
M
Rank
Userlevel 5
Badge +10

@Buddhika Kurera - I’ll post a snippet of code shortly, just need to tidy it up for ‘public’ consumption.

 

@Charith Epitawatta - I tried using Query Flag on a basic quick report. Looking for a WHERE LIKE and with the Bind Variable Setting enabled you are correct, it does work and accept a % in the Search Box without conacttng. This issue is the User has to input in the query box. 

We are all about making things easier for End Users - delivering in our own #MomentOfService - I have tried in the past to get users to add a % into a report ordering box. It does not end well. Hence the use of concating.

 

Thank you both for the options though.

M
Rank
Userlevel 5
Badge +10

Hi @Buddhika Kurera - I have wrote a very quick example of what we are trying to achieve.

 

 

This works fine in SQL Developer and fine with the new setting set to NO but gives an error when ran in IEE with the setting set to YES

 

select count(part_no), case WHEN upper('&summery_Y_n') = 'Y' THEN '' ELSE CONTRACT END SITES
FROM INVENTORY_PART
GROUP BY case WHEN upper('&summery_Y_n') = 'Y' THEN '' ELSE CONTRACT END

Charith Epitawatta
Rank
Userlevel 7
Badge +31

Hi @Matthew,

I checked this in one of our reference environments and was able to recreate the error. Behind the scene, Quick Report API validates the SQL statement and it comes out as syntactically correct against the DBMS SQL Parser. A valid SQL statement is generated by processing the parameters as well, which can be run directly in the database, but somehow, IEE show the error. 

This appears to be a bug and I would recommend reporting this to IFS through a case. 

Hope this helps!

Charith - https://charith.xyz
M
Rank
Userlevel 5
Badge +10

Thanks - glad to see it looks like an issue.

 

I have raised a case - CS0197826 - and shall keep the community updated.

 

Many thanks,

 

Matthew

Reply


Terms & ConditionsCookie settings