Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2007
    Posts
    1

    Unanswered: Parameters in Oracle

    SELECT * FROM EMPLOYEES WHERE
    DEPARTMENT_ID = (
    SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = '&&name')
    ) AND
    LAST_NAME <> '&name'

    How come the query above prompts for input only once?

    SELECT * FROM EMPLOYEES WHERE
    DEPARTMENT_ID = (
    SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = '&name')
    ) AND
    LAST_NAME <> '&&name'

    How come the query above prompts for input twice?

    thanx

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    & creates a TEMPORARY substitution variable. You have to enter its value every time it is referenced.

    &&, on the other hand, creates a PERMANENT substitution variable. Once you set the value for this kind of a variable, it will be used EVERY TIME variable is referenced.

    Having said that, and viewing your queries, it is obvious that in the first query you have created a permanent subst. variable and the next referencing already knew its value.

    The second query created a temporary subst. variable first and you had to enter its value once again (however, this time you have created a permanent subst. variable).

    Don't forget to UNDEFINE a permanent substitution variable once you don't need it any more (using the 'UNDEFINE variable_name' command).

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Btw: your query won't work if there is more than one person with the same LAST_NAME in the same department...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •