1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

oracle reports

Discussion in 'Oracle Forms and Reports' started by narrasai27, Jul 31, 2013.

  1. narrasai27

    narrasai27 Active Member

    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    100
    This is a oracle report task:

    there is a column called country which contains data like
    country
    1)ind
    2)aus
    3)ind
    4)pak
    5)eng
    6)ind
    7)ind
    8)eur
    etc....

    so now a parameter has to be included with option like
    parameter 1)yes
    2)No

    now if the user selects yes in the parameter only ind should come
    if the user selects no in the parameter except ind all othr should cum
    if does'nt select anything all the data shd come
     
  2. narrasai27

    narrasai27 Active Member

    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    100
    So can i get a query for this pls....
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're stating that if some parameter you want passed is Yes then only records where country = ind are to be returned, if this unknown parameter is No then everything BUT records where country = ind are returned and if this unknown parameter is NULL everything in the table is returned?
     
  4. narrasai27

    narrasai27 Active Member

    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    100
    if the parameter is "yes" then return only "ind"
    "no" except "ind" all other shd cum
    "null" everything shd come
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would be nice to communicate in FULL words and in a reasonably professional manner.

    Have you tried writing this on your own? Please provide the work you've done so far on this so we know how to help you.
     
  6. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Plz see if this helps .. :)

    Code (SQL):

    SELECT  *
    FROM
       (WITH    t1  AS
           (SELECT  'ind'   country
            FROM    dual
            UNION ALL
            SELECT  'aus'   country
            FROM    dual
            UNION ALL
            SELECT  'ind'   country
            FROM    dual
            UNION ALL
            SELECT  'pak'   country
            FROM    dual
            UNION ALL
            SELECT  'eng'   country
            FROM    dual
            UNION ALL
            SELECT  'ind'   country
            FROM    dual
            UNION ALL
            SELECT  'ind'   country
            FROM    dual
            UNION ALL
            SELECT  'eur'   country
            FROM    dual
           )
        SELECT  country
               ,decode(country,'ind','Y','N')   flag
        FROM    t1
       )
    WHERE   nvl(flag,'~NULL~')   =   nvl(:p_option,nvl(flag,'~NULL~'));
     
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your exanple doesn't help matter any:

    Code (SQL):
    SQL> variable p_option varchar2(10)
    SQL>
    SQL> EXEC :p_option := 'Yes';
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM
      2  (SELECT  country
      3             ,decode(country,'ind','Y','N')   flag
      4      FROM    not_my_own_work)
      5  WHERE      nvl(flag,'~NULL~') =   nvl(:p_option,nvl(flag,'~NULL~'));
     
    no ROWS selected
     
    SQL>
    SQL> EXEC :p_option := 'No';
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM
      2  (SELECT  country
      3             ,decode(country,'ind','Y','N')   flag
      4      FROM    not_my_own_work)
      5  WHERE      nvl(flag,'~NULL~') =   nvl(:p_option,nvl(flag,'~NULL~'));
     
    no ROWS selected
     
    SQL>
    SQL> EXEC :p_option := '';
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM
      2  (SELECT  country
      3             ,decode(country,'ind','Y','N')   flag
      4      FROM    not_my_own_work)
      5  WHERE      nvl(flag,'~NULL~') =   nvl(:p_option,nvl(flag,'~NULL~'));
     
    COU F
    --- -
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    eng N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    ire N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    eng N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    ire N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    eng N
    ind Y
    rus N
    rus N
    ind Y
    rus N
    rus N
    ind Y
    rus N
     
    100 ROWS selected.
     
    SQL>
    It returns nothing for the first two cases of desired output. Also it has no provision, really, for passing parameters to it via the report interface.

    I was not going to post this but given that another 'solution' has been provided I feel it necessary to do so:

    Code (SQL):
    SQL> CREATE TABLE not_my_own_work(
      2          yippie  NUMBER,
      3          country varchar2(3),
      4          yappie  varchar2(40),
      5          splasmo DATE,
      6          CONSTRAINT workie_not_mine_pk
      7          PRIMARY KEY (yippie)
      8  );
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..100 loop
      3                  IF MOD(i,3) = 0 THEN
      4                          INSERT INTO not_my_own_work
      5                          VALUES(i, 'ind', 'yesyesyesyesyesyesyes', sysdate);
      6                  elsif MOD(i,23) = 0 THEN
      7                          INSERT INTO not_my_own_work
      8                          VALUES(i, 'eng', 'nononononononononono', sysdate);
      9                  elsif MOD(i,29) = 0 THEN
     10                          INSERT INTO not_my_own_work
     11                          VALUES(i, 'ire', 'nononononononononono', sysdate);
     12                  ELSE
     13                          INSERT INTO not_my_own_work
     14                          VALUES(i, 'rus','maybemaybemaybemaybemaybe',sysdate);
     15                  END IF;
     16          END loop;
     17
     18          commit;
     19
     20  END;
     21  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> CREATE OR REPLACE PROCEDURE yappoo_guank(pparam IN varchar2 DEFAULT NULL)
      2  IS
      3          TYPE work_rec IS TABLE OF not_my_own_work%rowtype;
      4          work_tab        work_rec;
      5          cursor cursoryes IS
      6          SELECT * FROM not_my_own_work WHERE country = 'ind';
      7          cursor cursorno IS
      8          SELECT * FROM not_my_own_work WHERE country != 'ind';
      9          cursor cursornull IS
     10          SELECT * FROM not_my_own_work;
     11  BEGIN
     12          IF LOWER(pparam) = 'yes' THEN
     13                  OPEN cursoryes;
     14                  fetch cursoryes bulk collect INTO work_tab;
     15                  close cursoryes;
     16          elsif LOWER(pparam) = 'no' THEN
     17                  OPEN cursorno;
     18                  fetch cursorno bulk collect INTO work_tab;
     19                  close cursorno;
     20          elsif pparam IS NULL THEN
     21                  OPEN cursornull;
     22                  fetch cursornull bulk collect INTO work_tab;
     23                  close cursornull;
     24          END IF;
     25
     26          FOR i IN work_tab.FIRST..work_tab.LAST loop
     27                  dbms_output.put_line(work_tab(i).yippie||'  '||work_tab(i).country||'  '||work_tab(i).yappie||'  '||work_tab(i).splasmo
    );
     28          END loop;
     29
     30  END;
     31  /
     
    PROCEDURE created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> SET serveroutput ON SIZE 1000000
    SQL>
    SQL> EXEC yappoo_guank('YES')
    3  ind  yesyesyesyesyesyesyes  04-AUG-13
    6  ind  yesyesyesyesyesyesyes  04-AUG-13
    9  ind  yesyesyesyesyesyesyes  04-AUG-13
    12  ind  yesyesyesyesyesyesyes  04-AUG-13
    15  ind  yesyesyesyesyesyesyes  04-AUG-13
    18  ind  yesyesyesyesyesyesyes  04-AUG-13
    21  ind  yesyesyesyesyesyesyes  04-AUG-13
    24  ind  yesyesyesyesyesyesyes  04-AUG-13
    27  ind  yesyesyesyesyesyesyes  04-AUG-13
    30  ind  yesyesyesyesyesyesyes  04-AUG-13
    33  ind  yesyesyesyesyesyesyes  04-AUG-13
    36  ind  yesyesyesyesyesyesyes  04-AUG-13
    39  ind  yesyesyesyesyesyesyes  04-AUG-13
    42  ind  yesyesyesyesyesyesyes  04-AUG-13
    45  ind  yesyesyesyesyesyesyes  04-AUG-13
    48  ind  yesyesyesyesyesyesyes  04-AUG-13
    51  ind  yesyesyesyesyesyesyes  04-AUG-13
    54  ind  yesyesyesyesyesyesyes  04-AUG-13
    57  ind  yesyesyesyesyesyesyes  04-AUG-13
    60  ind  yesyesyesyesyesyesyes  04-AUG-13
    63  ind  yesyesyesyesyesyesyes  04-AUG-13
    66  ind  yesyesyesyesyesyesyes  04-AUG-13
    69  ind  yesyesyesyesyesyesyes  04-AUG-13
    72  ind  yesyesyesyesyesyesyes  04-AUG-13
    75  ind  yesyesyesyesyesyesyes  04-AUG-13
    78  ind  yesyesyesyesyesyesyes  04-AUG-13
    81  ind  yesyesyesyesyesyesyes  04-AUG-13
    84  ind  yesyesyesyesyesyesyes  04-AUG-13
    87  ind  yesyesyesyesyesyesyes  04-AUG-13
    90  ind  yesyesyesyesyesyesyes  04-AUG-13
    93  ind  yesyesyesyesyesyesyes  04-AUG-13
    96  ind  yesyesyesyesyesyesyes  04-AUG-13
    99  ind  yesyesyesyesyesyesyes  04-AUG-13
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXEC yappoo_guank('No')
    1  rus  maybemaybemaybemaybemaybe  04-AUG-13
    2  rus  maybemaybemaybemaybemaybe  04-AUG-13
    4  rus  maybemaybemaybemaybemaybe  04-AUG-13
    5  rus  maybemaybemaybemaybemaybe  04-AUG-13
    7  rus  maybemaybemaybemaybemaybe  04-AUG-13
    8  rus  maybemaybemaybemaybemaybe  04-AUG-13
    10  rus  maybemaybemaybemaybemaybe  04-AUG-13
    11  rus  maybemaybemaybemaybemaybe  04-AUG-13
    13  rus  maybemaybemaybemaybemaybe  04-AUG-13
    14  rus  maybemaybemaybemaybemaybe  04-AUG-13
    16  rus  maybemaybemaybemaybemaybe  04-AUG-13
    17  rus  maybemaybemaybemaybemaybe  04-AUG-13
    19  rus  maybemaybemaybemaybemaybe  04-AUG-13
    20  rus  maybemaybemaybemaybemaybe  04-AUG-13
    22  rus  maybemaybemaybemaybemaybe  04-AUG-13
    23  eng  nononononononononono  04-AUG-13
    25  rus  maybemaybemaybemaybemaybe  04-AUG-13
    26  rus  maybemaybemaybemaybemaybe  04-AUG-13
    28  rus  maybemaybemaybemaybemaybe  04-AUG-13
    29  ire  nononononononononono  04-AUG-13
    31  rus  maybemaybemaybemaybemaybe  04-AUG-13
    32  rus  maybemaybemaybemaybemaybe  04-AUG-13
    34  rus  maybemaybemaybemaybemaybe  04-AUG-13
    35  rus  maybemaybemaybemaybemaybe  04-AUG-13
    37  rus  maybemaybemaybemaybemaybe  04-AUG-13
    38  rus  maybemaybemaybemaybemaybe  04-AUG-13
    40  rus  maybemaybemaybemaybemaybe  04-AUG-13
    41  rus  maybemaybemaybemaybemaybe  04-AUG-13
    43  rus  maybemaybemaybemaybemaybe  04-AUG-13
    44  rus  maybemaybemaybemaybemaybe  04-AUG-13
    46  eng  nononononononononono  04-AUG-13
    47  rus  maybemaybemaybemaybemaybe  04-AUG-13
    49  rus  maybemaybemaybemaybemaybe  04-AUG-13
    50  rus  maybemaybemaybemaybemaybe  04-AUG-13
    52  rus  maybemaybemaybemaybemaybe  04-AUG-13
    53  rus  maybemaybemaybemaybemaybe  04-AUG-13
    55  rus  maybemaybemaybemaybemaybe  04-AUG-13
    56  rus  maybemaybemaybemaybemaybe  04-AUG-13
    58  ire  nononononononononono  04-AUG-13
    59  rus  maybemaybemaybemaybemaybe  04-AUG-13
    61  rus  maybemaybemaybemaybemaybe  04-AUG-13
    62  rus  maybemaybemaybemaybemaybe  04-AUG-13
    64  rus  maybemaybemaybemaybemaybe  04-AUG-13
    65  rus  maybemaybemaybemaybemaybe  04-AUG-13
    67  rus  maybemaybemaybemaybemaybe  04-AUG-13
    68  rus  maybemaybemaybemaybemaybe  04-AUG-13
    70  rus  maybemaybemaybemaybemaybe  04-AUG-13
    71  rus  maybemaybemaybemaybemaybe  04-AUG-13
    73  rus  maybemaybemaybemaybemaybe  04-AUG-13
    74  rus  maybemaybemaybemaybemaybe  04-AUG-13
    76  rus  maybemaybemaybemaybemaybe  04-AUG-13
    77  rus  maybemaybemaybemaybemaybe  04-AUG-13
    79  rus  maybemaybemaybemaybemaybe  04-AUG-13
    80  rus  maybemaybemaybemaybemaybe  04-AUG-13
    82  rus  maybemaybemaybemaybemaybe  04-AUG-13
    83  rus  maybemaybemaybemaybemaybe  04-AUG-13
    85  rus  maybemaybemaybemaybemaybe  04-AUG-13
    86  rus  maybemaybemaybemaybemaybe  04-AUG-13
    88  rus  maybemaybemaybemaybemaybe  04-AUG-13
    89  rus  maybemaybemaybemaybemaybe  04-AUG-13
    91  rus  maybemaybemaybemaybemaybe  04-AUG-13
    92  eng  nononononononononono  04-AUG-13
    94  rus  maybemaybemaybemaybemaybe  04-AUG-13
    95  rus  maybemaybemaybemaybemaybe  04-AUG-13
    97  rus  maybemaybemaybemaybemaybe  04-AUG-13
    98  rus  maybemaybemaybemaybemaybe  04-AUG-13
    100  rus  maybemaybemaybemaybemaybe  04-AUG-13
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXEC yappoo_guank()
    1  rus  maybemaybemaybemaybemaybe  04-AUG-13
    2  rus  maybemaybemaybemaybemaybe  04-AUG-13
    3  ind  yesyesyesyesyesyesyes  04-AUG-13
    4  rus  maybemaybemaybemaybemaybe  04-AUG-13
    5  rus  maybemaybemaybemaybemaybe  04-AUG-13
    6  ind  yesyesyesyesyesyesyes  04-AUG-13
    7  rus  maybemaybemaybemaybemaybe  04-AUG-13
    8  rus  maybemaybemaybemaybemaybe  04-AUG-13
    9  ind  yesyesyesyesyesyesyes  04-AUG-13
    10  rus  maybemaybemaybemaybemaybe  04-AUG-13
    11  rus  maybemaybemaybemaybemaybe  04-AUG-13
    12  ind  yesyesyesyesyesyesyes  04-AUG-13
    13  rus  maybemaybemaybemaybemaybe  04-AUG-13
    14  rus  maybemaybemaybemaybemaybe  04-AUG-13
    15  ind  yesyesyesyesyesyesyes  04-AUG-13
    16  rus  maybemaybemaybemaybemaybe  04-AUG-13
    17  rus  maybemaybemaybemaybemaybe  04-AUG-13
    18  ind  yesyesyesyesyesyesyes  04-AUG-13
    19  rus  maybemaybemaybemaybemaybe  04-AUG-13
    20  rus  maybemaybemaybemaybemaybe  04-AUG-13
    21  ind  yesyesyesyesyesyesyes  04-AUG-13
    22  rus  maybemaybemaybemaybemaybe  04-AUG-13
    23  eng  nononononononononono  04-AUG-13
    24  ind  yesyesyesyesyesyesyes  04-AUG-13
    25  rus  maybemaybemaybemaybemaybe  04-AUG-13
    26  rus  maybemaybemaybemaybemaybe  04-AUG-13
    27  ind  yesyesyesyesyesyesyes  04-AUG-13
    28  rus  maybemaybemaybemaybemaybe  04-AUG-13
    29  ire  nononononononononono  04-AUG-13
    30  ind  yesyesyesyesyesyesyes  04-AUG-13
    31  rus  maybemaybemaybemaybemaybe  04-AUG-13
    32  rus  maybemaybemaybemaybemaybe  04-AUG-13
    33  ind  yesyesyesyesyesyesyes  04-AUG-13
    34  rus  maybemaybemaybemaybemaybe  04-AUG-13
    35  rus  maybemaybemaybemaybemaybe  04-AUG-13
    36  ind  yesyesyesyesyesyesyes  04-AUG-13
    37  rus  maybemaybemaybemaybemaybe  04-AUG-13
    38  rus  maybemaybemaybemaybemaybe  04-AUG-13
    39  ind  yesyesyesyesyesyesyes  04-AUG-13
    40  rus  maybemaybemaybemaybemaybe  04-AUG-13
    41  rus  maybemaybemaybemaybemaybe  04-AUG-13
    42  ind  yesyesyesyesyesyesyes  04-AUG-13
    43  rus  maybemaybemaybemaybemaybe  04-AUG-13
    44  rus  maybemaybemaybemaybemaybe  04-AUG-13
    45  ind  yesyesyesyesyesyesyes  04-AUG-13
    46  eng  nononononononononono  04-AUG-13
    47  rus  maybemaybemaybemaybemaybe  04-AUG-13
    48  ind  yesyesyesyesyesyesyes  04-AUG-13
    49  rus  maybemaybemaybemaybemaybe  04-AUG-13
    50  rus  maybemaybemaybemaybemaybe  04-AUG-13
    51  ind  yesyesyesyesyesyesyes  04-AUG-13
    52  rus  maybemaybemaybemaybemaybe  04-AUG-13
    53  rus  maybemaybemaybemaybemaybe  04-AUG-13
    54  ind  yesyesyesyesyesyesyes  04-AUG-13
    55  rus  maybemaybemaybemaybemaybe  04-AUG-13
    56  rus  maybemaybemaybemaybemaybe  04-AUG-13
    57  ind  yesyesyesyesyesyesyes  04-AUG-13
    58  ire  nononononononononono  04-AUG-13
    59  rus  maybemaybemaybemaybemaybe  04-AUG-13
    60  ind  yesyesyesyesyesyesyes  04-AUG-13
    61  rus  maybemaybemaybemaybemaybe  04-AUG-13
    62  rus  maybemaybemaybemaybemaybe  04-AUG-13
    63  ind  yesyesyesyesyesyesyes  04-AUG-13
    64  rus  maybemaybemaybemaybemaybe  04-AUG-13
    65  rus  maybemaybemaybemaybemaybe  04-AUG-13
    66  ind  yesyesyesyesyesyesyes  04-AUG-13
    67  rus  maybemaybemaybemaybemaybe  04-AUG-13
    68  rus  maybemaybemaybemaybemaybe  04-AUG-13
    69  ind  yesyesyesyesyesyesyes  04-AUG-13
    70  rus  maybemaybemaybemaybemaybe  04-AUG-13
    71  rus  maybemaybemaybemaybemaybe  04-AUG-13
    72  ind  yesyesyesyesyesyesyes  04-AUG-13
    73  rus  maybemaybemaybemaybemaybe  04-AUG-13
    74  rus  maybemaybemaybemaybemaybe  04-AUG-13
    75  ind  yesyesyesyesyesyesyes  04-AUG-13
    76  rus  maybemaybemaybemaybemaybe  04-AUG-13
    77  rus  maybemaybemaybemaybemaybe  04-AUG-13
    78  ind  yesyesyesyesyesyesyes  04-AUG-13
    79  rus  maybemaybemaybemaybemaybe  04-AUG-13
    80  rus  maybemaybemaybemaybemaybe  04-AUG-13
    81  ind  yesyesyesyesyesyesyes  04-AUG-13
    82  rus  maybemaybemaybemaybemaybe  04-AUG-13
    83  rus  maybemaybemaybemaybemaybe  04-AUG-13
    84  ind  yesyesyesyesyesyesyes  04-AUG-13
    85  rus  maybemaybemaybemaybemaybe  04-AUG-13
    86  rus  maybemaybemaybemaybemaybe  04-AUG-13
    87  ind  yesyesyesyesyesyesyes  04-AUG-13
    88  rus  maybemaybemaybemaybemaybe  04-AUG-13
    89  rus  maybemaybemaybemaybemaybe  04-AUG-13
    90  ind  yesyesyesyesyesyesyes  04-AUG-13
    91  rus  maybemaybemaybemaybemaybe  04-AUG-13
    92  eng  nononononononononono  04-AUG-13
    93  ind  yesyesyesyesyesyesyes  04-AUG-13
    94  rus  maybemaybemaybemaybemaybe  04-AUG-13
    95  rus  maybemaybemaybemaybemaybe  04-AUG-13
    96  ind  yesyesyesyesyesyesyes  04-AUG-13
    97  rus  maybemaybemaybemaybemaybe  04-AUG-13
    98  rus  maybemaybemaybemaybemaybe  04-AUG-13
    99  ind  yesyesyesyesyesyesyes  04-AUG-13
    100  rus  maybemaybemaybemaybemaybe  04-AUG-13
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    All three conditions are satisfied and the reports can be generated as desired,
     
  8. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Haha :D LOL

    I guess here, this is understood that the parameter LOV (List of Values, which is provided to make sure the parameter inputs are compatible with the report) has to be defined in accordance. This is what the initiator hinted from the word "selects" in the first post.
    YES/NO or Y/N whatever it is, whoever is designing the report needs to make sure that the parameter LOV and the report query are compatible.
    And moreover, i guess the initiator requires a query and not a procedure.