Hi Team,
   I have the following script..

{sql}
SCRIPT (IN EndDate Date)
BEGIN

   EXECUTE IMMEDIATE 'insert into ajobtickets
   select * from jobtickets
   where
   (jobstatus = ''CA'' and Date_In < ?)
   or
   (jobstatus = ''CO'' and paid = true and (date_out > date ''2000-01-01'' and date_out < ?))'
   using EndDate;
END
{sql}

   The first half of the 'or' of the Where clause works, all the relevant 'CA' jobs get copied over, but none of the
second half do??  Yes there are plenty of records that meet the second half.

   What have I missed please?

Regards & TIA,
Ian

If I take the first half of the 'or' out..

{sql}
SCRIPT (IN EndDate Date)
BEGIN

   EXECUTE IMMEDIATE 'insert into ajobtickets
   select * from jobtickets
   where
   (jobstatus = ''CO'' and paid = true and (date_out > date ''2000-01-01'' and date_out < ?))'
   using EndDate;
END
{sql}

   Then I get all the correct 'CO' records..

Ian

Had to do this..
{sql}
SCRIPT (IN EndDate Date)
BEGIN

   EXECUTE IMMEDIATE 'insert into ajobtickets
   select * from jobtickets
   where
   (jobstatus = ''CA'' and Date_In < ?)
   or
   (jobstatus = ''CO'' and paid = true and (date_out > date ''2000-01-01'' and date_out < ?))'
   using EndDate, EndDate;      <<<<<<<<<<<<<<<<<<<<<<<<<<
END
{sql}
   Note the two EndDate for the using statement.
   Bit of a trap that for the unwary.
   All good now.
   Thanks for looking.
Regards,
Ian

Ian


Well spotted. If you used EDBManager to set up a query with parameters you'll see you get one line for each time a parameter is accessed, not a line for each parameter by name. The UI is just nice to us and fills in duplicates.

Roy Lambert

Roy Lambert wrote:

> Ian
>
>
> Well spotted. If you used EDBManager to set up a query with
> parameters you'll see you get one line for each time a parameter is
> accessed, not a line for each parameter by name. The UI is just nice
> to us and fills in duplicates.
>
> Roy Lambert

Thus far I have not used parameters in spite of having a few suitable
scripts.
(Never found a decent example in the help showing how to use them
repeatedly in a script with multiple statements)
With this example I am encouraged to give it another shot .. when I get
some time!    

Malcolm