1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

From Month,To Month prompt from a single attribute Month in database for OBI

Discussion in 'SQL PL/SQL' started by info.deepu@gmail.com, Feb 10, 2009.

  1. info.deepu@gmail.com

    info.deepu@gmail.com Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I have a month column in a table in database.I need to createtwo prompts From Month and To Month where in the values are to populated from this column.i.e., when the report is executed it should produce results between these two months.The query will look some thing like thing.
    Code (Text):

    Select
    t.fiscal_month_desc, SUM(s.amount_sold)
    from
    sales s,times t
    where
    t.fiscal_month_id >=(select distinct fiscal_month_id from times where fiscal_month_desc='&from_month')
    and
    t.fiscal_month_id <=(select distinct fiscal_month_id from times where fiscal_month_desc='&to_month')
    group by
    t.fiscal_month_desc
    order by
    t.fiscal_month_desc;
     
    The above query is for SH sample schema for details of table structure kindly refer to the SH schema.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    hi, ok but what exactly is the problem you are facing? what is it that you are asking us is not clear
     
  3. info.deepu@gmail.com

    info.deepu@gmail.com Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Well....I want to add 2 dashboard prompts From Month and To Month but I have only an month in the table in database.Kindly go through the SH schema for table structures.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I don't have access to SH schema to test the scenario.

    Are you sure that you want to go for Cartesian product between Sales and times ? ( These tables are not joined by any field in your query)


    You can go for ,

    1. Correlated sub query between 2 times tables ( One part of main query another sub query) and MIN and MAX function in inner query.

    like

    Code (Text):

    SELECT   t.fiscal_month_desc, SUM (s.amount_sold)
    FROM sales s, times t
    ------------------
    ------------------
    AND t.fiscal_month_id >= (SELECT MIN(t1.fiscal_month_id)
                                     FROM times t1
                                     WHERE fiscal_month_desc =                                                              '&FROM_MONTH'
                                     AND t1.-------------= t.----------)
    AND t.fiscal_month_id <= (SELECT MAX(t2.fiscal_month_id)
                                     FROM times t2
                                     WHERE fiscal_month_desc = '&TO_MONTH'
                                     AND t2.-------------= t.----------)
    GROUP BY t.fiscal_month_desc
    ORDER BY t.fiscal_month_desc;
     
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hmhm .. I think the better solution will be to use Normal subqueries like

    Code (Text):

    SELECT   t.fiscal_month_desc, SUM (s.amount_sold)
    FROM sales s, times t
    ------------------
    ------------------
    AND t.fiscal_month_id >= (SELECT MIN(t1.fiscal_month_id)
                                     FROM times t1
                                     WHERE fiscal_month_desc =                                                              '&FROM_MONTH' )
    AND t.fiscal_month_id <= (SELECT MAX(t2.fiscal_month_id)
                                     FROM times t2
                                     WHERE fiscal_month_desc = '&TO_MONTH' )
    GROUP BY t.fiscal_month_desc
    ORDER BY t.fiscal_month_desc;
     
     
  6. info.deepu@gmail.com

    info.deepu@gmail.com Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Sorry I didn't mean a cartesian join.I was trying to stress on the prompts in that query. Query with the join condition will llok something like this.
    Code (Text):

    Select
    t.fiscal_month_desc, SUM(s.amount_sold)
    from
    sales s,times t
    where
    t.time_id=s.time_id
    and
    t.fiscal_month_id >=(select distinct fiscal_month_id from times where fiscal_month_desc='&from_month')
    and
    t.fiscal_month_id <=(select distinct fiscal_month_id from times where fiscal_month_desc='&to_month')
    order by
    t.fiscal_month_desc;
     
    If you are interested in downloading the SH Schema you can download it from the link on attachment.
     

    Attached Files:

  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Isn't it the same I tried before ?
    What is the result you got ?

    IF it is something different , give us the Create and insert script to reproduce the test case along with the actual result you got and expected result.