Hi Petr!

I have no knowledge about your error, just guessing that it could have something to do with how IBO treats subselects with parameters within TIBO* components (probably like most persons using IBO, I only use TIB_* components myself, never TIBO* components) with OldParameterOrdering - either due to a flaw in IBO or something particular to your situation.

Do the error occur if you change to LEFT JOIN (the result should be the same - excepting that your original query would return an error if the subselect happened to return more than one row, whereas a LEFT JOIN would simply return two rows)? I.e.

select
TAB1.ID,
TAB1.ZKR,
TAB1.DATUM,
TAB2.NAZ
from TAB1
left join TAB2 on TAB2.ID = TAB1.ID and TAB2.TYP = :TYP
WHERE TAB1.DATUM = :DATUM

As far as I can see (admittedly without looking all too carefully), your original statement qualified as Helen did, also ought to work. The above query should (IMO) be a very good replacement for your original query, but IBO should still be able to handle both type of queries without barking upon refresh (well, unless you do silly things like changing OldParameterOrdering programmatically before/when doing refresh).

HTH,
Set

-----Original Message-----
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf Of Petr Hartman
Sent: 12. september 2007 15:55
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] IBOQuery and Refresh

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:
> So, start with the syntax. For future-proofing use either all
> relation aliases or all table names for identifiers, since Fb 2 and
> above won't allow them to be intermixed.
>
> e.g., in this statement you need to qualify all of the fields:
> select
> TAB1.ID,
> TAB1.ZKR,
> TAB1.DATUM,
> (select TAB2.NAZ from TAB2
> where TAB2.ID = TAB1.ID and TAB2.TYP = :typ)
> as NAZ
> from TAB1
> WHERE TAB1.ID= :id

I tried it, but the behaviour was the same. I'd like to emphasize,
that the SQL statement is probably ok, IBOQuery is opened, but the
following Refresh causes the error.

> Now, double-check your metadata in TAB2, to make sure that TYP is
> actually an integer.

TYP is really an INTEGER

> >/*---
> >EXECUTE STATEMENT
> >TR_HANDLE = 3830508
> >STMT_HANDLE = 3830660
> >PARAMS = [ Version 1 SQLd 2 SQLn 2
> > [TYP] = 2
> > [DATUM] = '12 IX 2007' ] <---- W H A T I S T H I S ? At
> > best, it is a string (not a date, not a date literal).

IB_Monitor uses FormatDateTime( 'dd mmm yyyy', ...) function for it.

> Sorry, that's quite correct. Would you please test it with the
same
> triple-quoted value in the right side of the assignment, but use
> AsString instead?

It doesn't work (''1.1.2007'' is not a valid date and time). It's
Delphi's error message.

>
> >I tried also
> > IBOQuery1.ParamByName('DATUM').AsDateTime := Date;
> >without success.
>
> Try it with the full function syntax, Date(). And please describe
> what "without success" means.

I tried it with Date(). The behaviour was the same. "Without success"
means that the behaviour was the same and the error after Refresh
also causes.

I noticed that in procedure GetSeekCursor (unit IB_Components,
function TIB_BDataset.SysLookupKeyForBufferFields) there is this
piece of code:

for ii := 0 to Params.PSQLDA.SQLn - 1 - tmpPrms - incAdj - SPrmCnt do
PSeekDA.SQLVAR[ii + tmpPrms + KeyFields.PSQLDA.SQLn] :=
Params.PSQLDA.SQLVAR[ii + tmpPrms];

If I set OldParameterOrdering = 1, Params[0].FieldName = 'DATUM',
Params[1].FieldName = 'TYP' and everything is ok.
If I set OldParameterOrdering = 0, the order of these parameters is
inverse (Params[0].FieldName = 'TYP', Params[1].FieldName = 'DATUM')
and Refresh causes the error.

Thanks
Petr