SQL - How to declare a variable

Question

I have below Oracle SQL syntax:

select effective_date
from table
 where effective_date >= to_date(:processingDate, 'yyyymmdd')

My error is

'processingDate' not declared .

Question: What does this mark ':' mean? Do I need to specify what is 'processingDate'? If so, how?

Thank you!

Hello Please edit the question and tag with the appropriate database platform and post a minimal reproducible example that shows all the code definitions. :processingDate is a bind variable, but without seeing the other code, can't really help What is the client software written in? — OldProgrammer
– OldProgrammer, Commented Oct 22, 2019 at 20:28
Some systems use : preceding the name to denote a variable. Some use ?. Others use @. To know what is right for your database, we need to know what kind of database you're using. to_date() narrows it down to Oracle or Postgresql, but that's still not enough, because I've also seen reporting tools that use : preceding the name for variables regardless of the ultimate database platform. — Joel Coehoorn
– Joel Coehoorn, Commented Oct 22, 2019 at 20:29
"SQL" - the standard query language has no variables. Some DBMS products extend the SQL standard and allow the use of variables in non-procedural code. So you need to tell us which DBMS product you are using. — user330315
– user330315, Commented Oct 22, 2019 at 20:36

Joel Coehoorn · Accepted Answer · 2019-10-22 20:35:06Z

2

This is probably a variable (any of varchar(8), nvarchar(8), char(8), or nchar(8), since you match it to an 8-character fixed date format).

Some systems use : preceding the name to denote a variable name. Some use ?. Others use @. Others don't require any leading character at all.

To know what is right for your database, we need to know what kind of database you're using. to_date() narrows it down to Oracle or Postgresql, but that's still not enough, because I've also seen reporting tools which use : preceding the name for variables regardless of the ultimate database platform.

But you probably don't want to just add a DECLARE statement for the variable. The variable alone is not helpful here unless it already has a meaningful value. You need to figure out where that value is supposed to come from, and make sure that system is set up to provide it correctly.

answered Oct 22, 2019 at 20:35

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vicky Over a year ago

It's Oracle. I'm reading someone else's code, but I suppose 'processingDate' comes alone. Should I just declare this variable?

William Robertson Over a year ago

In Oracle, a colon prefix probably indicates a bind or host variable. Some desktop applications will prompt interactively, others need an explicit declaration. What desktop application are you using?

Popeye · Accepted Answer · 2019-10-23 02:29:42Z

1

If you want to execute query alone in sql*plus or in any of your tool then you can define this variable and use it using substitution variable. : is used for bind variables in oracle.

Substitution variable are declared using &.

So your case can go like this:

Define processingDate=20191023

select effective_date
from table
 where effective_date >= to_date('&processingDate', 'yyyymmdd');

Cheers!!

answered Oct 23, 2019 at 2:29

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

SQL - How to declare a variable

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related