This discussion is archived
6 Replies Latest reply: Jan 28, 2013 10:10 PM by HamidHelal RSS

Looping through report paramters

987284 Newbie
Currently Being Moderated
Dear All,
I have a problem regarding report parameters.
I have given a taskt save report paramters to a table REP_INFO structure blow.

Report Name, Paramter name, Value, Report Run Date/Time
------------------------------------------------------------------------------------

Now I have 5 report paramters, ORG_ID, P_FDATE,P_TDATE, LOT_NO, LOT_QUANTITY
I have applied an INSERT query in AFTER PARAMTER FORM TRIGGER.

Report Trigger: AFTER_PARAMTER_FORM
----------------------------------------------------------
INSERT INTO REP_INFO VALUES (......................)

ISSUES:
--------------------------------
1) Should I have to issue seperate INSERT statements for each paramter?
2) Problem get worse when want to add three additional paramters to the report. What the solution?
3) Can't we dynamically refer report paramter name and insert values in a LOOP through using a single INSERT statement????



Best Regards,
Usman
  • 1. Re: Looping through report paramters
    yoonas Expert
    Currently Being Moderated
    Hi,
    What are the column you have in the table?
    If you don't have seperate columns, How are you saving it in the table comma, seperated values or?

    If you have columns for each parameter why don't you insert all the parameters together
    insert into rep_info values(ORG_ID, P_FDATE,P_TDATE, LOT_NO, LOT_QUANTITY)
    
    or --coma seperated
    
    insert into rep_info values(ORG_ID||','|| P_FDATE||','||P_TDATE...etc)
    Regards
    Yoonas
  • 2. Re: Looping through report paramters
    987284 Newbie
    Currently Being Moderated
    Thanks for your reply...But my problem persist........
    I have following columns in the the table and can't change them...

    1) Report Name
    2) Paramter Name
    3) Paramter Value
    4) Report Run Date/Time

    I can only save one paramter per row. What is the possible solution?
  • 3. Re: Looping through report paramters
    yoonas Expert
    Currently Being Moderated
    I think you better consider changing the design as you will have so much redundant data. For the same report you are saving 'report name', Report Run Date/Time again and again the more parameter you have the more records you are inserting with same data

    What about this
    Insert into rep_info values(col_1,col_2,P_1...)
    Insert into rep_info values(col_1,col_2,P_2...)
    Insert into rep_info values(col_1,col_2,P_3...)
    Insert into rep_info values(col_1,col_2,P_4...)
  • 4. Re: Looping through report paramters
    987284 Newbie
    Currently Being Moderated
    Well....Younus thanks for your suggestion...
    May be we should open this for another possible solution.
  • 5. Re: Looping through report paramters
    yoonas Expert
    Currently Being Moderated
    You are welcome
    If your post is answered please mark it answered. If any post is correct or helpful mark it accordingly as well :)

    Edited by: yoonus on Jan 28, 2013 10:00 PM
  • 6. Re: Looping through report paramters
    HamidHelal Guru
    Currently Being Moderated
    UsmanPK wrote:
    Well....Younus thanks for your suggestion...
    May be we should open this for another possible solution.
    Well,
    What you want ?
    1. Do you want to insert all parameter in a single record ?
    or
    2. Do you want to insert each record for each parameter ?

    or
    3. Only those parameter what are not null ?
    Or
    What your want ?

    Answer
    For point 1 and 2 may follow the Younus example
    For point 3
    may follow
    if :paramert1 is not null then
    insert into()
    values();
    end if;
    
    if :paramer2 is not null then
    insert into()
    values();
    end if;
    Hope this helps

    Hamid

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points