01791. 00000 - "not a SELECTed expression" on a customized window info

[Nicolas Micoud]

Hi,

We just encountered an issue on a info window which has been customized at tenant level.
The column used in the ORDER BY clause (System level) was not displayed.
That lead to a 01791. 00000 -  "not a SELECTed expression" error.

Wondering what would be the best way to manage this :
 - add a OrderByClause column on AD_InfoWindow ?
 - check the SELECT part of the generated sql in InfoWindow.buildDataSQL has all necessary column(s). But not sure is a good idea to "hack" that :-/

wdyt?

Regards,

Nicolas

[Heng Sin Low]

With tenant level customization, you will always have the risk that it will lead to SQL exceptions. If possible, it would be better to have a process that can validate the SQL generated.

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/8e273a76-a8d8-4fe5-be43-915285b71e25n%40googlegroups.com.

[Nicolas Micoud]

I agree, but in my case, the user doesn't want to display the column used in the order by clause.

I said "sorry, but you have to display it" (colum is now the last one of the grid). And this is acceptable (the workaround is to create a new IW at tenant System and use it).
But I think that would be better to be able to handle that at tenant level.

And maybe instead of a single process, that should be possible to add a IsValid column on AD_UserDef_Info and tick it automatically same as AD_InfoWindow where the checkbox is automatically checked/unchecked through the validate method + can be called by a process)

wdyt?

[Heng Sin Low]

Wouldn't using DisplayLogic or IsDisplay to hide the column work for the mentioned use case ?

Otherwise, it would be necessary to ensure the generated SQL always includes all order by columns. I'm not very sure but I think we probably have that implemented for report.

To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/3b371e67-7a55-4284-a8d7-85e7c1cd2346n%40googlegroups.com.

[Nicolas Micoud]

I haven't tried the DisplayLogic approach, will give it a try.

Anyway, that would make sense to validate the setup done at tenant level (no interest to allow using something that will fail).
So the IsValid on AD_UserDef_Info could solve that and help to find the cause

[Nicolas Micoud]

The DisplayLogic approach is not working (or I can't make it work).

And I think i found the cause (maybe specific to Oracle).

If there is more than one page of results, that will work.

SQL is ending with "ORDER BY d.XXA_DossierNo DESC FETCH FIRST 501 ROWS ONLY"

But if the number of results to display is showable on a single page, SQL is ending with "ORDER BY d.XXA_DossierNo DESC", and that's what throw the error.

I made this quick test : change on InfoWindow.buildDataSQL

And error is gone

I always have the "FETCH..." part on the ORDER BY clause now, ie: "ORDER BY d.XXA_DossierNo DESC FETCH FIRST 501 ROWS ONLY" / "ORDER BY d.XXA_DossierNo DESC FETCH FIRST 16 ROWS ONLY"

But not sure is correct to change that (have no idea about impact)

wdyt?

[Carlos Antonio Ruiz Gomez]

Hi Nicolas,

I don't see the error.

Which is the SQL failing?

Am 25.04.23 um 15:48 schrieb Nicolas Micoud:

[Nicolas Micoud]

Hi Carlos,

Error is that the sql generated by the IW is failing as the column used in the ORDER BY clause is not in the SELECT part.
And this only fail when there is no pagination.

When there is pagination, the ORDER BY clause add " FETCH FIRST x ROWS ONLY" and this prevent the query to throw the ORA-01791.

I've tried to reproduce it on https://test-oracle.idempiere.org - I set a Info Element there - but all my tests are successfull :-/
I'll try again tomorrow and keep you updated

Thanks,

Nicolas

[Carlos Antonio Ruiz Gomez]

Hi Nicolas, I would like to see the two statements, failing and not failing.

Which oracle version are you running?

I just did this test in my oracle-12c and it doesn't throw error:

SELECT c_bpartner_id, value, name

FROM c_bpartner

ORDER BY name2

Regards,

Carlos Ruiz

Am 25.04.23 um 18:29 schrieb Nicolas Micoud:

[Nicolas Micoud]

Hi Carlos,

SELECT c_bpartner_id, value, name
FROM c_bpartner

ORDER BY name2;
is working here


I'm using 19c locally and 18c for production.
I'll talk with our DBA as it is currently working on 12c (maybe there is a missing patch or an error on some config)


Failing sql (ORA-01791: cette expression n'a pas été SELECTionnée):

SELECT DISTINCT d.XXA_Dossier_ID, d.XXA_SinistreName AS XXA_SinistreName, d.XXA_CieAssurance_BPartner_ID AS XXA_CieAssurance_BPartner_ID, d.XXA_DossierState_ID AS XXA_DossierState_ID, d.XXA_SinistreDateApparition AS XXA_SinistreDateApparition, l.City || ' - ' || l.Address1 AS XXA_Sinistre_Location_ID, du.AD_User_ID AS AD_User_ID, d.XXA_NatureSinistre_ID AS XXA_NatureSinistre_ID FROM XXA_Dossier d
LEFT OUTER JOIN XXA_DossierBPartner dbp ON (d.XXA_Dossier_ID = dbp.XXA_Dossier_ID)
LEFT OUTER JOIN C_BPartner bp ON (dbp.C_BPartner_ID = bp.C_BPartner_ID)
LEFT OUTER JOIN AD_User dbpu ON (dbp.AD_User_ID = dbpu.AD_User_ID)
LEFT OUTER JOIN XXA_DossierUser du ON (d.XXA_Dossier_ID = du.XXA_Dossier_ID)
LEFT OUTER JOIN C_Location l ON (d.XXA_Sinistre_Location_ID = l.C_Location_ID) WHERE d.IsActive='Y' AND Trunc(d.XXA_DossierRecuLe) >= ? AND d.DocStatus != 'CL' AND d.AD_Client_ID IN(0,1000033) AND (d.AD_Org_ID IN (0,1000182))
ORDER BY d.XXA_DossierNo DESC

It happens when 16 records are retrieved (I'm using XXA_DossierRecuLe as a filter).


Not failing :
SELECT DISTINCT d.XXA_Dossier_ID, d.XXA_SinistreName AS XXA_SinistreName, d.XXA_CieAssurance_BPartner_ID AS XXA_CieAssurance_BPartner_ID, d.XXA_DossierState_ID AS XXA_DossierState_ID, d.XXA_SinistreDateApparition AS XXA_SinistreDateApparition, l.City || ' - ' || l.Address1 AS XXA_Sinistre_Location_ID, du.AD_User_ID AS AD_User_ID, d.XXA_NatureSinistre_ID AS XXA_NatureSinistre_ID FROM XXA_Dossier d
LEFT OUTER JOIN XXA_DossierBPartner dbp ON (d.XXA_Dossier_ID = dbp.XXA_Dossier_ID)
LEFT OUTER JOIN C_BPartner bp ON (dbp.C_BPartner_ID = bp.C_BPartner_ID)
LEFT OUTER JOIN AD_User dbpu ON (dbp.AD_User_ID = dbpu.AD_User_ID)
LEFT OUTER JOIN XXA_DossierUser du ON (d.XXA_Dossier_ID = du.XXA_Dossier_ID)
LEFT OUTER JOIN C_Location l ON (d.XXA_Sinistre_Location_ID = l.C_Location_ID) WHERE d.IsActive='Y' AND d.DocStatus != 'CL' AND d.AD_Client_ID IN(0,1000033) AND (d.AD_Org_ID IN (0,1000182)) ORDER BY d.XXA_DossierNo DESC FETCH FIRST 501 ROWS ONLY

The 'XXA_DossierRecuLe' filter is not used and 549 records are retrieved.

Thanks,

Nicolas

[Carlos Antonio Ruiz Gomez]

I see, the DISTINCT clause is what makes the difference:

SELECT DISTINCT c_bpartner_id, value, name

FROM c_bpartner

ORDER BY name2

SQL Error [1791] [42000]: ORA-01791: not a SELECTed expression

I had a similar problem and the display logic worked fine for me, just added 1=2 in DisplayLogic and the column is not shown.

Regards,

Carlos Ruiz

Am 26.04.23 um 06:27 schrieb Nicolas Micoud:

[Nicolas Micoud]

Hi Carlos,

I've already tried the DisplayLogic approach (adding 1=2) ; the column is not displayed in the grid, but it still throws the error (as the column is not present in the SELECT clause)

.

Can you check if what you've done is still working on v10 ?

Thanks

Nicolas

[Diego Ruiz]

Answer from Martin Schönbeck on Mattermost:

Hi, I have tried to answer the thread '01791. 00000 - "not a SELECTed expression" on a customized window info' in google groups, but my message is always deleted immediately after posting. Can anybody copy it into the group? I said:

perhaps you can solve it using the with clause

with partner as (select distinct c_bpartner_id, value, name, name2 from c_bpartner order by name2) select c_bpartner_id, value, name, name2 from partner

of course this will include name2 in the distinct clause, but I think there couldn't be no different name2 for the same c_bpartner_id. :wink:

Regards, Martin