1. gary
  2. Valentina Studio
  3. Wednesday, May 23 2018, 07:24 PM
  4.  Subscribe via email
I have a report which the query result different from the actual output.

Check the screen shot.
The query result show that in correct order: itemCode: 0001 -> 0013
But when the actual report output, the order is incorrect: 0013 -> 0001

What's wrong there???

Thanks in advance


regards,
Gary
Attachments (2)
Comment
There are no comments made yet.
gary Accepted Answer
The query is simple:


SELECT c.branchName, c.branchAddress, c.branchGSTNO, c.branchRegisterNO, b.*,
a.itemCode, a.itemUPrice, a.itemQty, a.itemSubTotal, a.itemDiscount / (a.itemQty * a.itemUPrice) * 100 AS itemDiscountRate,
CONCAT(CONCAT(CONCAT(a.itemDescription,' ('), a.itemUOM),')') as itemDetail,
CONCAT(CONCAT(a.itemQty, 'x'),FORMAT(a.itemUPrice,2)) AS itemUnitDetail,
a.itemGSTCode, a.itemGSTRate,
FORMAT(a.itemDiscount,2) itemDiscount,
d.staffCode, d.staffName
FROM pointofsalesdtl a
INNER JOIN pointofsales b ON a.documentNO=b.documentNO
INNER JOIN branch c ON b.branchID=c.branchID
INNER JOIN staff d ON d.staffCode=b.salesAgentCode
WHERE a.documentNO='POS-000094'
ORDER BY a.rowNO;



a.rowNO is sorting key. itemCode 0001 is 1, and itemCode 0013 is 2. So it is obvious that 0001 should be listed first, but why the actual output is 0013 first????

Report design screenshot is problem-3.png
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
gary Accepted Answer
Mysql query browser show that:
rowNO = 1, ItemCode 0001
rowNO = 2, itemCode 0013
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
gary Accepted Answer
Alright, I check the report again, I found I have set the grouping on itemQty, once I remove the grouping, it works.
But I am not sure the problem is entirely solved or not. Any idea?
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Sergey Pashkov Accepted Answer
Hello Gary,

Grouping by itemQty results in sorting by this field.
So you removed grouping and get the order from the query result
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
gary Accepted Answer
Thanks Mr Sergey, now I am quite assure my problem is solved. Thanks again
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
Michael Ayres Accepted Answer
Similar problem. Is the UI box for adding grouping and sorting liked to the query used in a report? Also, I cannot remove the sort by or group by items in the GUI.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 6
Sergey Pashkov Accepted Answer
Hello Michael,

To remove sorting or grouping it is necessary to select an empty string instead of the field name.

That's how grouping and sorting is applied to the initial query:
SELECT * FROM ( initial_sql ) ORDER BY fldToSort
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 7
Nice one this type of topic very helpful
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 8
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.