1

I have this current query. I have tested it on 2 of the same verions of Jaspersoft Studio. When I hit read fields, one instance of the program gives me the columns I want however the other instance is stuck in a loop. I have checked the error logs and there is nothing for me to troubleshoot.

I had theorized it was the query but why would it work in one instance and not the next.

Where can I look to find out what is happening? What are my options?

SELECT
LOCATIONS.ID AS LOCATIONID,
LOCATIONS.NAME AS LOCATIONNAME,
PRODUCTS.REFERENCE,
PRODUCTS.CODE AS BARCODE,
PRODUCTS.NAME AS ITEM_NAME,
STOCKDIARY.ATTRIBUTESETINSTANCE_ID,
ATTRIBUTESETINSTANCE.DESCRIPTION AS ATTINSTANCEDESC,
PRODUCTS.CATEGORY,
TICKETLINES.UNITS,
CATEGORIES.NAME AS CATEGORYNAME,
STOCKDIARY.DATENEW,
STOCKDIARY.REASON,
SUM(CASE WHEN STOCKDIARY.UNITS <0 AND STOCKDIARY.DATENEW >=  $P{start_date} AND (STOCKDIARY.DATENEW) <= $P{end_date} THEN STOCKDIARY.UNITS ELSE 0 END) AS UNITSOUT,
SUM(CASE WHEN STOCKDIARY.UNITS >=0 AND STOCKDIARY.DATENEW >=  $P{start_date} AND (STOCKDIARY.DATENEW) <= $P{end_date} THEN STOCKDIARY.UNITS ELSE 0 END) AS UNITSIN,
SUM(CASE WHEN STOCKDIARY.DATENEW <= $P{end_date} THEN STOCKDIARY.UNITS ELSE 0 END) AS STOCKTOTALNEW,
SUM(CASE WHEN STOCKDIARY.DATENEW <=  $P{start_date} THEN STOCKDIARY.UNITS ELSE 0 END) AS STARTINGBALANCE,
SUM(TICKETLINES.UNITS) AS UNIT


FROM TICKETLINES,
STOCKDIARY
    LEFT JOIN
LOCATIONS ON STOCKDIARY.LOCATION = LOCATIONS.ID
    INNER JOIN
PRODUCTS ON PRODUCTS.ID = STOCKDIARY.PRODUCT
    LEFT JOIN
CATEGORIES ON PRODUCTS.CATEGORY = CATEGORIES.ID
    LEFT JOIN
ATTRIBUTESETINSTANCE ON STOCKDIARY.ATTRIBUTESETINSTANCE_ID = ATTRIBUTESETINSTANCE.ID


WHERE products.name LIKE "%"

GROUP BY LOCATIONS.NAME , PRODUCTS.REFERENCE , STOCKDIARY.ATTRIBUTESETINSTANCE_ID , ATTRIBUTESETINSTANCE.DESCRIPTION
ORDER BY PRODUCTS.NAME
Alex K
  • 22,315
  • 19
  • 108
  • 236
Robert Farr
  • 680
  • 2
  • 7
  • 19
  • What are versions your tried to use? Looks like a bug of JSS. It is better to create official bug request. – Alex K Oct 12 '16 at 20:06
  • Did you try to use a simple query? Is it ok? – Alex K Oct 12 '16 at 20:07
  • Your issue similar to this one: [In Jaspersoft Studio, Sql server procedure returning fewer fields than expected](http://stackoverflow.com/q/38877249/876298) – Alex K Oct 12 '16 at 20:08
  • @AlexK I'm using Jasper 6.3.1 My confusion is... it worked find until I added the lines "SUM(TICKETLINES.UNITS) AS UNIT" and FROM "TICKETLINES". If I remove those lines it works perfectly and there is no hanging – Robert Farr Oct 12 '16 at 20:51
  • I'm still at a loss... all other queries work perfectly and it works on one installation of Jasper but not the other. Should I simply remove the installation and try again? – Robert Farr Oct 12 '16 at 20:55
  • `Should I simply remove the installation and try again?` - You can try this, but I don't believe that it will help you. `it worked find until I added the lines "SUM(TICKETLINES.UNITS) AS UNIT"` - Try to rename the alias **UNIT**, just a crazy idea ;) – Alex K Oct 12 '16 at 22:28
  • Is `TICKETLINES` just a simple table? – Alex K Oct 12 '16 at 22:30
  • @AlexK I tried renaming the alias on the machine that it's not working on. Does not work. It is a simple table. I changed the types of JOINS and it picked up the columns fine but now the data is empty. So I believe I will have to look at fixing the query instead. – Robert Farr Oct 13 '16 at 01:08

0 Answers0