468,550 Members | 2,390 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,550 developers. It's quick & easy.

sql query in ireport

I am using a sql query in ireport to make a report based on two tables.I have 2 tables ORDER and ORDER_LINE.Orders contains orders of seeds ordered by different requesters.

ORDER:Order_ID,Seed_ID,quantity(here orderid and seedid are composite primary key and one order may contain requests of multiple seeds by the same requester)
ORDER_LINE:Order_ID,Req_ID,Date of order,no.of seeds

Now i have to show all this information on a single report.
I am using ireport and i m new to this tool.The query i have made is like this:
Expand|Select|Wrap|Line Numbers
  1. Select orders.Order_ID AS "Order_ID",orders.quantity AS "quantity",orders.Seed_ID AS "Seed_ID",order_line.R_ID AS "R_ID",order_line.Date_order AS "date",order_line.no_of_seeds AS "no. of seeds",requesters.R_NAME AS "Req" from orders,order_line,requesters where orders.Order_ID=order_line.Order_ID AND order_line.R_ID=requesters.R_ID;
  2.  
The query is working but i want to display the Seed_ID's 0f one order separated by comma .I have use group_concat() but it shows then only one order and displays all the seed id's form the table in one row separated by comma.I need help for this query.I think my query for group_concat is not right.
Jun 22 '12 #1

✓ answered by Luuk

The query is working but i want to display the Seed_ID's 0f one order separated by comma
You can only do this if you use 'GROUP BY Order_ID'

But how will you show 'quantity' for this seeds?
Should it show the total quantity?

BTW, if you format your query its more readible:
Expand|Select|Wrap|Line Numbers
  1. Select 
  2.     orders.Order_ID AS "Order_ID",
  3.     orders.quantity AS "quantity",
  4.     orders.Seed_ID AS "Seed_ID",
  5.     order_line.R_ID AS "R_ID",
  6.     order_line.Date_order AS "date",
  7.     order_line.no_of_seeds AS "no. of seeds",
  8.     requesters.R_NAME AS "Req" 
  9. from orders,order_line,requesters 
  10. where 
  11.     orders.Order_ID=order_line.Order_ID 
  12.     AND order_line.R_ID=requesters.R_ID; 
  13.  
  14.  

3 4057
Luuk
1,043 Expert 1GB
The query is working but i want to display the Seed_ID's 0f one order separated by comma
You can only do this if you use 'GROUP BY Order_ID'

But how will you show 'quantity' for this seeds?
Should it show the total quantity?

BTW, if you format your query its more readible:
Expand|Select|Wrap|Line Numbers
  1. Select 
  2.     orders.Order_ID AS "Order_ID",
  3.     orders.quantity AS "quantity",
  4.     orders.Seed_ID AS "Seed_ID",
  5.     order_line.R_ID AS "R_ID",
  6.     order_line.Date_order AS "date",
  7.     order_line.no_of_seeds AS "no. of seeds",
  8.     requesters.R_NAME AS "Req" 
  9. from orders,order_line,requesters 
  10. where 
  11.     orders.Order_ID=order_line.Order_ID 
  12.     AND order_line.R_ID=requesters.R_ID; 
  13.  
  14.  
Jun 22 '12 #2
No i dont want total quantity .Thanku for reply.Got it solved and sorry for inconvenience
Jun 22 '12 #3
Can anyone tell me about basic tutorial on charts in ireport
Jun 22 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Raj | last post: by
4 posts views Thread by Robertu | last post: by
reply views Thread by anuc | last post: by
ddtpmyra
1 post views Thread by ddtpmyra | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.