Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
It's not happening?? |
Thu, Jul 19 2018 3:09 AM | Permanent Link |
Ian Branch | 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 |
Thu, Jul 19 2018 3:38 AM | Permanent Link |
Ian Branch | 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 |
Thu, Jul 19 2018 5:01 AM | Permanent Link |
Ian Branch | 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 |
Thu, Jul 19 2018 7:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Jul 19 2018 7:59 AM | Permanent Link |
Malcolm Taylor | 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 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |