preetha00 Posted April 27, 2009 Share Posted April 27, 2009 Can we not include more than one IN operator with parameter in a query.I am using jasper report with ireportI am trying the following querySELECT 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_level1WHERE (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? Link to comment Share on other sites More sharing options...
preetha00 Posted April 28, 2009 Author Share Posted April 28, 2009 please let me know how i can make that query work in jasper report. Link to comment Share on other sites More sharing options...
dnvsrikanth Posted April 28, 2009 Share Posted April 28, 2009 Hi Instead of declaring the parameters as Collection datatype declare them as String datatype itself.... DNV Srikanth. Link to comment Share on other sites More sharing options...
preetha00 Posted April 29, 2009 Author Share Posted April 29, 2009 Thnks i changed those types to string itself...Now my query looks something like thisSELECT 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_level1WHERE (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 errornet.sf.jasperreports.engine.JRExceptionat 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 Link to comment Share on other sites More sharing options...
dnvsrikanth Posted April 29, 2009 Share Posted April 29, 2009 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 Link to comment Share on other sites More sharing options...
preetha00 Posted April 29, 2009 Author Share Posted April 29, 2009 The problem is the parameters...in the report i need optional parameters also which i rectify using teh case when featureactually 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 thisSELECT 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_level1WHERE (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. Link to comment Share on other sites More sharing options...
dnvsrikanth Posted April 29, 2009 Share Posted April 29, 2009 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 Link to comment Share on other sites More sharing options...
Teodor Danciu Posted April 30, 2009 Share Posted April 30, 2009 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 Link to comment Share on other sites More sharing options...
rvasi Posted April 15, 2011 Share Posted April 15, 2011 dnvsrikanthWrote: 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now