| Subject | Re: [firebird-support] Query Problem |
|---|---|
| Author | Helen Borrie |
| Post date | 2006-08-27T22:47:26Z |
At 06:58 AM 28/08/2006, you wrote:
1. What's the *actual* query that manages to work sometimes?
-- In this one you have 2 grouping arguments (bi.billingid and
bi.Amount) that are not in the SELECT list. Unless you are using
InterBase or Firebird 1.0, that should return exceptions, not results.
2 (related) If bi.billingid is meant to be there, is it a foreign key
to some parent structure? If yes, it's in the wrong place in the
GROUP BY clause.
3. Why DISTINCT? What do expect from including it?
To solve this, it's probably going to avoid the "20 Questions"
rigmarole if you provide the metadata for the (2? 3?) tables involved
and a description of the output you're seeking and mention what
version of Firebird? InterBase? you're using.
./heLen
>I have one table BILLINGITEM, with individual charge lines.Questions first.
>
>For each entry in BILLINGITEM, the PAYMENT table may have 0, 1 or more
>entries.
>
>I want to query the database and for every line in BILLINGITEM, I want to
>get the charge and then the sum of any payments in the payment table.
>Further I want to get this information, as of a certain date.
>
>It seems like a simple outer join issue, however this query is kicking my
>rear... :p
>
>Here is the current version of my query:
>
>SELECT DISTINCT
> BI.BILLINGITEMID,
> BI.AMOUNT * BI.UNITS CHARGES,
> SUM(PM.AMOUNT) CREDITS
>FROM
> BILLINGITEM BI
> LEFT OUTER JOIN PAYMENT PM ON (BI.BILLINGITEMID = PM.BILLINGITEMID)
>WHERE
> (
> BI.BILLINGITEMDATE <= :ASOFDATE AND
> (PM.PAYMENTDATE <= :ASOFDATE OR
> PM.PAYMENTDATE IS NULL)
> )
>GROUP BY
> BI.BILLINGITEMID,
> BI.billingid,
> BI.AMOUNT,
> BI.UNITS
>ORDER BY
> BI.BILLINGITEMID
>
>The problem is that if there are payments *after* the "ASOFDATE" but none
>*before* then that record is not returned at all. :(
>
>If there are no payments at all then the record is returned properly and if
>there are any payments before the as of date then those are also returned
>properly.
>
>Again, if there are no payments before the as of date, but some after, then
>that record is not returned.
>
>*sobs* :(
1. What's the *actual* query that manages to work sometimes?
-- In this one you have 2 grouping arguments (bi.billingid and
bi.Amount) that are not in the SELECT list. Unless you are using
InterBase or Firebird 1.0, that should return exceptions, not results.
2 (related) If bi.billingid is meant to be there, is it a foreign key
to some parent structure? If yes, it's in the wrong place in the
GROUP BY clause.
3. Why DISTINCT? What do expect from including it?
To solve this, it's probably going to avoid the "20 Questions"
rigmarole if you provide the metadata for the (2? 3?) tables involved
and a description of the output you're seeking and mention what
version of Firebird? InterBase? you're using.
./heLen