|Subject||SV: [IBO] IBOQuery : Filtering problem with left join|
|Author||Svein Erling Tysvær|
>I have an IBOQuery with an SQL containing a LEFT JOIN :Don't think of parameters as variables. To Firebird, your two references to :USAGER_ are two different parameters. IBO tries to make it so that you can treat them as if they were a variable, but I wouldn't be surprised if it failed in somewhat odd cases like yours. I was bitten by this some time ago when I tried to use the same parameter against one CHAR and one VARCHAR field (I think that has since been corrected in IBO, but I learnt that I should never think of parameters as variables).
> CAST(:USAGER_ AS VARCHAR(10)) AS USAGER,
> LEFT JOIN TCSELECT ON (TCCLIENTS.CLIENT = TCSELECT.CLIENT AND TCSELECT.USAGER = :USAGER_)
>Though, when a filter is applied, like
>WHERE ((UPPER( TCCLIENTS.ASSOCIE ) = 'AP'))
>All the data from the TCSELECT.XS is lost. When I show the data in a grid, the whole column gets blank. Only a manual RefreshRows will get the data back.
>I've tried putting the join conditions in the Joi nLinks of the DataSet. This correctly displayed the 'XS' field when a filter was applied,
>though it corrupted the dataset SQL when further filters were applied (as expected).
>How to automatically get the right 'XS' column value when a filter is applied?
If you have only one reference to the parameter, the query (and filter) may work (unless the reason for your error is different from what I believe). So try something like this:
with tmp(USAGER) as
(SELECT CAST(:USAGER_ AS VARCHAR(10)
CROSS JOIN tmp
LEFT JOIN TCSELECT ON TCCLIENTS.CLIENT = TCSELECT.CLIENT AND TCSELECT.USAGER = tmp.USAGER