This discussion is archived
10 Replies Latest reply: Feb 2, 2013 1:36 PM by 988510 RSS

2 SQL questions

988510 Newbie
Currently Being Moderated
Hi!
I need 2 queries:

1)
I have a table b1 with date (string, yyyymmdd) and some other fields and a table b2 with date (string, yyyymmdd, referenced to b1), swid (ID of product) and pcs (no of ordered products). b1 are the base data of 1 order, b2 are the ordered products with reference to b1.
What I need is a query that shows a statistic per month with count of all orders, all ordered pcs and all ordered pcs with swid='9016' like ...:
month, sum_orders, sum_pcs, sum_9016

2)
I have a table b2 with date (string, yyyymmdd), swid (ID of product), pcs (no of ordered products), prc (price for 1 piece of the product).
What I need is a statistic per year like ...:
swid, sum_pcs_2008, sum_prc_2008, sum_pcs_2009, sum_prc_2009, ...

I don't know if it's important but I have to use the queries in VB 2010 for an Oracle server database via ODBC.

Thanks
Peter
  • 1. Re: 2 SQL questions
    Bawer Journeyer
    Currently Being Moderated
    user11096060 wrote:
    Hi!
    I need 2 queries:

    1)
    I have a table b1 with date (string, jjjjmmdd) and some other fields and a table b2 with date (string, jjjjmmdd, referenced to b1), swid (ID of product) and pcs (no of ordered products). b1 are the base data of 1 order, b2 are the ordered products with reference to b1.
    What I need is a query that shows a statistic per month with count of all orders, all ordered pcs and all ordered pcs with swid='9016'.

    2)
    I have a table b2 with date (string, jjjjmmdd), swid (ID of product), pcs (no of ordered products), prc (price for 1 piece of the product).
    What I need is a statistic per year like ...:
    swid, sum_pcs_2008, sum_prc_2008, sum_pcs_2009, sum_prc_2009, ...
    both are samething like.
    Solution:
    Use date feld and NOT varchar. If you use strings, you must convert all fields to date to compare with a date, or you must convert your input and all fields to a number to compare as numbers, which will get unneeded performance problems.
    define/alter your table so that the field is a date.
    then use group by TO_DATE(,date_field'MM') / TO_DATE(,date_field'YYYY')
    with in where clause using BETWEEN operator for start and end times.
  • 2. Re: 2 SQL questions
    988510 Newbie
    Currently Being Moderated
    thanks Bawer but I think there's not a big difference for small databases whether I use date-format or strings for date. There's no problem to use i.e. GROUP BY MID(datestring,1,6) for a monthly report.
    My problem is to "combine" the 2 tables in 1) and to generate a "Pivot" in 2).
  • 3. Re: 2 SQL questions
    Bawer Journeyer
    Currently Being Moderated
    user11096060 wrote:
    thanks Bawer but I think there's not a big difference for small databases whether I use date-format or strings for date.
    theoretically, you can use strings for numbers too ;-) but I don't think, it is right way. Number is number, and a date is a date.
    user11096060 wrote:
    My problem is to "combine" the 2 tables in 1) and to generate a "Pivot" in 2).
    please provide small examples with create table/insert statements and expected output.
  • 4. Re: 2 SQL questions
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!
    user11096060 wrote:
    thanks Bawer but I think there's not a big difference for small databases whether I use date-format or strings for date.
    No, there's always a big difference, even for small tables. (The size of the database has nothing to do with it.)
    The smaller the table, the less important it is for performance. A table with 0 rows might be equally fast either way. However, the code will be more complicated and more error-prone if you use the wrong data type.
    In this particular problem, the difference isn't so apparant, but you will have times when when you want to dispaly a date in a particular format, such as 'Feb. 1, 2013', or when you need to know if a given date is on a weekend, or if one date is within 90 days of another.
    There's no problem to use i.e. GROUP BY MID(datestring,1,6) for a monthly report.
    What is MID? Do you mean SUBSTR?
    My problem is to "combine" the 2 tables in 1) and to generate a "Pivot" in 2).
    What exactly is the problem, using SUBSTR?
    Post your best attempts.

    Whenever you have a problem, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

    Edited by: Frank Kulash on Feb 1, 2013 7:03 AM
  • 5. Re: 2 SQL questions
    EdStevens Guru
    Currently Being Moderated
    user11096060 wrote:
    thanks Bawer but I think there's not a big difference for small databases whether I use date-format or strings for date.
    It's less about performance than it is about data purity. If you keep 'dates' as strings, there is no inherent difference between '01/01/2013' and 'heres your sign'. At some point, you WILL be coming back to this forum wanting to know how to scrub your data because some occurrences of that 'date' do not meet your expected format. and in the mean time, all of your code will have to jump through hoops -- will be more complex than it need be -- just to try to treat a string as if it were a date. Every time you do a date comparison or do date arithmetic.

    There is simply NO EXCUSE for not using the correct data type. DATE for dates. NUMBER for numbers. Period. End of story.
    There's no problem to use i.e. GROUP BY MID(datestring,1,6) for a monthly report.
    My problem is to "combine" the 2 tables in 1) and to generate a "Pivot" in 2).
  • 6. Re: 2 SQL questions
    988510 Newbie
    Currently Being Moderated
    Thanks to all for your comments but please no more discussions about data types.

    About my data:

    ad 1)

    b1.date (string, yyyymmdd)
    b2.date (string, yyyymmdd, referenced to b1)
    b2.swid (num, ID of product)
    b2.pcs (num, no of ordered products)

    b1.date | b1.name | ...
    20120113 | Dori | ...
    20120302 | Coma | ...
    20120330 | Carl | ...

    b2.date | b2.swdid | b2.pcs
    20120113 | 9016 | 3
    20120302 | 8888 | 9
    20120302 | 9016 | 1
    20120302 | 7777 | 2
    20120330 | 3333 | 2
    20120330 | 2222 | 1

    what I want:

    month | sum_orders_permonth | sum_pcs_permonth | sum_9016_permonth
    201201 | 1 | 3 | 3
    201203 | 2 | 15 | 1


    ad 2)

    b2.date (string, yyyymmdd)
    b2.swid (num, ID of product)
    b2.pcs (num, no of ordered products)
    b2.prc (price for 1 piece)

    b2.date | b2.swdid | b2.pcs | b2.prc
    20120113 | 9016 | 3 | 3.13
    20120302 | 8888 | 9 | 2.00
    20120302 | 9016 | 1 | 3.13
    20120302 | 7777 | 2 | 9.22
    20120330 | 3333 | 2 | 1.98
    20120330 | 2222 | 1 | 4.10

    what I want:

    swid | sum_pcs_2011 | sum_prc_2011 | sum_pcs_2012 | sum_prc_2012 | ...
    2222 | 0 | 0.00 | 1 | (1*4.10)
    3333 | 0 | 0.00 | 2 | (2*1.98)
    7777 | 0 | 0.00 | 2 | (2*9.22)
    8888 | 0 | 0.00 | 9 | (9*2.00)
    9016 | 0 | 0.00 | 4 | (4*3.13)

    Thanks
    Peter
  • 7. Re: 2 SQL questions
    Stew Ashton Expert
    Currently Being Moderated
    Thanks for your questions but please no more telling people what they may or may not reply.
  • 8. Re: 2 SQL questions
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Problem 1 is a Fan Trap , that is, a one-to-many relationship between tables, where you need aggregates from both tables. If you join the tables, then do the aggregates, then the parent rows that have multiple children will be counted multiple times. The solution is to do the aggregates on each table separately, using sub-queries. The solution above does the aggregates on b1 first, in the sub-query order_summary, and then does the aggregates on b2 in the main query:
    WITH     order_summary     AS
    (
         SELECT       SUBSTR (dt, 1, 6)     AS month
         ,       COUNT (*)              AS sum_orders_permonth
         FROM       b1
         GROUP BY  SUBSTR (dt, 1, 6)
    )
    SELECT       o.month
    ,       MIN (o.sum_orders_permonth)     AS sum_orders_permonth
    ,       SUM (b2.pcs)               AS sum_pcs_permonth
    ,       NVL ( SUM ( CASE
                              WHEN  b2.swid = 9016
                     THEN  bs.pcs
                          END
                        )
               , 0
               )                    AS sum_9016_permonth
    FROM       order_summary  o
    JOIN       b2              ON  SUBSTR (b2.dt, 1, 6)
    GROUP BY  o.month
    ORDER BY  o.month
    ;
    If you'd care to post CREATE TABLE and INSERT statments for your sample data, then I could test it.

    Problem 2 is a Pivot .
    The number of columns in a result set has to be hard-coded into the query when it is written and compiled.
    If you want to write something today that will have exactly as many columns as you have in the table next month (or any other point in the future), then you need Dynamic SQL . A simpler alternative is to use String Aggregation , where the output from a variable number of months actually goes into one big VARCHAR2 column, which is formatted to look like multiple columns.
    Both pivoting and string aggregation depend on your version of Oracle. The forum FAQ {message:id=9360005} covers several versions.
    {message:id=3527823} discusses options for pivoting a variable numebr of columns.
    If you'd like help, post you best attempt, CREATE TABLE and INSERT statements for some sample data, the results you want from that data, and your Oracle version.
    No kidding; you need to read the forum FAQ: {message:id=9360002}
  • 9. Re: 2 SQL questions
    EdStevens Guru
    Currently Being Moderated
    user11096060 wrote:
    Thanks to all for your comments but please no more discussions about data types.
    You are driving down a road to DBA Hell. People are telling you that. And your response is to tell them to quit talking about the horrors of DBA Hell and focus on helping you make your car go faster.

    I stand in amazement ...

    <snip>
  • 10. Re: 2 SQL questions
    988510 Newbie
    Currently Being Moderated
    Many thanks Frank, I'll try it tomorrow.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points