Firebird/SQL Query doing my head in.

Forums › IT Pro and developers › Firebird/SQL Query doing my head in.

Acropoli

58 posts

Master Geek

#85141 14-Jun-2011 16:12

For the past 2 days I've been struggling with writing this query to export data from firebird. Here's the structure of the data:

WHENINVOICED                  SALESTAX   REMOTELOCATIONID
04.02.2011, 17:45:41.171   2.22      4
04.02.2011, 17:50:06.359   1.53                  4
04.02.2011, 17:53:57.093   3.33                  4
04.02.2011, 17:54:42.156   0.52                  4
04.02.2011, 17:55:15.406   0.59                 4
04.02.2011, 17:52:43.618   1.57                 9
04.02.2011, 17:54:12.977   3.57                 9
04.02.2011, 17:55:28.930   16.49                5
04.02.2011, 17:58:59.977   11.09                5
04.02.2011, 17:59:10.696   5.83                 5
04.02.2011, 16:57:53.281   1.15                 6
04.02.2011, 16:59:56.296   2.58                 6
04.02.2011, 17:03:03.484   1.75                 6
04.02.2011, 17:12:23.890   3.23                 6
04.02.2011, 17:14:57.859   0.76                 6
04.02.2011, 17:16:11.484   8.90                 6
04.02.2011, 17:17:31.781   0.76                  6

What I want to achieve, is a sum of all SALESTAX figures multiplied by 7.66666666 for each hour.

The code I have so far is:
SELECT sum(salestax * 7.66666666) AS COUNTER FROM INVOICE WHERE wheninvoiced> Current_timeStamp- 1 GROUP BY (EXTRACT(HOUR FROM WHENINVOICED))

This produces:
127.2667
9.506667
44.62
74.82667
177.6367
405.1067
327.1367
121.2867
63.48

This works absolutely perfectly, except this only pulls data for hours where there is data to sum. I instead need it to generate a row for each 24 hours in the day, and place zero's against the hours for which there is no data.

Ideas?

1 | 2 | 3

1573 posts

Uber Geek

#481086 14-Jun-2011 16:37

Does Firebird support derived tables?

If it does:
SELECT
h.Hour as hour,
sum(salestax * 7.66666666) AS COUNTER
FROM
(
  SELECT 0 AS HOUR
  UNION ALL
  SELECT 1
  UNION ALL
  SELECT 2
  ...etc..
  SELECT 24
) AS h
LEFT OUTER JOIN INVOICE
  ON h.Hour = EXTRACT(HOUR FROM WHENINVOICED)
WHERE wheninvoiced > Current_timeStamp- 1
GROUP BY h.Hour

If it does not, create a table with 24 rows (1 for each hour of the day) and join on that instead of the derived table above.

Edit: Corrected GROUP BY

Acropoli

58 posts

Master Geek

#481103 14-Jun-2011 17:18

Thanks nzkc.

Yes, Firebird/FlameRobin does support derived tables apparantly since v2.0, but unfortunately I've tripped up already with the code provided:

Token unknown - line 2, column 4
Hour

I'm a newbie with databases, so unfortunately I'm not sure what the h.Hour is in reference to.

Acropoli

58 posts

Master Geek

#481173 14-Jun-2011 20:16

I'm so close!

Here's the query:
SELECT
H.HOURLIST as h,
SUM(salestax * 7.66666666) AS COUNTER
FROM
(
SELECT 0 AS h from INVOICE
UNION ALL
SELECT 1 as h from INVOICE
UNION ALL
SELECT 2 as h from INVOICE
UNION ALL
SELECT 3 as h from INVOICE
UNION ALL
SELECT 4 as h from INVOICE
UNION ALL
SELECT 5 as h from INVOICE
UNION ALL
SELECT 6 as h from INVOICE
UNION ALL
SELECT 7 as h from INVOICE
UNION ALL
SELECT 8 as h from INVOICE
UNION ALL
SELECT 9 as h from INVOICE
UNION ALL
SELECT 10 as h from INVOICE
UNION ALL
SELECT 11 as h from INVOICE
UNION ALL
SELECT 12 as h from INVOICE
UNION ALL
SELECT 13 as h from INVOICE
UNION ALL
SELECT 14 as h from INVOICE
UNION ALL
SELECT 15 as h from INVOICE
UNION ALL
SELECT 16 as h from INVOICE
UNION ALL
SELECT 17 as h from INVOICE
UNION ALL
SELECT 18 as h from INVOICE
UNION ALL
SELECT 19 as h from INVOICE
UNION ALL
SELECT 20 as h from INVOICE
UNION ALL
SELECT 21 as h from INVOICE
UNION ALL
SELECT 22 as h from INVOICE
UNION ALL
SELECT 23 as h from INVOICE
) AS h
LEFT OUTER JOIN INVOICE
ON H.HOURLIST = EXTRACT(HOUR FROM WHENINVOICED) <-----<<< Line 55
WHERE wheninvoiced > Current_timeStamp- 1
GROUP BY h.HourLIST

Here's the result:
SQL Message : -206
Column does not belong to referenced table

Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -206
Column unknown
H.HOURLIST
At line 55, column 8

Any further suggestion?

nzkc

1573 posts

Uber Geek

#481208 14-Jun-2011 21:49

Your derived table shouldn't have SELECT .... FROM, it should just have SELECT x

Reason being is that here we're "deriving" a table of values: 0 through 23 (or 1 through 24...your choice). The code "SELECT 1 FROM INVOICE" will generate a result set with 1 column populated with the value 1 and it will contain the same number of rows as your INVOICE table.

The issue with the join is that you have referenced a column from your derived table that does not exist.

If you look at your code, you named the column "h" but referenced a column "HOURLIST". You want to reference it as h.h

As it happens I'd recommend naming your column something like HourValue then reference it as "h.HourValue". Or if you want your derived table to be named "HOURLIST" it would become "HOURLIST.HourValue"

I could post the fixed code for you...but I think you'll get more value out of correcting this yourself. That said, if you cant solve it - let me know and I'll update the code :-)

Acropoli

58 posts

Master Geek

#481236 14-Jun-2011 22:51

Hi again nzkc.

Thanks for not abandoning me so close to a resolution, and even more so for leaving it for me to correct given the appropriate guidance - it's the best way to learn!

I'll have a crack at this first thing tomorrow, and will post back with how I get on.

Acropoli

58 posts

Master Geek

#481346 15-Jun-2011 10:17

Argh, I must be doing something drastically wrong. I've tried both a derived table and creating a new table with the hour values, both of which I'm not having any success with. This is what happens when you attempt to wing something in a specialist area

I thought I had corrected the derived table references, but then I kept getting a "Token Unknown" in reference to "Union" thrown back at me.

If you could fix the code nzkc, it would be appreciated. Then I'll be able to see where I went wrong. TIA

nzkc

1573 posts

Uber Geek

#481378 15-Jun-2011 11:05

I've personally never used Firebird. I'm a SQL Server person...

Can you tell me if this works in Firebird:

SELECT
    *
FROM
(
    SELECT 0 AS HourValue
    UNION ALL SELECT 1 AS HourValue
    UNION ALL SELECT 2 AS HourValue
    UNION ALL SELECT 3 AS HourValue
    UNION ALL SELECT 4 AS HourValue
    UNION ALL SELECT 5 AS HourValue
    UNION ALL SELECT 6 AS HourValue
    UNION ALL SELECT 7 AS HourValue
    UNION ALL SELECT 8 AS HourValue
    UNION ALL SELECT 9 AS HourValue
    UNION ALL SELECT 10 AS HourValue
    UNION ALL SELECT 11 AS HourValue
    UNION ALL SELECT 12 AS HourValue
    UNION ALL SELECT 13 AS HourValue
    UNION ALL SELECT 14 AS HourValue
    UNION ALL SELECT 15 AS HourValue
    UNION ALL SELECT 16 AS HourValue
    UNION ALL SELECT 17 AS HourValue
    UNION ALL SELECT 18 AS HourValue
    UNION ALL SELECT 19 AS HourValue
    UNION ALL SELECT 20 AS HourValue
    UNION ALL SELECT 21 AS HourValue
    UNION ALL SELECT 22 AS HourValue
    UNION ALL SELECT 23 AS HourValue
) as HourList

If that returns a 24 row list of 0 thru 23 then that's your derived table right there. Now just a case of joining to the query you had.

The query now becomes:

SELECT
    HourList.HourValue
    ,SUM(SalesTax) * 7.66666666 AS Counter
FROM
(
    SELECT 0 AS HourValue
    UNION ALL SELECT 1 AS HourValue
    UNION ALL SELECT 2 AS HourValue
    UNION ALL SELECT 3 AS HourValue
    UNION ALL SELECT 4 AS HourValue
    UNION ALL SELECT 5 AS HourValue
    UNION ALL SELECT 6 AS HourValue
    UNION ALL SELECT 7 AS HourValue
    UNION ALL SELECT 8 AS HourValue
    UNION ALL SELECT 9 AS HourValue
    UNION ALL SELECT 10 AS HourValue
    UNION ALL SELECT 11 AS HourValue
    UNION ALL SELECT 12 AS HourValue
    UNION ALL SELECT 13 AS HourValue
    UNION ALL SELECT 14 AS HourValue
    UNION ALL SELECT 15 AS HourValue
    UNION ALL SELECT 16 AS HourValue
    UNION ALL SELECT 17 AS HourValue
    UNION ALL SELECT 18 AS HourValue
    UNION ALL SELECT 19 AS HourValue
    UNION ALL SELECT 20 AS HourValue
    UNION ALL SELECT 21 AS HourValue
    UNION ALL SELECT 22 AS HourValue
    UNION ALL SELECT 23 AS HourValue
) AS HourList
LEFT OUTER JOIN Inovice
    ON HourList.HourValue = EXTRACT(HOUR FROM WhenInvoiced)
WHERE
    WhenInvoiced > CURRENT_TIMESTAMP - 1
GROUP BY
    HourList.HourValue

If that doesn't work because Firebird doesn't like joins onto functions (as I said, never used it so don't know) we can get around this by doing two derived tables:

SELECT
    HourList.HourValue
    ,COALESCE(GroupedInvoice.Counter, 0)    AS Counter
FROM
(
    /* This is derived table #1. It generates a list of hour values 0 through 23 */
    SELECT 0 AS HourValue
    UNION ALL SELECT 1 AS HourValue
    UNION ALL SELECT 2 AS HourValue
    UNION ALL SELECT 3 AS HourValue
    UNION ALL SELECT 4 AS HourValue
    UNION ALL SELECT 5 AS HourValue
    UNION ALL SELECT 6 AS HourValue
    UNION ALL SELECT 7 AS HourValue
    UNION ALL SELECT 8 AS HourValue
    UNION ALL SELECT 9 AS HourValue
    UNION ALL SELECT 10 AS HourValue
    UNION ALL SELECT 11 AS HourValue
    UNION ALL SELECT 12 AS HourValue
    UNION ALL SELECT 13 AS HourValue
    UNION ALL SELECT 14 AS HourValue
    UNION ALL SELECT 15 AS HourValue
    UNION ALL SELECT 16 AS HourValue
    UNION ALL SELECT 17 AS HourValue
    UNION ALL SELECT 18 AS HourValue
    UNION ALL SELECT 19 AS HourValue
    UNION ALL SELECT 20 AS HourValue
    UNION ALL SELECT 21 AS HourValue
    UNION ALL SELECT 22 AS HourValue
    UNION ALL SELECT 23 AS HourValue
) AS HourList
LEFT OUTER JOIN (

    /* This is derived table #2. It aggregates our Invoice records by hour */

    SELECT
        ,EXTRACT(HOUR FROM WhenInvoiced)    AS HourValue
        SUM(SalesTax) * 7.66666666         AS Counter
    FROM
        Invoice
    WHERE
        WhenInvoiced > CURRENT_TIMESTAMP - 1
    GROUP BY
        EXTRACT(HOUR FROM WhenInvoiced)

) AS GroupedInvoice
    ON HourList.HourValue = GroupedInvoice.HourValue

Note that we use the HourList.HourValue as the return column in the query and not the one dervied from Invoice. If we used the Invoice one we'd get NULL values (try it and see). We do a LEFT JOIN to ensure we get ALL records from the HourList derived table. Also no need to have a WHERE clause outside of the derived table. We've already done the restriction there so no records to exclude anymore. I also wrapped the GroupedInvoice.Counter value in a COALESCE statement to replace NULL values with 0.

Think that should about cover it :-)

Quic Broadband

Move to New Zealand's best fibre broadband service (affiliate link). Free setup code: R587125ERQ6VE. Note that to use Quic Broadband you must be comfortable with configuring your own router.

Acropoli

58 posts

Master Geek

#481406 15-Jun-2011 12:03

nzkc: I've personally never used Firebird. I'm a SQL Server person...

Can you tell me if this works in Firebird:

SELECT
    *
FROM
(
    SELECT 0 AS HourValue
    UNION ALL SELECT 1 AS HourValue
    UNION ALL SELECT 2 AS HourValue
    UNION ALL SELECT 3 AS HourValue
    UNION ALL SELECT 4 AS HourValue
    UNION ALL SELECT 5 AS HourValue
    UNION ALL SELECT 6 AS HourValue
    UNION ALL SELECT 7 AS HourValue
    UNION ALL SELECT 8 AS HourValue
    UNION ALL SELECT 9 AS HourValue
    UNION ALL SELECT 10 AS HourValue
    UNION ALL SELECT 11 AS HourValue
    UNION ALL SELECT 12 AS HourValue
    UNION ALL SELECT 13 AS HourValue
    UNION ALL SELECT 14 AS HourValue
    UNION ALL SELECT 15 AS HourValue
    UNION ALL SELECT 16 AS HourValue
    UNION ALL SELECT 17 AS HourValue
    UNION ALL SELECT 18 AS HourValue
    UNION ALL SELECT 19 AS HourValue
    UNION ALL SELECT 20 AS HourValue
    UNION ALL SELECT 21 AS HourValue
    UNION ALL SELECT 22 AS HourValue
    UNION ALL SELECT 23 AS HourValue
) as HourList

If that returns a 24 row list of 0 thru 23 then that's your derived table right there. Now just a case of joining to the query you had.

Argh, tearing my hair out. Thanks for the detailed information, unfortunately Firebird is giving me a Token Unknown for UNION when running the above query. I've confirmed FireBird does support derived tables but there has to be a missing parameter or something thats missing to make that work. I have a feeling it needed to be followed by "FROM" which is why I used it in one of my previous attempts, and didn't get the token unknown on Union message.

The alternative is of course to do the create table option. If I were to create a new table called "HourList" with with 1 column named "HourValues" and rows 0-23, what would the code look like?

nzkc

1573 posts

Uber Geek

#481417 15-Jun-2011 12:23

Can you run:

SELECT 0 AS HourValue

does that return 1 record? If it works, then try:

SELECT 0 AS HourValue
UNION ALL
SELECT 1 AS HourValue

Deos that work?

To answer your question regarding the creation of a fixed table (which would not be a bad idea) then you'd just reference that instead of the HourList derived table. So the code would look VERY similar, just much shorter :-)

nzkc

1573 posts

Uber Geek

#481419 15-Jun-2011 12:25

Looking at some Firebird info, you might be hitting an issue with the UNION ALL. Try UNION DISTINCT and if that doesn't work just UNION on its own.

Or it could be the naming of the value in the second and subsequent selects in the union so also try

SELECT 0 AS HourValue
UNION ALL
SELECT 1

Acropoli

58 posts

Master Geek

#481449 15-Jun-2011 13:32

Alrighty, Union really hates me, whether it's solo, all, or distinct. Always returns token unknown on "Union".

SELECT 0 AS HourValue returns Token Unknown '.'

It's almost leading me to believe that derived tables are not firebird friendly.

So, I've now created a new table called "HourList" with Column "HourValues", rows 0-23.

Where am I going wrong here?
SELECT
sum(salestax * 7.66666666) AS COUNTER
FROM INVOICE
LEFT OUTER JOIN HourList ON HourValues = EXTRACT(HOUR FROM wheninvoiced)
WHERE wheninvoiced > Current_timeStamp- 1

This returns a single record under "Counter" of 23260.81 *boggle*

Edit: Silly me, forgot the Group By.

This now returns the result:
344.386666
739.066666
352.743333
186.223333
1057.309999
411.163333
631.656666
4076.979996
6833.223327
4418.989996
1572.739999
526.930000
287.730000
56.580000
46.076667
23.996667
1211.179999

Great, but there's only 17 of them! /head meets desk

Acropoli

58 posts

Master Geek

#481461 15-Jun-2011 13:54

SELECT COALESCE(hourlist.HOURVALUES, 0),
sum(salestax * 7.66666666) as counter
FROM INVOICE
LEFT OUTER JOIN HourList ON HourValues = EXTRACT(HOUR FROM wheninvoiced)
WHERE wheninvoiced > Current_timeStamp - 1
group by HOURLIST.HOURVALUES

returns

Coalesce                                           Counter
10                                                   344.386666
11                                                   739.066666
12                                                   352.743333
13                                                   80.346667
14                                                   1057.309999
15                                                   411.163333
16                                                   631.656666
17                                                   4076.979996
18                                                   6833.223327
19                                                   4418.989996
20                                                   1572.739999
21                                                   526.930000
22                                                   287.730000
23                                                   56.580000
7                                                    46.076667
8                                                    23.996667
9                                                    1211.179999

reven

3743 posts

Uber Geek

Trusted

#481464 15-Jun-2011 13:58

cant you just round WHENINVOICED value to the nearest hour, do a sum of the salestax and group by the WHENINVOICED figure, and then multiple the sum by 7.66666?

nzkc

1573 posts

Uber Geek

#481466 15-Jun-2011 14:01

You've got your JOIN round the wrong way.

Either make it a RIGHT OUTER JOIN, or swap the table order.

Thats why you've lost the hours where there is no data. And your COALESCE is on the wrong column, you want it on the SUM e.g: COALESCE(SUM(SalesTax) * 7.6666666, 0) AS Total

And add an ORDER BY on the HourList.HourValue column :-)

@Reven, if you look at his first post, that's what he was doing but doesn't want that. He wants all hours (0-23) in a day and the totals where there is data for those hours.

Acropoli

58 posts

Master Geek

#481490 15-Jun-2011 14:21

Thanks guys, I've changed the Coalesce to the correct position, and Right Outer Join still returns 17 rows instead of 24. I'm making an assumption in thinking that perhaps the HourValues Column in the HourList table needs to have a different format? I think it's set up as CHAR(50)

1 | 2 | 3