SitePoint Sponsor

User Tag List

Results 1 to 15 of 15

Thread: dynamic query

  1. #1
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    302
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    dynamic query

    I have a database table month_values

    this table has 12 columns...... columns are jan , feb , march , .....decm.

    I have data values for each months.

    I'm writing store procedure. I want to sum values between any two input month columns.


    Example Input :

    SP_MONTH_VALS( feb,sept)



    Expected Output:

    output for the above stored procedure should be sum values for month columns feb,march,april,may,june,july,aug,sept.


    Problem:

    my problem is ...As you can see this is going to be dynamic query....because we may pass any two month columns to the store procedure input.

    Can you please suggest how to build dynamic query here based on input selection ?

    Do I need to use a CASE here ? how do I proceed ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i know you're going to get upset at this, but it needs to be said -- your table design is the cause of your difficulty

    if you had one row per month's data, where the month number (1 through 12) was part of the key, then the query would be trivial...
    Code:
    SELECT SUM(amt)
      FROM daTable
     WHERE mth >= @startmonth
       AND mth <= @endmonth
    here @startmonth and @endmonth are placeholders (not php variables ) for the two months passed in to your stored proc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    302
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i know you're going to get upset at this, but it needs to be said -- your table design is the cause of your difficulty

    if you had one row per month's data, where the month number (1 through 12) was part of the key, then the query would be trivial...
    Code:
    SELECT SUM(amt)
      FROM daTable
     WHERE mth >= @startmonth
       AND mth <= @endmonth
    here @startmonth and @endmonth are placeholders (not php variables ) for the two months passed in to your stored proc
    No. I dont have. I don't want to change the design also....this is impossible.

    Do you suggest any workaround with this existing design ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    fine, do it your way

    stored procedures allow the use of the IF statement

    use it... copiously
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    302
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    fine, do it your way

    stored procedures allow the use of the IF statement

    use it... copiously
    I know it would be clumsy.

    1. design can not be changed because its already loaded with data.
    2. its a huge database with many tables having relationships with each other.
    3. system is stable with user input in the UI level.
    4. database designed by a certified DBA. Its not easy to comment on design without looking at the complete database design and complicacy. I also have least control altering any database design changes here.

    Now having said these ... I understand if is an alternative way out...but I'm looking out possibilities for a better approach.

    Is not that going to bring many IF's here ? Can we think about minimizing IF here ?

    How you are planning to mark the range ?

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,220
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Personally, due to your limitations, I'd argue going on a software approach instead of a query approach.

    Let the UI collect the start and ending months, but return the entire 12 months back in the query and then let the programming language figure out which columns to add together. Otherwise you are looking at a very bulky, hard to maintain stored procedure.

  7. #7
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    302
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Personally, due to your limitations, I'd argue going on a software approach instead of a query approach.

    Let the UI collect the start and ending months, but return the entire 12 months back in the query and then let the programming language figure out which columns to add together. Otherwise you are looking at a very bulky, hard to maintain stored procedure.
    I liked this approach. Yes. I do have a control on JSP / Java side . I hold UI data in a java variable.

    I'm not clear ... are you suggesting to build query in java side and then pass the query to the stored procedure ? OR are you saying to collect how many months needs to be passed to the store procedure ?

    where the select query will built up ?

    Please clarify .

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,220
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    I am saying, let the UI collect the start and end month, run a generic query that returns all months, then let the code loop through the results and sum up the appropriate columns based on the start and end month.

    You could have the UI build the query too (if that is acceptable in your work environment) and run an ad-hoc query instead of a stored procedure. Either way, doing this in a stored procedure (in my opinion) is a mistake due to the restraints on the table design.

  9. #9
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    302
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    I am saying, let the UI collect the start and end month, run a generic query that returns all months, then let the code loop through the results and sum up the appropriate columns based on the start and end month.

    No. This won't be possible and inconvenient in my work environment... because I am sending output values directly to a Report Designer which displays data in PDF/Word/Excel format in the UI finally....so have to do it in the Stored Procedure.

    You could have the UI build the query too (if that is acceptable in your work environment) and run an ad-hoc query instead of a stored procedure. Either way, doing this in a stored procedure (in my opinion) is a mistake due to the restraints on the table design.

    I am thinking like this ...

    let the UI build a Select query and pass this query to the stored procedure....inside stored procedure I can use PREPARED STMT to run a query and get the output.

    I still have the check though if this fits in the present set up
    my comments in the above

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,220
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Okay. Keep in mind you may be opening up a security issue with utilizing a stored procedure to accept a query. As a developer could send whatever query they would want through that stored procedure and depending on the role associated to the stored procedure may have more access than they should.

    If you only have them send in the SUM equation, you could likely concatenate the rest of the query so the stored procedure limits the return data to your specific table(s).

  11. #11
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is what you need
    I created just sample table and added test data into

    create table #T
    (
    Jan int,
    Feb int,
    Mar int,
    Apr int,
    May int,
    June int,
    July int,
    Aug int,
    Sept int,
    Oct int,
    Nov int,
    Dec int
    )

    insert into #T
    values(12,15,48,74,636,89,12,45,54,88,88,77)
    insert into #T
    values(2,1,8,4,63,8,1,5,4,8,8,7)

    so, select statement like below
    SELECT * FROM #T
    produce output...
    Jan Feb Mar Apr May June July Aug Sept Oct Nov Dec
    12 15 48 74 636 89 12 45 54 88 88 77
    2 1 8 4 63 8 1 5 4 8 8 7

    Now time to create your store proc

    DECLARE @startMonth as int, @endMonth as int

    set @startMonth=2

    set @endMonth=5


    ;
    with t as
    (
    select 1 as mnth, jan as cnt from #T
    union
    select 2 as mnth, feb as cnt from #T
    union
    select 3 as mnth, Mar as cnt from #T
    union
    select 4 as mnth, Apr as cnt from #T
    union
    select 5 as mnth, May as cnt from #T
    union
    select 6 as mnth, June as cnt from #T
    union
    select 7 as mnth, July as cnt from #T
    union
    select 8 as mnth, Aug as cnt from #T
    union
    select 9 as mnth, Sept as cnt from #T
    union
    select 10 as mnth, oct as cnt from #T
    union
    select 11 as mnth, nov as cnt from #T
    union
    select 12 as mnth, dec as cnt from #T
    )
    select SUM(cnt) FROM t
    WHERE mnth BETWEEN @startMonth AND @endMonth

    I declare variables (you should have them as stored procedure parameters) and give them values just for test

    that produce result

    total
    849

    which you looking for.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gk53 View Post
    this is what you need ...
    discombobulating the badly designed table on the fly -- i love it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,220
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gk53 View Post
    this is what you need
    I created just sample table and added test data into
    This process assumes SQL Server right? As I don't think Oracle or MySQL support CTEs (I could be wrong)

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    CTEs can always be re-written as POS (plain old sql)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, change with to


    select SUM(cnt) FROM
    (
    select 1 as mnth, jan as cnt from #T
    union
    select 2 as mnth, feb as cnt from #T
    union
    select 3 as mnth, Mar as cnt from #T
    union
    select 4 as mnth, Apr as cnt from #T
    union
    select 5 as mnth, May as cnt from #T
    union
    select 6 as mnth, June as cnt from #T
    union
    select 7 as mnth, July as cnt from #T
    union
    select 8 as mnth, Aug as cnt from #T
    union
    select 9 as mnth, Sept as cnt from #T
    union
    select 10 as mnth, oct as cnt from #T
    union
    select 11 as mnth, nov as cnt from #T
    union
    select 12 as mnth, dec as cnt from #T
    ) t
    WHERE t.mnth BETWEEN @startMonth AND @endMonth

    it is supports by mySQL and Oracle


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •