You're right. -- George H [email protected]
On Wed, Feb 25, 2009 at 2:50 PM, Rick Hillegas <[email protected]> wrote: > 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 >> [email protected] >> >> >> >> On Wed, Feb 25, 2009 at 2:15 PM, Rick Hillegas <[email protected]> >> 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 >>>> >>>> >>>> >>>> ------------------------------------------------------------------------------------- >>>> >>> >>> > >
