Skip to Content
0
Nov 04, 2011 at 07:43 PM

Code Break for WEBI report

23 Views

Could someone help me in the following code which is in Custom SQL. In the code in the From Clause there is a code which acts as Table. How can I achieve this in WEBI as a single code.

 SELECT DISTINCT 
  PENDETAIL.TRADE, 
  PENDETAIL.PRODUCT, 
  PENTRANSACT.ACCTDATE, 
  PENDETAIL.PENTRANSACT, 
  PENTRANSACT.SAP_DOCUMENT_NO, 
  PENDETAIL.ACCOUNT, 
  Iowa.SAPAccountCode(PENDETAIL.ACCOUNT) sapact, 
  PENDETAIL.COUNTERPARTY, 
  PENDETAIL.QUANTITY,    
  PENDETAIL.CREDIT, 
  PENDETAIL.DEBIT, 
  SHIPMENTPENDTL.VESSEL, 
  PENDETAIL.DESCRIPTION, 
  (( PENDETAIL.CREDIT ) - ( PENDETAIL.DEBIT )) amt, 
  COUNTERPARTY_PENCP.SIC, 
  Iowa.SAPProfitCenter(PENDETAIL.ACCOUNT) a, 
  PENTRANSACT.COMPANY, 
  PENDETAIL.SHIPMENT, 
  PENDETAIL.SUBLEDGER, 
  NVL(PENDETAIL.CONTRACT,DEV_GROSS_LENGTH_DETAIL.CONTRACT), 
  PENDETAIL.PENDETAIL, 
  PENDETAIL.QUANTITYSTATUS, 
  PENDETAIL.QUALITY 
FROM 
  PENDETAIL, 
  PENTRANSACT, 
  SHIPMENT  SHIPMENTPENDTL, 
  COUNTERPARTY  COUNTERPARTY_PENCP, 
  (Select distinct shipment, contract from PENdetail where contract is not null) DEV_GROSS_LENGTH_DETAIL 
WHERE 
  ( PENTRANSACT.PENTRANSACT=PENDETAIL.PENTRANSACT  ) 
  AND  ( SHIPMENTPENDTL.SHIPMENT(+)=PENDETAIL.SHIPMENT  ) 
  AND  ( PENDETAIL.SHIPMENT = DEV_GROSS_LENGTH_DETAIL.SHIPMENT) 
  AND  ( COUNTERPARTY_PENCP.COUNTERPARTY=PENTRANSACT.COMPANY  ) 
  AND  ( 
  PENTRANSACT.ACCTDATE  BETWEEN  @variable('Enter the Beginning Date (MM/DD/YYYY)') AND @variable('Enter the Ending Date (MM/DD/YYYY)') 
  AND  Iowa.SAPAccountCode(PENDETAIL.ACCOUNT)  IN  ('30110075', '30115075', '40110075', '40115075') 
  AND  PENTRANSACT.COMPANY  IN  @variable('Enter Company Name') 
  )

In the code the difference is in the FROM Clause which acts a table:

 (Select distinct shipment, contract from PENdetail where contract is not null) DEV_GROSS_LENGTH_DETAIL

In Where clause:

AND  ( PENDETAIL.SHIPMENT = DEV_GROSS_LENGTH_DETAIL.SHIPMENT)

Select Clause:

 NVL(PENDETAIL.CONTRACT,DEV_GROSS_LENGTH_DETAIL.CONTRACT), 

For the little code which acts as table ,I think there is no need of derived table I guess.