Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




58 posts

Master Geek


Topic # 85141 14-Jun-2011 16:12
Send private message

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?

View this topic in a long page with up to 500 replies per page Create new topic
 1 | 2 | 3
380 posts

Ultimate Geek
+1 received by user: 38


  Reply # 481086 14-Jun-2011 16:37
Send private message

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



58 posts

Master Geek


  Reply # 481103 14-Jun-2011 17:18
Send private message

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.

 
 
 
 




58 posts

Master Geek


  Reply # 481173 14-Jun-2011 20:16
Send private message

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?

380 posts

Ultimate Geek
+1 received by user: 38


  Reply # 481208 14-Jun-2011 21:49
Send private message

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 :-)



58 posts

Master Geek


  Reply # 481236 14-Jun-2011 22:51
Send private message

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.



58 posts

Master Geek


  Reply # 481346 15-Jun-2011 10:17
Send private message

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 Tongue out

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

380 posts

Ultimate Geek
+1 received by user: 38


  Reply # 481378 15-Jun-2011 11:05
Send private message

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 :-)



58 posts

Master Geek


  Reply # 481406 15-Jun-2011 12:03
Send private message

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?



380 posts

Ultimate Geek
+1 received by user: 38


  Reply # 481417 15-Jun-2011 12:23
Send private message

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 :-)

380 posts

Ultimate Geek
+1 received by user: 38


  Reply # 481419 15-Jun-2011 12:25
Send private message

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



58 posts

Master Geek


  Reply # 481449 15-Jun-2011 13:32
Send private message

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





58 posts

Master Geek


  Reply # 481461 15-Jun-2011 13:54
Send private message

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


3211 posts

Uber Geek
+1 received by user: 563

Trusted

  Reply # 481464 15-Jun-2011 13:58
Send private message

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?

380 posts

Ultimate Geek
+1 received by user: 38


  Reply # 481466 15-Jun-2011 14:01
Send private message

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.



58 posts

Master Geek


  Reply # 481490 15-Jun-2011 14:21
Send private message

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
View this topic in a long page with up to 500 replies per page Create new topic



Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

UAV Traffic Management Trial launching today in New Zealand
Posted 12-Dec-2017 16:06


UFB connections pass 460,000
Posted 11-Dec-2017 11:26


The Warehouse Group to adopt IBM Cloud to support digital transformation
Posted 11-Dec-2017 11:22


Dimension Data peeks into digital business 2018
Posted 11-Dec-2017 10:55


2018 Cyber Security Predictions
Posted 7-Dec-2017 14:55


Global Govtech Accelerator to drive public sector innovation in Wellington
Posted 7-Dec-2017 11:21


Stuff Pix media strategy a new direction
Posted 7-Dec-2017 09:37


Digital transformation is dead
Posted 7-Dec-2017 09:31


Fake news and cyber security
Posted 7-Dec-2017 09:27


Dimension Data New Zealand strengthens cybersecurity practice
Posted 5-Dec-2017 20:27


Epson NZ launches new Expression Premium Photo range
Posted 5-Dec-2017 20:26


Eventbrite and Twickets launch integration partnership in Australia and New Zealand
Posted 5-Dec-2017 20:23


New Fujifilm macro lens lands in New Zealand
Posted 5-Dec-2017 20:16


Cyber security not being taken seriously enough
Posted 5-Dec-2017 20:13


Sony commences Android 8.0 Oreo rollout in New Zealand
Posted 5-Dec-2017 20:08



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.