Home » Developer & Programmer » Reports & Discoverer » pass parameters (report builder 6)
icon4.gif  pass parameters [message #556212] Thu, 31 May 2012 22:34 Go to next message
fadhzone
Messages: 61
Registered: April 2008
Member
Hi All,

I have a problem here.
Normally, we use &p_where inside a sql script in condition section
e.g :
select name from member where name like 'a%' &p_where order by name;


may i use this kind of parameter in table section?
e.g :
select name from &p_table where name like 'a%' and status = 'a' order by name;


the reason i need to do is there are 2 different server. but i need retrieve same info.server ABC have table A but don't have table B and server DEF have table B but don't have table A.

Is there any other method to solve this problem?

Thanks.
Re: pass parameters [message #556218 is a reply to message #556212] Thu, 31 May 2012 23:59 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't use Reports Builder 6i, but - in its 10g version - yes, it is possible (by the way, how come you didn't try it already?). In Help, "About lexical references" section says this:
Oracle

Example 2: FROM clause
SELECT ORDID, TOTAL
FROM &ATABLE;

ATABLE can be used to change the table from which columns are selected at runtime. For example, you could enter ORD for ATABLE at runtime (...)
Re: pass parameters [message #556231 is a reply to message #556218] Fri, 01 June 2012 01:29 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
Thanks Littlefoot for the response. But my company is still using report builder 6i.Already tried but there was an error prompt out.
This is my code :

SELECT  SS.ART_CD,
        D.SKU_CD,
        S.SKU_DESC,
        D.UNIT_COST,
        D.UNIT_RETAIL,
        SUM(D.RCV_ORD_QTY),
        SUM(D.RCV_FOC_QTY),
        SUM((D.RCV_ORD_QTY + D.RCV_FOC_QTY)) QTY        
FROM    TSUB_GOODS_RECEIVED_HDR H,TSUB_GOODS_RECEIVED_DET D,SKU S,&P_TABLE SS
WHERE   H.STORE_CD  = D.STORE_CD
AND     H.SLIP_NO   = D.SLIP_NO
AND     D.SKU_CD    = S.SKU_CD
&P_COND
AND     H.STORE_CD  = :P_STORE_CD
AND     SS.ART_CD   = :P_ART_CD
AND     TRUNC(H.RECEIVED_DATE) >= :P_DATE_FR
AND     TRUNC(H.RECEIVED_DATE) <= :P_DATE_TO
&P_WHERE
--AND     STATUS = 'A'
GROUP BY 
        D.SKU_CD,
        S.SKU_DESC,
        D.UNIT_COST,
        D.UNIT_RETAIL;


And the error was ORA - 00942 TABLE OR VIEW DOES NOT EXIST
Re: pass parameters [message #556233 is a reply to message #556231] Fri, 01 June 2012 01:36 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you read your Reports Builder's Help? What does it say? Does it support what we discussed?

Try to create a whole FROM clause, i.e. don't put just one table into &P_TABLE, but all of them, such as:
:p_table := ' from tsub_goods_received_hdr, h, tsub_goods_received_det d, sku s, ' || :your_table_here || ' ss';


Alternatively, a simple solution would be to create a view (or a synonym) in both schemas. For example:
connect user_a
create synonym s_my_table for table_a;

connect user_b
create synonym s_my_table for table_b;

Then, you'd use synonym name in report's query:
select ...
from TSUB_GOODS_RECEIVED_HDR H,TSUB_GOODS_RECEIVED_DET D,SKU S,
     s_my_table ss                                                     --> here!
where ...
Re: pass parameters [message #556235 is a reply to message #556233] Fri, 01 June 2012 02:00 Go to previous message
fadhzone
Messages: 61
Registered: April 2008
Member
Already tried the first suggestion you gave me. Still could not. But at least you had give me the ideas to solve this.
I'll try another way.
Thanks littlefoot for your kind help..
Previous Topic: Skips one extra page after the last page using ORARRP
Next Topic: Reports Parameter Form Questions - Fields Missing
Goto Forum:
  


Current Time: Thu Mar 28 16:09:18 CDT 2024