1.
How can i use parameter of sub query in SP?

DELCARE qry CURSOR WITH RETURN FOR stmt;

PREPARE stmt FROM ' select
id,
(SELECT count(*) from xxx where yyyy = ?) as cnt
FROM zz
WHERE kkk = ?';

OPEN qry USING param1, param2;

This shows error.

2.
Is there any way convert date to valid sql date?

PREPARE m FROM '.....' || xxxx(aDATE);

aDATE is Date type and i want xxx returns something lik DATE'2008-05-01'.

Tomas,

> 1.
> How can i use parameter of sub query in SP?
>
> This shows error.

What error do you see?
At the moment EDB doesn't support parameters in sub-queries and I don't know
if it will - let's wait for what Tim has to say about it.


> 2.
> Is there any way convert date to valid sql date?

If you must use string concatenation, you can do it like this:

PREPARE sql1 FROM   'UPDATE Tbl1 SET Dt = DATE '''||CAST(aDATE as
varchar(14))||'''';
EXECUTE sql1;

OR you can use a better solution with parameters:

PREPARE sql1 FROM  'UPDATE Tbl1 SET Dt = ?';
EXECUTE sql1 USING aDATE;

--
Fernando Dias
[Team Elevate]

Tomas,

One more thing:
If you chose to use string concatenation, be aware that you must deal wit
null values as a special case.
--
Fernando Dias
[Team Elevate]

The error message is
"ElevateDB Error#700 An error was found in the statement at... (Invalid
expression ? found. dynamic parameter references not allowed)"

"Fernando Dias [Team Elevate]" <fernandodias.removthis@easygate.com.pt>
wrote in message
news:560165B2-027C-4F77-8107-0A9730616140@news.elevatesoft.com...
> Tomas,
>
>> 1.
>> How can i use parameter of sub query in SP?
>>
>> This shows error.
>
> What error do you see?
> At the moment EDB doesn't support parameters in sub-queries and I don't
> know if it will - let's wait for what Tim has to say about it.
>
>
>> 2.
>> Is there any way convert date to valid sql date?
>
> If you must use string concatenation, you can do it like this:
>
> PREPARE sql1 FROM   'UPDATE Tbl1 SET Dt = DATE '''||CAST(aDATE as
> varchar(14))||'''';
> EXECUTE sql1;
>
> OR you can use a better solution with parameters:
>
> PREPARE sql1 FROM  'UPDATE Tbl1 SET Dt = ?';
> EXECUTE sql1 USING aDATE;
>
> --
> Fernando Dias
> [Team Elevate]
>

Tomas,

<< How can i use parameter of sub query in SP? >>

The issue is that you're trying to use a parameter in the SELECT column
expressions, which is not allowed.  However, I suppose that I can make an
exception for sub-query WHERE clauses and allow this in the next build.

--
Tim Young
Elevate Software
www.elevatesoft.com

Fernando,

<< At the moment EDB doesn't support parameters in sub-queries and I don't
know if it will - let's wait for what Tim has to say about it. >>

Actually, it's the location of the sub-query in the SELECT expressions list,
not the sub-query itself.  EDB supports parameters in sub-queries, no
problem.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thank you for the clarification. This is what happens when we jump to
conclusions without the required knowledge

--
Fernando Dias
[Team Elevate]