db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From George H <george....@gmail.com>
Subject Re: Query issue
Date Wed, 25 Feb 2009 14:31:01 GMT
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