0

I have a very long Oracle SQL script that filters for article numbers in multiple places, i.e.:

[...]
where article_nr = 12345
[...]

To avoid adjusting 20 lines of code manually when changing the article, I introduced variables, i.e.:

define article_1 = 12345
[...]
where article_nr = &&article_1
[...]

Now, however, I get an error saying that it's "missing right parenthesis". I did not touch anything regarding a parenthesis, just replaced the 12345 by the variable. Do I have to escape the "&&" somehow?

Sorry for not posting the full code, but its multiple nested selects.

Thanks

EDIT (Working example):

This works:

select * from stores
where
   (opening_date >= '21.11.2016')
;

This DOESN'T works (either with one or with two "&"):

define opening = '21.11.2016'

select * from stores
where
   (opening_date >= &&opening)
;

==> "missing right parenthesis"

Berbatov
  • 999
  • 1
  • 10
  • 18
  • Why did you put 2 &? To use parameters you should either write :article_1 or &article_1 depending on your context. – StephaneM Nov 24 '16 at 10:32
  • I followed this guide: [link](http://stackoverflow.com/questions/5653423/how-do-i-use-variables-in-oracle-sql-developer) – Berbatov Nov 24 '16 at 10:50
  • PS: Even with only one "&", I get the same error – Berbatov Nov 24 '16 at 10:59
  • 2
    in one of the comments of [the answer where the define is used](http://stackoverflow.com/a/14333561/7170434) in your link, they say you have to surround `&&article_1` with single quotes when using it to compare values: `'&&article_1'` – J.Baoby Nov 24 '16 at 11:09
  • [This link](http://www.oracle.com/technetwork/testcontent/sub-var2-099853.html) says the same. – J.Baoby Nov 24 '16 at 11:13
  • 1
    As an aside, what is the datatype of the opening_date column? If it's DATE then you should really be converting your string-representing-a-date into a date, either by using the ANSI `DATE 'yyyy-mm-dd'` literal, or by using `to_date('', '')`. Also, if your opening_date column is a VARCHAR2 column, your comparison is not going to return the right results. After all, '22.01.2016' is greater than '21.11.2016', yet 22nd Jan 2016 is definitely before 21st Nov 2016! – Boneist Nov 24 '16 at 11:29

1 Answers1

0

Numeric literals do not require quote marks ...

where article_nr = 12345

but date (and string) literals do ...

(opening_date >= '21.11.2016')

The same rules apply for substitution variables. Numeric assignment:

where article_nr = &&article_1

Date assignment:

(opening_date >= '&&opening')

As others have commented, it's good practice to explicitly cast dates:

(opening_date >= to_date('&&opening', 'dd.mm.yyyy'))
APC
  • 144,005
  • 19
  • 170
  • 281