Adding Sql Statements to Business Objects Report

[Comet304]

I have a BO report that I need to add the sql statemnt to. The SQL staements when I hit the SQL button is
SELECT
NIM_SHIP_INV_LINE.IMPORT_SHIP_NO,
NIM_SHIP_INV_LINE.PARTID,
NIM_SHIP_INV_LINE.HTS_NUMBER1,
PRODUCT.SYNBOLIZATION_NAME,
PRODUCT.PDESC,
NIM_SHIPMENT.DOB,
NIM_SHIPMENT.UNLADING_DATE,
NIM_SHIPMENT.IOR_CD,
NIM_SHIPMENT.MOT
FROM
NIM_SHIP_INV_LINE,
PRODUCT,
NIM_SHIPMENT,
NIM_SHIP_HOLD
WHERE
( NIM_SHIP_INV_LINE.PARTID=PRODUCT.PARTID(+) )
AND ( NIM_SHIP_HOLD.IMPORT_SHIP_NO(+)=NIM_SHIPMENT.IMPORT_SHIP_NO )
AND ( NIM_SHIP_HOLD.SHIP_VERSION(+)=NIM_SHIPMENT.SHIP_VERSION )
AND ( NIM_SHIP_HOLD.SHIP_REC_TYPE(+)=NIM_SHIPMENT.REC_TYPE )
AND ( NIM_SHIP_INV_LINE.IMPORT_SHIP_NO(+)=NIM_SHIPMENT.IMPORT_SHIP_NO )
AND ( NIM_SHIPMENT.REC_TYPE=NIM_SHIP_INV_LINE.SHIP_REC_TYPE(+) )
AND ( NIM_SHIPMENT.SHIP_VERSION=NIM_SHIP_INV_LINE.SHIP_VERSION(+) )
AND (
NIM_SHIP_HOLD.HOLD_NAME = 'HTS_HOLD'
AND NIM_SHIPMENT.REC_TYPE = 'PC'
AND NIM_SHIPMENT.DOB >= @variable('Current Date (Greater Than - mm/dd/yyyy)')
AND NIM_SHIPMENT.DOB <= @variable('Current Date (LessThan - mm/dd/yyyy)')
AND NIM_SHIPMENT.REC_STATUS = 'A'
AND NIM_SHIPMENT.IOR_CD = '''00039062'''
)
I would like to add it to say instead
SELECT
NIM_SHIP_INV_LINE.IMPORT_SHIP_NO,
NIM_SHIP_INV_LINE.PARTID,
NIM_SHIP_INV_LINE.HTS_NUMBER1,
PRODUCT.SYNBOLIZATION_NAME,
PRODUCT.PDESC,
NIM_SHIPMENT.DOB,
NIM_SHIPMENT.UNLADING_DATE,
NIM_SHIPMENT.IOR_CD,
NIM_SHIPMENT.MOT
FROM
NIM_SHIP_INV_LINE,
PRODUCT,
NIM_SHIPMENT,
NIM_SHIP_HOLD
WHERE
( NIM_SHIP_INV_LINE.PARTID=PRODUCT.PARTID(+) )
AND ( NIM_SHIP_HOLD.IMPORT_SHIP_NO(+)=NIM_SHIPMENT.IMPORT_SHIP_NO )
AND ( NIM_SHIP_HOLD.SHIP_VERSION(+)=NIM_SHIPMENT.SHIP_VERSION )
AND ( NIM_SHIP_HOLD.SHIP_REC_TYPE(+)=NIM_SHIPMENT.REC_TYPE )
AND ( NIM_SHIP_INV_LINE.IMPORT_SHIP_NO(+)=NIM_SHIPMENT.IMPORT_SHIP_NO )
AND ( NIM_SHIPMENT.REC_TYPE=NIM_SHIP_INV_LINE.SHIP_REC_TYPE(+) )
AND ( NIM_SHIPMENT.SHIP_VERSION=NIM_SHIP_INV_LINE.SHIP_VERSION(+) )
AND (
NIM_SHIP_HOLD.HOLD_NAME = 'HTS_HOLD'
AND NIM_SHIPMENT.REC_TYPE = 'PC'
AND NIM_SHIPMENT.DOB >= TRUNC(SYSDATE - 1)
AND NIM_SHIPMENT.DOB < TRUNC(SYSDATE) AND NIM_SHIPMENT.REC_STATUS = 'A'
AND NIM_SHIPMENT.IOR_CD = '''00039062'''
)

But I do not know how to add these 2 statements.

 

[DJM99]

So your report currently prompts you for you to enter dates, and you would like it to work on Sysdate...

You will need to amend the universe to create a condition that does it for you. I suspect you have a pre-defined condition you have used in the conditions box. Its this that needs to change.



Darren Marsden

http://www.BIMentors.com

<-Sign up for our BusinessObject® Advice Newsletter

 

[BrianTyler]

I agree with Darren. Ihave found it very useful to build a number of date objects into the universe - todays date, yesterdays date, startof month, lastfriday etc, current financial year - as it avoids the need of complex conditions in many queries.