db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Query issue
Date Wed, 25 Feb 2009 14:15:09 GMT
Hi Mikel,

Could you provide a script which reproduces this problem on Derby?

Thanks,
-Rick

Mikel Irurita wrote:
> Dear,
>
> I have a problem executing this query [1]:
>
> It returns the T.ID value duplicated in P.ID, T.ID y C.ID.
> All the values are correct.
>
> Example of the result in Derby:
>
> id1 2 0 2009-02-24 12:43:49.136 id1 id1 NULL NULL 0 1000006 3.0 37.0 
> id1 189.08
> id1 2 0 2009-02-24 12:43:49.136 id1 id1 NULL NULL 1 1000004 4.0 13.0 
> id1 189.08
>
>
> If I executed this query in other database for example (MySQL, ...) it 
> works fine.
>
> Example of the expected result:
>
> id1 2 0 2009-02-24 12:43:49.136 id2 id3 NULL NULL 0 1000006 3.0 37.0 
> id4 189.08
> id1 2 0 2009-02-24 12:43:49.136 id2 id3 NULL NULL 1 1000004 4.0 13.0 
> id5 189.08
>
> Any suggestions?
>
> Thanks in advance.
>
> Regards,
> Mikel Irurita.
>
> [1] http://rafb.net/p/LeTm7b34.html
>
> -------------------------------------------------------------------------------------

>
>
> SELECT T.ID TI_UUID, T.TICKETID TI_ID, T.TICKETTYPE TI_TYPE, R.DATENEW 
> TI_DATE,  P.ID PEO_ID,
> C.ID BP_ID, C.SEARCHKEY BP_SKEY, C.NAME BP_NAME,
> L.LINE LINE_ID, L.PRODUCT LINE_PRODUCT, L.UNITS LINE_UNITS, L.PRICE 
> LINE_PRICE, TAX.ID TAX_ID,
> SUM(PAY.TOTAL) PAYMENT_TOTAL
>
> FROM RECEIPTS R JOIN TICKETS T ON R.ID = T.ID JOIN TICKETLINES L ON 
> T.ID = L.TICKET
> JOIN TAXES TAX ON L.TAXID = TAX.ID LEFT OUTER JOIN PEOPLE P ON 
> T.PERSON = P.ID
> LEFT OUTER JOIN CUSTOMERS C ON T.CUSTOMER = C.ID JOIN PAYMENTS PAY ON 
> R.ID=PAY.RECEIPT
> WHERE (T.TICKETTYPE = 0 OR T.TICKETTYPE = 1) AND T.STATUS = 0
>
> GROUP BY T.ID, T.TICKETID, T.TICKETTYPE, R.DATENEW,  P.ID,
> C.ID , C.SEARCHKEY, C.NAME,
> L.LINE, L.PRODUCT, L.UNITS, L.PRICE, TAX.ID
>
> -------------------------------------------------------------------------------------

>


Mime
View raw message