spen_lang
asked on
Sybase With Clause using a declare
Hi,
We are using Sybase anywhere 11 and I have a query that uses a with clause e.g.
However, what I would like to do is declare a variable, e.g. @Date to take place of the fixed value. I cannot seem to get this to work with Sybase.
We are using Sybase anywhere 11 and I have a query that uses a with clause e.g.
WITH Table1 AS
(
SELECT * FROM TABLE2 WHERE Date = '2013-06-18'
)
However, what I would like to do is declare a variable, e.g. @Date to take place of the fixed value. I cannot seem to get this to work with Sybase.
ASKER
Thanks for your response.
The problem I have is that I know the correct syntax for DECLARE and WITH but I am unable to combine the two in one statement.
I can DECLARE a variable in a statement without a WITH and vice versa...
The problem I have is that I know the correct syntax for DECLARE and WITH but I am unable to combine the two in one statement.
I can DECLARE a variable in a statement without a WITH and vice versa...
documentation suggests declare is for use with BEGIN - END
have you tried CREATE VARIABLE
I too would have started with declare - but that appears to be different in Anywhere
have you tried CREATE VARIABLE
I too would have started with declare - but that appears to be different in Anywhere
ASKER
The CREATE VARIABLE worked but the problem is that it is only dropped when the connection is dropped, the SQL will be ran from inside an application (report) and I think this may cause issues.
You can explicitly drop the created variable:
DROP VARIABLE [ IF EXISTS ] identifierhttp://dcx.sybase.com/index.html#1101/en/dbreference_en11/drop-variable-statement.html
Remarks
The DROP VARIABLE statement eliminates a SQL variable that was previously created using the CREATE VARIABLE statement. Variables are automatically eliminated when the database connection is released. Variables are often used for large objects, so eliminating them after use or setting them to NULL can free up significant resources (primarily disk space).
Use the IF EXISTS clause if you do not want an error returned when the DROP statement attempts to remove a database object that does not exist.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
thank you! I trust you were able to get the variables to work.
Cheers, Paul
Cheers, Paul
CREATE VARIABLE identifier data-type
http://dcx.sybase.com/index.html#1101/en/dbreference_en11/create-variable-statement.html
http://dcx.sybase.com/index.html#1101/en/dbreference_en11/set-statement.html
so without an ability to trial it, it might look like:
CREATE VARIABLE myDate datetime
set @myDate = '2013-06-19'
; with table1 as (
select * from whatever where adate > @myDate
)
select
*
from table1;
but 99% of the above is an assumption I regret to say