Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    13

    Unanswered: how to put the result of a query into a variable??

    DEFINE invoice_count = SELECT COUNT(Invoice#) from INVOICE NUMBER;

    This doesnt work. What's the correct syntax or method for doing this?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: how to put the result of a query into a variable??

    Originally posted by lnong
    DEFINE invoice_count = SELECT COUNT(Invoice#) from INVOICE NUMBER;

    This doesnt work. What's the correct syntax or method for doing this?
    In SQL Plus?

    For a substitution variable:

    COLUMN c NEW_VALUE invoice_count

    SELECT COUNT(*) c FROM invoice;

    Or for bind variables:

    VARIABLE c NUMBER

    SELECT COUNT(*) INTO :c FROM invoice;

  3. #3
    Join Date
    Jun 2002
    Posts
    13
    OK, here's what I just did:

    VARIABLE invoice_count NUMBER;
    SELECT COUNT(*) into :invoice_count from INVOICE;

    select
    lpad(' ', 11) || 'Currently, there are ' || '&invoice_count' || ' invoices.' line
    from dual;


    There are 2 things wrong with the output. First, when the SELECT COUNT(*)... statement is executed, the result is output to the screen immediately. How do I do it so that it wont be displayed until I use the actual variable (as in the second part).

    The other problem is that when the select lpad.... statement is executed, I get this displayed on the screen:

    "Currently, there are SELECT COUNT(*) into :invoice_count from INVOICE invoices". I guess I'm using an incorrect syntax for variable usage. What is the correct way to use variables during report formatting?

    Thanks.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by lnong
    OK, here's what I just did:

    VARIABLE invoice_count NUMBER;
    SELECT COUNT(*) into :invoice_count from INVOICE;

    select
    lpad(' ', 11) || 'Currently, there are ' || '&invoice_count' || ' invoices.' line
    from dual;


    There are 2 things wrong with the output. First, when the SELECT COUNT(*)... statement is executed, the result is output to the screen immediately. How do I do it so that it wont be displayed until I use the actual variable (as in the second part).

    The other problem is that when the select lpad.... statement is executed, I get this displayed on the screen:

    "Currently, there are SELECT COUNT(*) into :invoice_count from INVOICE invoices". I guess I'm using an incorrect syntax for variable usage. What is the correct way to use variables during report formatting?

    Thanks.
    Sorry, I got it slightly wrong before: to use SELECT INTO you must be executing PL/SQL:

    SQL> variable emp_count number
    SQL> exec select count(*) into :emp_count from emp;

    PL/SQL procedure successfully completed.

    SQL> select 'There are '||:emp_count||' emps' from dual;

    'THEREARE'||:EMP_COUNT||'EMPS'
    -------------------------------------------------------
    There are 14 emps

    SQL>

    You can use SET FEEDBACK OFF to suppress the "completed" message.

    Do not confuse :variable with &variable, they are not the same things.

Posting Permissions

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