Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    India
    Posts
    40

    Unanswered: Select Statement Prob

    Hi all

    Can i give a select statement like below
    I have one variable "FF" in which the value will be changed

    First it will be

    trunc(so_wr_dt,'MM')

    trunc(so_wr_dt,'W')

    trunc(so_wr_dt,'Q')

    as i want to constuct the Query based on the user input

    So now i want give the query as following

    Select
    Empno
    &FF
    From
    Emp
    Group by &FF;

    Is it possible to give like that pass the variable value to the select statement.

    If that is not the case i have to give the following three statement...as user wants the separate report

    Select
    Empno
    trunc(so_wr_dt,'MM')
    From
    Emp
    Group by
    trunc(so_wr_dt,'MM');

    Select
    Empno
    trunc(so_wr_dt,'W')
    From
    Emp
    Group by
    trunc(so_wr_dt,'w');

    Select
    Empno
    trunc(so_wr_dt,'Q')
    From
    Emp
    Group by
    trunc(so_wr_dt,'Q');


    Let me know the possible ways for this Question.

    Thanks
    Suryadevara

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like
    PHP Code:
    SELECT empno, &ff
      FROM emp
      GROUP BY 
    &ff
    won't compile as "empno" isn't part of a GROUP BY clause. You could include it, but would it make any sense? There should be an aggregate function in SELECT statement, such as ... I don't know ...
    PHP Code:
    SELECT SUM(salary), TRUNC(wo_wr_dt'&param')
      
    FROM emp 
      GROUP BY TRUNC
    (wo_wr_dt'&param'
    This query runs properly. User provided "param" values can be Q, W, MM and it returns some data. But I'm not sure that's what you wanted
    PHP Code:
    SELECT empnoTRUNC (so_wr_dt'&param')
      
    FROM emp

  3. #3
    Join Date
    Jun 2002
    Location
    India
    Posts
    40
    S that is working fine, u got what exactly what i was looking for,
    but i want to write that in a .sql file and pass the variable value while calling that file ...So can u help me on that if possible...
    thanks for u r code...
    Suryadevara

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    To pass a parameter value to your query, you'll need to modify it and save to a file (for example, we'll call it "myquery.sql"):
    PHP Code:
    SELECT empnoTRUNC (so_wr_dt'&1')
      
    FROM emp
    where &1 represents your first parameter. In Ora7 you can use up to 9 parameters (don't know about later versions) and reference them by number (i.e. second parameter would be &2 etc.).

    Command line would then be
    PHP Code:
    OSSQLPLUS username/password @myquery Q 
    if you want to pass "Q" as the parameter.

Posting Permissions

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