Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Parameter selection into another table problem
Mon, Jul 16 2007 3:40 AMPermanent Link

Chris Holland

SEC Solutions Ltd.

Avatar

Team Elevate Team Elevate

If I try and run the following SQL:

SELECT * INTO Answer
FROM CustomerAgreed CA
LEFT OUTER JOIN Customer C ON C.AccountCode=CA.CustomerAccountCode
WHERE CA.AgreedUntil <= ?
ORDER BY C.AccountCode;
SELECT * FROM Answer

with the parameter entered as below:

Command.Parameters.Clear();
Command.Parameters.Add("AgreedUntil", OdbcType.DateTime);
Command.Parameters["AgreedUntil"].Value = DateTime.Today;

I get zero results returned.

However if I remove the "INTO Answer" or put in '2007-07-16' instead of
using a parameter it returns 1 record as it should.

I have posted an example project into the binaries.

Chris Holland
SEC Solutions Ltd.
Mon, Jul 16 2007 6:04 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Chris,

<< If I try and run the following SQL:

SELECT * INTO Answer
FROM CustomerAgreed CA
LEFT OUTER JOIN Customer C ON C.AccountCode=CA.CustomerAccountCode
WHERE CA.AgreedUntil <= ?
ORDER BY C.AccountCode;
SELECT * FROM Answer

with the parameter entered as below:

Command.Parameters.Clear();
Command.Parameters.Add("AgreedUntil", OdbcType.DateTime);
Command.Parameters["AgreedUntil"].Value = DateTime.Today;

I get zero results returned.

However if I remove the "INTO Answer" or put in '2007-07-16' instead of
using a parameter it returns 1 record as it should. >>

You're using a script, therefore the parameters will not be used.  The
DBISAM ODBC Driver doesn't support using parameters within a script.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Jul 17 2007 3:09 AMPermanent Link

Chris Holland

SEC Solutions Ltd.

Avatar

Team Elevate Team Elevate

Hi Tim,

Is there any other way to do this.

What I am trying to do is extract data from 2 tables where a date field
is within a specified value.

I was going to do this:

Select Fields INTO MEMORY\Answer
FROM AgreedElectrical
WHERE Date < ?
;
INSERT INTO MEMORY\Answer
(Fields)
SELECT Fields FROM AgreedMechanical
WHERE Date < ?
;
SELECT * FROM MEMORY\Answer

This is being run from a report generator so I cannot run it as seperate
calls, and I cannot embed the Date parameter into the SQL string (or
else I will have to rewrite the report everytime that I want to change
the date)

Chris Holland


Tim Young [Elevate Software] wrote:
> Chris,
>
> << If I try and run the following SQL:
>
>  SELECT * INTO Answer
>  FROM CustomerAgreed CA
>  LEFT OUTER JOIN Customer C ON C.AccountCode=CA.CustomerAccountCode
>  WHERE CA.AgreedUntil <= ?
>  ORDER BY C.AccountCode;
>  SELECT * FROM Answer
>
>  with the parameter entered as below:
>
>  Command.Parameters.Clear();
>  Command.Parameters.Add("AgreedUntil", OdbcType.DateTime);
>  Command.Parameters["AgreedUntil"].Value = DateTime.Today;
>
>  I get zero results returned.
>
>  However if I remove the "INTO Answer" or put in '2007-07-16' instead of
> using a parameter it returns 1 record as it should. >>
>
> You're using a script, therefore the parameters will not be used.  The
> DBISAM ODBC Driver doesn't support using parameters within a script.
>
Tue, Jul 17 2007 12:52 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Chris,

<< Is there any other way to do this. >>

Not really.  The issue is that most applications don't support using arrays
of parameters for the scripts, so the ODBC driver doesn't support them.  I
could see about changing this, but it may take some time.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Jul 18 2007 3:53 AMPermanent Link

Chris Holland

SEC Solutions Ltd.

Avatar

Team Elevate Team Elevate

Hi Tim,

It would be very handy if you could.

In the mean time I have changed the script to:

... date < CURRENT_DATE+30

They will have to make do with this at the moment.

Chris Holland

Tim Young [Elevate Software] wrote:
> Chris,
>
> << Is there any other way to do this. >>
>
> Not really.  The issue is that most applications don't support using arrays
> of parameters for the scripts, so the ODBC driver doesn't support them.  I
> could see about changing this, but it may take some time.
>
Image