Code Break for WEBI report

[former_member203645]

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.

[Former Member]

Hi ,

Two options in my opinion.

1.Remove the derived table and bring in an alias table to same SQL.Refer Query1

2.Remove the derive table and perform everything with One instance of the PENDETAIL.I am not clear why you need to query the table again for the below field as query is displaying the same CONTRACT field irrespective of the fact it is has null or non null value .Did i miss anything here?

NVL(PENDETAIL.CONTRACT,DEV_GROSS_LENGTH_DETAIL.CONTRACT),

Second option is better as my opinion .

Query 1

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,PENDETAIL_NOTNULL.CONTRACT),
  PENDETAIL.PENDETAIL,
  PENDETAIL.QUANTITYSTATUS,
  PENDETAIL.QUALITY
FROM PENDETAIL,
  PENTRANSACT,
  SHIPMENT SHIPMENTPENDTL,
  COUNTERPARTY COUNTERPARTY_PENCP,
  PENDETAIL PENDETAIL_NOTNULL
WHERE ( PENTRANSACT.PENTRANSACT      =PENDETAIL.PENTRANSACT )
AND ( SHIPMENTPENDTL.SHIPMENT(+)     =PENDETAIL.SHIPMENT )
AND ( PENDETAIL.SHIPMENT             = PENDETAIL_NOTNULL.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') )
AND PENDETAIL_NOTNULL.contract             IS NOT NULL

Regards,

Bilahari