Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    10

    Unanswered: Creating a prompt

    I am fairly new to sql and oracle.

    I am trying to create a prompt so i can run this query. Is there a way to bring up a prompt, so the user can enter the information so the query runs. Im my query below the prompt is taken by Promptid.
    I do not really want to make a GUI. Is there a way to just bring up a prompt screen??

    Select A.DName,
    FROM Designer
    WHERE Designerid
    IN(SELECT B.Supervisorid
    FROM DEsigner B
    WHERE Designerid = Promptid);

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    You can use the sqlplus ACCEPT and PROMPT commands

    eg. assuming Designerid is a number, then save the query to a file and run the script.

    ACCEPT Promptid number PROMPT 'Designer ID: '
    Select A.DName,
    FROM Designer
    WHERE Designerid
    IN(SELECT B.Supervisorid
    FROM DEsigner B
    WHERE Designerid = Promptid);

  3. #3
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi Sky

    Can i execute this in SQL PLUS, If so means the SQL Statement can't identify the PromptId as Variable ? how could i substitute the Value ?

    thanx in advance

  4. #4
    Join Date
    Nov 2002
    Posts
    833
    Select A.DName,
    FROM Designer
    WHERE Designerid
    IN(SELECT B.Supervisorid
    FROM DEsigner B
    WHERE Designerid = &Promptid);

  5. #5
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Thanx Osy. I completely forgot this syntax.
    thanx a lot

  6. #6
    Join Date
    Mar 2004
    Posts
    10
    Hi, designer id is not a number, its an varchar variable. Ive tried to run what you said and i get an error and most probably because of that. Is there a way to get it to work without using a number?

  7. #7
    Join Date
    Jan 2004
    Posts
    370
    ACCEPT Promptid char PROMPT 'Designer ID: '

  8. #8
    Join Date
    Mar 2004
    Posts
    10
    i keep on getting this error

    SP2-0850: Command "accept" is not available in iSQL*Plus
    old 6: WHERE Designerid = &Promptid)
    new 6: WHERE Designerid = SH786)

    Select A.DName
    *

    ERROR at line 1:
    ORA-00904: "A"."DNAME": invalid identifier


    Here is the query

    ACCEPT Promptid char PROMPT 'Designer ID: '
    Select A.DName
    FROM Designer
    WHERE Designerid
    IN(SELECT B.Supervisorid
    FROM DEsigner B
    WHERE Designerid = &Promptid);

  9. #9
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi Snipered,

    U have to give the &PromptId within the single Quotes .

    now u try this.

    ----------------------------------------------------------
    ACCEPT Promptid char PROMPT 'Student ID: ';
    Select *
    FROM student
    WHERE id
    IN(SELECT B.id
    FROM studentdetails B
    WHERE id ='&PromptId');
    -----------------------------------

  10. #10
    Join Date
    Mar 2004
    Posts
    10
    i keep getting this error.
    It still wont work, does anybody have a clue?

    SP2-0850: Command "accept" is not available in iSQL*Plus
    old 6: WHERE Designerid = '&Promptid')
    new 6: WHERE Designerid = 'SH786')

    Select A.DName
    *

    ERROR at line 1:
    ORA-00904: "A"."DNAME": invalid identifier

    This is the query

    ACCEPT Promptid char PROMPT 'Designer ID: '
    Select A.DName
    FROM Designer
    WHERE Designerid
    IN(SELECT B.Supervisorid
    FROM DEsigner B
    WHERE Designerid = '&Promptid');

  11. #11
    Join Date
    Mar 2004
    Posts
    10
    i figured out i missed the reference A on the FROM line.

    I now only get this error.



    SP2-0850: Command "accept" is not available in iSQL*Plus
    old 6: WHERE Designerid = '&Promptid')
    new 6: WHERE Designerid = 'SH786')



    no rows selected

  12. #12
    Join Date
    Mar 2004
    Posts
    10
    its ok, i realise it works, it was just the number i was entering, thanx for everyones help

Posting Permissions

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