| SQL for Discoverer [message #373619] |
Fri, 27 April 2001 03:42  |
Robert Angel
Messages: 8
Registered: March 2001
|
Junior Member |
|
|
I have written the following piece of sql, which works just fine, but I need to make it available to users (and convert the date parameter (01-NOV-00) so that it always gives a six months old slice) and make it available through discoverer.
Do I need to save it as a view, will this work with the parameter, and how do I then deal with the admin side in discoverer?
SQL:
SPOOL m:\fso\sql\script\ap\bacs.lst
SET PAGESIZE 10000
SET ECHO OFF
SET LINESIZE 550
SEt FEEDBACK ON
SET HEAD OFF
CLEAR BREAKS
SET TERMOUT OFF
SET TAB OFF
SET TRIMOUT OFF
SET WRAP OFF
SET COLSEP " "
SET TRIMSPOOL ON
--
--
column Name format a35
column "Vendor Number" format a7
column "Vendor Site" format a35
column AD1 format a30
column AD2 format a30
column AD3 format a30
column CITY format a20
column Postal_code format a7
--
--
Select vendor_name "Name",
segment1 "Vendor Number",
s.vendor_id,
s.vendor_site_id,
vendor_site_code "Vendor Site",
ADDRESS_LINE1 AD1,
ADDRESS_LINE2 AD2,
ADDRESS_LINE3 AD3,
CITY,
ZIP Postal_Code,
AREA_CODE,
PHONE,
FAX,
TELEX,
s.Bank_Account_name,
s.Bank_account_num,
s.bank_num,
s.inactive_date,
s.pay_site_flag "Pay",
s.purchasing_site_flag "Purchase",
v.payment_method_lookup_code,
v.vendor_type_lookup_code
--
--
from po_vendor_sites s,
po_vendors v
--
--
where v.vendor_id = s.vendor_id
-- and segment1 = '10000'
and v.vendor_type_lookup_code ='TRADE'
and v.payment_method_lookup_code = 'EFT'
and s.inactive_date is null
and s.pay_site_flag = 'Y'
and s.vendor_id||s.vendor_site_id in
(select vendor_id||vendor_site_id
from ap_invoices_all
where invoice_date > '01-NOV-00')
/
Any words of wisdom , if only incomplete, gratefully received,
Robert.
|
|
|
|
| Re: SQL for Discoverer [message #373630 is a reply to message #373619] |
Fri, 27 April 2001 13:09  |
Dawn
Messages: 10
Registered: April 2001
|
Junior Member |
|
|
In order for Discoverer to be able to use it yes you will have to save it as a view. Once the view is created and you grant the necessary rights then in the admin portion of discoverer all you need to do is import that "business area"/view so that you have a folder that points to the view.
As for passing a parameter that will have to be handled outside of discover.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
