dnvsrikanth Wrote: hi, Ok manage the optional fields either in java or in sql so that exception wont occur, like use if condition whether it is null or not. If it is null do something. if it is not null paas the the parameter.... I use the same... DNV Srikanth Code:hi i have situation in my project to pass optional parameters Below is the sql which i am doing but it's not working properl>in the below query i am passing $p{Artist} as optional parameter but when i give null or whn artist name given it was generating same report.I think it was not picking up the condition.can anyone tell where am going wrong. I am attaching the jrxml file also.Thanks in advance Selectlegacy_artist.artist_name as Artist,AH.art_piece_id,AH.art_piece_title as ArtPiece_Title,AH.day_number||'/'||AH.Lot_Number as Day_lot,apc_medium.apc_medium_desc as medium,MIN(po_receipt.date_received) AS FirstReciept,MAX (po_receipt.date_received) AS lastreciept,Sales.Auctioneer as Auctioneer,Sales.Voyagecode,Sales.Date_Sold as Date_Sold,Sales.Vessel,NVL(Sales.SoldQty,0) as Sold_Qty,Sales.price_Sold As Price_Sold,Sales.Reserve_price As Reserve_price,Sales.ArtCost as Art_Cost,Sales.CostWD As Art_Cost_with_WDFrom Legacy_Sales_HeaderInner Join Legacy_Sales_Line_ItemOn Legacy_Sales_Header.L_Invoice_Number=Legacy_Sales_Line_Item .L_Invoice_NumberAnd Legacy_Sales_Header.L_Vessel_Id=Legacy_Sales_Line_Item.L_Vessel_IdAnd Legacy_Sales_Header.L_Manual_Invoice=Legacy_Sales_Line_Item.L_Manual_InvoiceLeft Outer Join Apc_Header AHOn Legacy_Sales_Line_Item.L_Item_Id =AH.Art_Piece_IdInner join apc_mediumon AH.apc_medium_id=apc_medium.apc_medium_idInner Join LEGACY_ARTISTon AH.artist_id=legacy_artist.artist_idinner join po_receipt onAH.art_piece_id = po_receipt.art_piece_idInner join Legacy_Auction_HeaderOn Legacy_Auction_Header.l_auction_id = Legacy_Sales_Header.l_auction_idand Legacy_Auction_Header.l_vessel_id= Legacy_Sales_Header.L_Vessel_IdInner join Legacy_Vesselon Legacy_Auction_Header.l_vessel_id=Legacy_Vessel.l_Vessel_idInner Join Legacy_VoyageOn Legacy_Voyage.l_voyage_Code=Legacy_Auction_Header.l_Voyage_Codeand Legacy_Voyage.L_start_date=Legacy_Auction_Header.L_Voyage_start_dateand Legacy_Voyage.l_vessel_id =Legacy_Auction_Header.l_vessel_idLeft Outer join(Select Legacy_Sales_Line_Item.L_Item_Id,legacy_art_director.l_ad_name as Auctioneer,Legacy_auction_header.l_auction_timestamp as Date_Sold,legacy_auction_header.l_voyage_code as VoyageCode,legacy_auction_header.l_vessel_id as Vessel,Count(Legacy_Sales_Line_Item.L_Item_Id) As SoldQty, Legacy_Sales_Line_Item.l_Price as Price_Sold,legacy_sales_line_item.l_reserve_price as Reserve_price,NVL(Legacy_Invoice_Art.ArtCost_At_SALE,0)+NVL(Legacy_Invoice_Art.Signing_Cost_At_Sale,0)+NVL(Legacy_Invoice_Art.Embellish_Cost_At_Sale,0) AS ARTCOST,NVL(Legacy_Invoice_Art.ArtCost_At_SALE,0)+NVL(Legacy_Invoice_Art.Signing_Cost_At_Sale,0)+NVL(Legacy_Invoice_Art.Embellish_Cost_At_Sale,0)-NVL(Legacy_Invoice_Art.Writedown_At_Sale,0) AS COSTWD From Legacy_Sales_Line_Item Inner Join Legacy_Sales_Header On Legacy_Sales_Header.L_Manual_Invoice = Legacy_Sales_Line_Item.L_Manual_Invoice And Legacy_Sales_Header.L_Vessel_Id = Legacy_Sales_Line_Item.L_Vessel_Id And Legacy_Sales_Header.L_Invoice_Number = Legacy_Sales_Line_Item.L_Invoice_Number Inner Join Legacy_Invoice_Art On Legacy_Sales_Header.L_Manual_Invoice = Legacy_Invoice_Art.Manual_Invoice And Legacy_Sales_Header.L_Vessel_Id = Legacy_Invoice_Art.Vessel_Id And Legacy_Sales_Header.L_Invoice_Number = Legacy_Invoice_Art.Invoice_Number Inner Join Apc_Header On Legacy_Sales_Line_Item.l_item_id= Apc_Header.Art_Piece_Id and Legacy_Invoice_Art.Item_ID= Apc_Header.Art_Piece_Id Inner join Legacy_Auction_Header On Legacy_Auction_Header.l_auction_id = Legacy_Sales_Header.l_auction_id and Legacy_Auction_Header.l_vessel_id= Legacy_Sales_Header.L_Vessel_Id Inner join Legacy_Vessel on Legacy_Auction_Header.l_vessel_id=Legacy_Vessel.l_Vessel_id Inner Join Legacy_Voyage On Legacy_Voyage.l_voyage_Code=Legacy_Auction_Header.l_Voyage_Code and Legacy_Voyage.L_start_date=Legacy_Auction_Header.L_Voyage_start_date and Legacy_Voyage.l_vessel_id =Legacy_Auction_Header.l_vessel_id Inner join legacy_art_director On legacy_art_director.l_ad_id = Legacy_auction_header.l_ad_id Where Legacy_Sales_Header.L_Status in('Shipped') and legacy_auction_header.l_auction_timestamp >= To_Date('11/01/2010','MM/dd/yyyy') AND legacy_auction_header.l_auction_timestamp<= To_Date('11/30/2010','MM/dd/yyyy') --and L_ITEM_ID=4180 and Apc_Header.Art_Piece_Id >=100 And Apc_Header.Frame=0 and APC_Header.Apply_BP=1 GROUP BY Legacy_Sales_Line_Item.L_Item_Id, legacy_art_director.l_ad_name, Legacy_auction_header.l_auction_timestamp, legacy_auction_header.l_voyage_code, legacy_auction_header.l_vessel_id, Legacy_Sales_Line_Item.l_Price, legacy_sales_line_item.l_reserve_price,NVL(Legacy_Invoice_Art.ArtCost_At_SALE,0)+NVL(Legacy_Invoice_Art.Signing_Cost_At_Sale,0)+NVL(Legacy_Invoice_Art.Embellish_Cost_At_Sale,0), NVL(Legacy_Invoice_Art.ArtCost_At_SALE,0)+NVL(Legacy_Invoice_Art.Signing_Cost_At_Sale,0)+NVL(Legacy_Invoice_Art.Embellish_Cost_At_Sale,0)-NVL(Legacy_Invoice_Art.Writedown_At_Sale,0))Sales On Ah.Art_Piece_Id = Sales.L_Item_Idwherelegacy_auction_header.l_auction_timestamp >= To_Date('11/01/2010','MM/dd/yyyy')AND legacy_auction_header.l_auction_timestamp<= To_Date('11/30/2010','MM/dd/yyyy')--and ah.art_piece_id=4180and legacy_artist.artist_name=(case when ($P{Artist})! = '' then ($P{Artist}) else legacy_artist.artist_name end)GROUP BY legacy_artist.artist_name, AH.art_piece_id, AH.art_piece_title, AH.day_number||'/'||AH.Lot_Number, apc_medium.apc_medium_desc, Sales.Auctioneer, Sales.Voyagecode, Sales.Date_Sold, Sales.Vessel, NVL(Sales.SoldQty,0), Sales.price_Sold, Sales.Reserve_price, Sales.ArtCost, Sales.CostWDorder by Artist Desc
