Solved

Sybase With Clause using a declare

Posted on 2013-06-18
7
2,700 Views
Last Modified: 2013-06-25
Hi,

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'
)

Open in new window


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.
0
Comment
Question by:spen_lang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39255693
mmm, not something I know a lot about, but documentation suggests a slightly different syntax to TSQL.

CREATE VARIABLE identifier data-type
http://dcx.sybase.com/index.html#1101/en/dbreference_en11/create-variable-statement.html

The SET statement assigns a new value to a variable. The variable must have been previously created using a CREATE VARIABLE statement or DECLARE statement, or it must be an OUTPUT parameter for a procedure. The variable name can optionally use the Transact-SQL convention of an @ sign preceding the name. For example:

SET @localvar = 42

A variable can be used in a SQL statement anywhere a column name is allowed. If a column name exists with the same name as the variable, the variable value is used.
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
0
 

Author Comment

by:spen_lang
ID: 39255699
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...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39255711
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
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:spen_lang
ID: 39255717
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39255734
You can explicitly drop the created variable:
DROP VARIABLE  [ IF EXISTS ] identifier
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.
http://dcx.sybase.com/index.html#1101/en/dbreference_en11/drop-variable-statement.html
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39255738
also noteworthy:
Variables belong to the current connection, and disappear when you disconnect from the database or when you use the DROP VARIABLE statement. Variables are not visible to other connections.
create variable, url provided above
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39274007
thank you! I trust you were able to get the variables to work.

Cheers, Paul
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question