db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Query issue
Date Wed, 25 Feb 2009 16:30:09 GMT

Just a shot in the dark...

How is he handling null values in certain columns? 
Wouldn't that account for some of his 'strange behavior'?

I'm sorry, but is it just me or does anyone else get sick of the '... it
works this way in xxx?' 

No offense but each database has its own quirks and half the fun (and reason
for drinking massive amounts of alcohol and caffeine, although not
necessarily at the same time... :-) is having to deal with slight
differences between your databases.

-Mike

> -----Original Message-----
> From: George H [mailto:george.dma@gmail.com]
> Sent: Wednesday, February 25, 2009 9:10 AM
> To: Derby Discussion
> Subject: Re: Query issue
> 
> You're right.
> --
> George H
> george.dma@gmail.com
> 
> 
> 
> On Wed, Feb 25, 2009 at 2:50 PM, Rick Hillegas <Richard.Hillegas@sun.com>
> 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
> >> 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