Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of george.dma@gmail.com
 designates 64.233.182.188 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type:content-transfer-encoding;
        b=bOp3aTskiYQ7ztRijbU15WOgvCM/oBVpQOfOJmUItb94HuXpzYDKsyJqs2nlV5ENHT
         iHY75Ps2b467FNGi6/VW5tp0EqoADJA5jeFVMSVw92UcSnMFmk8VT8Am3qTUM+yyXf7E
         IhfhPPFOQMDDyI9UF0aSOYnmcM7EH14a3bB2I=
MIME-Version: 1.0
In-Reply-To: <49A55AC5.3050402@sun.com>
References: <49A536EE.70808@openbravo.com> <49A5526D.2050109@sun.com>
	 <de07811a0902250631l63650fb9vf1d32c1cf51a9458@mail.gmail.com>
	 <49A55AC5.3050402@sun.com>
Date: Wed, 25 Feb 2009 15:09:34 +0000
Message-ID: <de07811a0902250709l16a323e2sd70e91b2e9893f2e@mail.gmail.com>
Subject: Re: Query issue
From: George H <george.dma@gmail.com>
To: Derby Discussion <derby-user@db.apache.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

You're right.
--
George H
george.dma@gmail.com


On Wed, Feb 25, 2009 at 2:50 PM, Rick Hillegas <Richard.Hillegas@sun.com> w=
rote:
> 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 bu=
g
> 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 =EF=BB=BFT.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 i=
d1
>>>> 189.08
>>>> id1 2 0 2009-02-24 12:43:49.136 id1 id1 NULL NULL 1 1000004 4.0 13.0 i=
d1
>>>> 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 i=
d4
>>>> 189.08
>>>> id1 2 0 2009-02-24 12:43:49.136 id2 id3 NULL NULL 1 1000004 4.0 13.0 i=
d5
>>>> 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, =C2=A0P.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 =3D T.ID JOIN TICKETLINES L ON =
T.ID
>>>> =3D
>>>> L.TICKET
>>>> JOIN TAXES TAX ON L.TAXID =3D TAX.ID LEFT OUTER JOIN PEOPLE P ON T.PER=
SON
>>>> =3D
>>>> P.ID
>>>> LEFT OUTER JOIN CUSTOMERS C ON T.CUSTOMER =3D C.ID JOIN PAYMENTS PAY O=
N
>>>> R.ID=3DPAY.RECEIPT
>>>> WHERE (T.TICKETTYPE =3D 0 OR T.TICKETTYPE =3D 1) AND T.STATUS =3D 0
>>>>
>>>> GROUP BY T.ID, T.TICKETID, T.TICKETTYPE, R.DATENEW, =C2=A0P.ID,
>>>> C.ID , C.SEARCHKEY, C.NAME,
>>>> L.LINE, L.PRODUCT, L.UNITS, L.PRICE, TAX.ID
>>>>
>>>>
>>>>
>>>> ----------------------------------------------------------------------=
---------------
>>>>
>>>
>>>
>
>