Multiple IN operators in a report

[preetha00]

Can we not include more than one IN operator with parameter in a query.

I am using jasper report with ireport

I am trying the following query

SELECT
     invoice.`INVOICE_DATE` AS Date,
     invoice.`TILL_NO` AS Till_No,
     time(invoice.CREATED_ON)as "Time",
     concat(user.first_name," ",user.last_name)as "Cashier",
     payment_details.`SYS_INVOICE_NO` AS Invoice_No,
     invoice.`total_amount`,
     tender_type_level1.`sub_tender1_name` AS Type_of_Coupen,
     payment_details.`coup_denomination`,
     payment_details.`coup_qty`,
     payment_details.`coup_amount_paid`
FROM
    
     `user` user,
     `invoice` invoice,
     `payment_details` payment_details,
     `tender_type_level1` tender_type_level1
WHERE
     (invoice.SYS_INVOICE_NO = payment_details.SYS_INVOICE_NO)
     and (invoice.CASHIER_ID = user.USER_ID)
     and payment_details.sub_tender1_id = tender_type_level1.sub_tender1_id
     and (invoice.BILL_VOID_FLG IS null
     and payment_details.PAYMENT_VOID_FLG = 'N'
     and payment_details.TENDER_ID = 'CP')
     AND invoice.cashier_id=(case when($P{cashierId})! = '' then($P{cashierId})else invoice.cashier_id end)
 AND invoice.invoice_date >= date($P{fromDate})
 AND invoice.invoice_date <= date($P{toDate})
 AND $X{IN,invoice.TILL_NO,tillNo} 
 AND $X{IN ,tender_type_level1.sub_tender1_name,typeOfCoupon}
 AND $X{IN,invoice.sys_invoice_no,invoiceNo}

when i give preview for the same i get this error

com.jaspersoft.ireport.designer.errorhandler.ProblemItem@1b4e7ae	Cannot cast from Collection to String	net.sf.jasperreports.engine.design.JRDesignExpression@162bdd8
com.jaspersoft.ireport.designer.errorhandler.ProblemItem@1d690fb	Cannot cast from Collection to String	net.sf.jasperreports.engine.design.JRDesignExpression@17ff60e

 How can that be rectified?

[preetha00]

please let me know how i can make that query work in jasper report.

 

[dnvsrikanth]

Hi

 

Instead of declaring the parameters as Collection datatype declare them as String datatype itself....

 

DNV Srikanth.

[preetha00]

 

Thnks i changed those types to string itself...

Now my query looks something like this

SELECT
     invoice.`INVOICE_DATE` AS Date,
     invoice.`TILL_NO` AS Till_No,
     time(invoice.CREATED_ON)as "Time",
     concat(user.first_name," ",user.last_name)as "Cashier",
     payment_details.`SYS_INVOICE_NO` AS Invoice_No,
     invoice.`total_amount`,
     tender_type_level1.`sub_tender1_name` AS Type_of_Coupen,
     payment_details.`coup_denomination`,
     payment_details.`coup_qty`,
     payment_details.`coup_amount_paid`
FROM
    
     `user` user,
     `invoice` invoice,
     `payment_details` payment_details,
     `tender_type_level1` tender_type_level1
WHERE
     (invoice.SYS_INVOICE_NO = payment_details.SYS_INVOICE_NO)
     and (invoice.CASHIER_ID = user.USER_ID)
     and payment_details.sub_tender1_id = tender_type_level1.sub_tender1_id
     and (invoice.BILL_VOID_FLG IS null
     and payment_details.PAYMENT_VOID_FLG = 'N'
     and payment_details.TENDER_ID = 'CP')
     AND invoice.invoice_date >= date($P{fromDate})
 AND invoice.invoice_date <= date($P{toDate})
 AND invoice.TILL_NO in ($P!{tillNoValuated})
 AND tender_type_level1.sub_tender1_name IN ($P!{typeOfCouponValuated})
 AND invoice.sys_invoice_no IN (case when ($P{invoiceNovaluated}) != '' then ($P!{invoiceNovaluated}) else invoice.sys_invoice_no end)
AND invoice.cashier_id=(case when ($P{cashierId}) != '' then ($P{cashierId}) else invoice.cashier_id end)
AND invoice.store_id in (case when ($P{storeIdsValuated}) != '' then ($P!{storeIdsValuated}) else invoice.store_id end)

 but now when i try to execute the query i get an error

net.sf.jasperreports.engine.JRException

at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(

at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(

at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(

at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(

at net.sf.jasperreports.engine.fill.JRFiller.fillReport(

at net.sf.jasperreports.engine.JasperFillManager.fillReport(

at net.sf.jasperreports.engine.JasperFillManager.fillReportToFile(

at net.sf.jasperreports.engine.JasperFillManager.fillReportToFile(

at src.JasperReportTest.runreport(

at src.JasperReportTest.main(

Caused by:

at com.mysql.jdbc.MysqlIO.checkErrorPacket(

at com.mysql.jdbc.MysqlIO.sendCommand(

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(

at com.mysql.jdbc.Connection.execSQL(

at com.mysql.jdbc.PreparedStatement.executeQuery(

at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135)

: Error executing SQL statement for : Test ReportJRJdbcQueryExecuter.java:141)JRFillDataset.java:671)JRFillDataset.java:591)JRBaseFiller.java:1212)JRBaseFiller.java:842)JRBaseFiller.java:791)JRFiller.java:63)JasperFillManager.java:402)JasperFillManager.java:188)JasperFillManager.java:94)JasperReportTest.java:57)JasperReportTest.java:87)java.sql.SQLException: Operand should contain 1 column(s)MysqlIO.java:2001)MysqlIO.java:1168)MysqlIO.java:1279)Connection.java:2281)PreparedStatement.java:1634)

I have bolded the part of the text that i feel that is the reason for the error, though i am not sure why it is wrong. please help.

Post Edited by preetha00 at 04/29/2009 04:27

[dnvsrikanth]

Hi,

 

If am not wrong, the parameters to the query are not correctly being passed. Check whether they are correctly passed or not...

 

Or else check the no. of columns in the JRDataSource....

 

Still if the problem persists lets check abt the other stuff..

 

DNV Srikanth

[preetha00]

The problem is the parameters...in the report i need optional parameters also which i rectify using teh case when feature

actually that query works fine if i am changing all the parameters as mandatory parameters.....but i require the optional parameters also...

so if my query is like this

SELECT
     invoice.`INVOICE_DATE` AS Date,
     invoice.`TILL_NO` AS Till_No,
     time(invoice.CREATED_ON)as "Time",
     concat(user.first_name," ",user.last_name)as "Cashier",
     payment_details.`SYS_INVOICE_NO` AS Invoice_No,
     invoice.`total_amount`,
     tender_type_level1.`sub_tender1_name` AS Type_of_Coupen,
     payment_details.`coup_denomination`,
     payment_details.`coup_qty`,
     payment_details.`coup_amount_paid`
FROM
    
     `user` user,
     `invoice` invoice,
     `payment_details` payment_details,
     `tender_type_level1` tender_type_level1
WHERE
     (invoice.SYS_INVOICE_NO = payment_details.SYS_INVOICE_NO)
     and (invoice.CASHIER_ID = user.USER_ID)
     and payment_details.sub_tender1_id = tender_type_level1.sub_tender1_id
     and (invoice.BILL_VOID_FLG IS null
     and payment_details.PAYMENT_VOID_FLG = 'N'
     and payment_details.TENDER_ID = 'CP')
     AND invoice.invoice_date >= date('2009-04-01')
 AND invoice.invoice_date <= date('2009-04-29')
 AND invoice.TILL_NO in ($P!{tillNoValuated})
 AND tender_type_level1.sub_tender1_name IN ($P!{typeOfCouponValuated})
 AND invoice.sys_invoice_no IN ($P!{invoiceNovaluated})
AND invoice.cashier_id=(case when ($P{cashierId}) != '' then ($P{cashierId}) else invoice.cashier_id end)
AND invoice.store_id in ($P!{storeIdsValuated})

i get the report generated when i make all the params mandatory.

[dnvsrikanth]

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

[teodord]

Hi,

 

This should work, but you should attach the entire JRXML so that we can see the types of parameters involved.

Also, you should specify the version of iR you are using.

 

Thank you,

Teodor

 

[rvasi]

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