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:50:45 GMT
Hi George,

I see the query. But I don't see the schema (the ddl used to create the 
tables) or the inserts which populate the tables to the point that the 
bug occurs.

Thanks,
-Rick

George H wrote:
> He did, it's footnoted at the bottom of his email as a URL and the
> query is pasted at the bottom too.
> We tend to miss things at the bottom of emails :P
>
> --
> George H
> george.dma@gmail.com
>
>
>
> On Wed, Feb 25, 2009 at 2:15 PM, Rick Hillegas <Richard.Hillegas@sun.com> wrote:
>   
>> 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