Toad World® Forums

I want the result for different years without filling in the year manually

I’m hoping that post this message on the right forum. I’m programmer with some SQL knoledge.

I’m doing this with TOAD!

On another forum I had a thread with a pseudo query and some people say you don’t need a loop. Ok can be, but what is then the solution that get the result for different years in a grid?
I have placed here the real query.
The years can every time been different but now I want the result for the years 2010 till 2014? As ‘programmer’ I think I need a loop!?
Ok I can fill in the years manualy, but that is not the way to go I think.
And now there is another request the product can be different.

I hope that somebody can help me or put me in the right direction.

--Sommatie van de volumes
    select lc.CONTRACTID
          ,c.CLICKTYPECODE
          ,count(*) NumberOfSlices
          ,ROUND(SUM(c.slicevolume),0) as SliceVolume
    from contract lc
        ,click c
        ,(
        -- De losse clickcontracten die gesommeeerd worden en dan tevens voor de sommatie
        -- o.a. het contract id mee moeten retourneren
        select clc.CONTRACTID as Contract_ID
        from contract clc
            ,click cc
        where clc.CONTRACTSTATUSCODE                         = 'EXECUTED'
        and   clc.CONTRACTTYPECODE                           = 'STANDARD_PRICING'
        and   cc.UIDCONTRACT                                 = clc.UIDCONTRACT
        and   TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy'))   = 2011
        group by clc.CONTRACTID
        ) Click_Cntr
    where lc.CONTRACTSTATUSCODE                         = 'EXECUTED'
    and   lc.CONTRACTID                                 = Click_Cntr.Contract_ID
    and   lc.CONTRACTTYPECODE                           = 'STANDARD_PRICING'
    and   c.UIDCONTRACT                                 = lc.UIDCONTRACT
    and   c.CLICKTYPECODE                               = 'BOTH' --'OFF_PEAK 'ON_PEAK'
    and   TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy'))   = 2011
    group by lc.CONTRACTID, c.CLICKTYPECODE
    union
    select lc.CONTRACTID
          ,c.CLICKTYPECODE
          ,count(*) NumberOfSlices
          ,ROUND(SUM(c.slicevolume),0) as SliceVolume
    from contract lc
        ,click c
        ,(
        -- De losse clickcontracten die gesommeeerd worden en dan tevens voor de sommatie
        -- o.a. het contract id mee moeten retourneren
        select clc.CONTRACTID as Contract_ID
        from contract clc
            ,click cc
        where clc.CONTRACTSTATUSCODE                         = 'EXECUTED'
        and   clc.CONTRACTTYPECODE                           = 'STANDARD_PRICING'
        and   cc.UIDCONTRACT                                 = clc.UIDCONTRACT
        and   TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy'))   = 2011
        group by clc.CONTRACTID
        ) Click_Cntr
    where lc.CONTRACTSTATUSCODE                         = 'EXECUTED'
    and   lc.CONTRACTID                                 = Click_Cntr.Contract_ID
    and   lc.CONTRACTTYPECODE                           = 'STANDARD_PRICING'
    and   c.UIDCONTRACT                                 = lc.UIDCONTRACT
    and   c.CLICKTYPECODE                               = 'OFF_PEAK' --''OFF_PEAK ''ON_PEAK
    and   TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy'))   = 2011
    group by lc.CONTRACTID, c.CLICKTYPECODE
    union
    select lc.CONTRACTID
          ,c.CLICKTYPECODE
          ,count(*) NumberOfSlices
          ,ROUND(SUM(c.slicevolume),0) as SliceVolume
    from contract lc
        ,click c
        ,(
        -- De losse clickcontracten die gesommeeerd worden en dan tevens voor de sommatie
        -- o.a. het contract id mee moeten retourneren
        select clc.CONTRACTID as Contract_ID
        from contract clc
            ,click cc
        where clc.CONTRACTSTATUSCODE                         = 'EXECUTED'
        and   clc.CONTRACTTYPECODE                           = 'STANDARD_PRICING'
        and   cc.UIDCONTRACT                                 = clc.UIDCONTRACT
        and   TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy'))   = 2011
        group by clc.CONTRACTID
        ) Click_Cntr
    where lc.CONTRACTSTATUSCODE                         = 'EXECUTED'
    and   lc.CONTRACTID                                 = Click_Cntr.Contract_ID
    and   lc.CONTRACTTYPECODE                           = 'STANDARD_PRICING'
    and   c.UIDCONTRACT                                 = lc.UIDCONTRACT
    and   c.CLICKTYPECODE                               = 'ON_PEAK' --''OFF_PEAK ''ON_PEAK
    and   TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy'))   = 2011
    group by lc.CONTRACTID, c.CLICKTYPECODE;

Message from: reedda

You actually want the TOADSQL forum. Anyway… You should replace your
to_number(…) = 2011 with to_number(…) IN (2011,2012,2013,2014). No looping
is associated with SQL. If I am off base, someone will correct me.


Historical Messages

Author: David Reed
Date: Fri Oct 21 05:31:46 PDT 2011
You actually want the TOADSQL forum. Anyway… You should replace your
to_number(…) = 2011 with to_number(…) IN (2011,2012,2013,2014). No looping
is associated with SQL. If I am off base, someone will correct me.
__

Author: nico_515
Date: Fri Oct 21 03:07:04 PDT 2011

I’m hoping that post this message on the right forum. I’m programmer
with some SQL knoledge.

I’m doing this with TOAD!

On another forum I had a thread with a pseudo query and some people say you
don’t need a loop. Ok can be, but what is then the solution that get the
result for different years in a grid?
I have placed here the real query.
The years can every time been different but now I want the result for the years
2010 till 2014? As ‘programmer’ I think I need a loop!?
Ok I can fill in the years manualy, but that is not the way to go I think.
And now there is another request the product can be different.

I hope that somebody can help me or put me in the right direction.

--Sommatie van de volumes
select lc.CONTRACTID
,c.CLICKTYPECODE
,count(*) NumberOfSlices
,ROUND(SUM(c.slicevolume),0) as SliceVolume
from contract lc
,click c
,(
-- De losse clickcontracten die gesommeeerd worden en dan tevens voor de
sommatie
-- o.a. het contract id mee moeten retourneren
select clc.CONTRACTID as Contract_ID
from contract clc
,click cc
where clc.CONTRACTSTATUSCODE = 'EXECUTED'
and clc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and cc.UIDCONTRACT = clc.UIDCONTRACT
and TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy')) = 2011
group by clc.CONTRACTID
) Click_Cntr
where lc.CONTRACTSTATUSCODE = 'EXECUTED'
and lc.CONTRACTID = Click_Cntr.Contract_ID
and lc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and c.UIDCONTRACT = lc.UIDCONTRACT
and c.CLICKTYPECODE = 'BOTH' --'OFF_PEAK 'ON_PEAK'
and TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy')) = 2011
group by lc.CONTRACTID, c.CLICKTYPECODE
union
select lc.CONTRACTID
,c.CLICKTYPECODE
,count(*) NumberOfSlices
,ROUND(SUM(c.slicevolume),0) as SliceVolume
from contract lc
,click c
,(
-- De losse clickcontracten die gesommeeerd worden en dan tevens voor de
sommatie
-- o.a. het contract id mee moeten retourneren
select clc.CONTRACTID as Contract_ID
from contract clc
,click cc
where clc.CONTRACTSTATUSCODE = 'EXECUTED'
and clc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and cc.UIDCONTRACT = clc.UIDCONTRACT
and TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy')) = 2011
group by clc.CONTRACTID
) Click_Cntr
where lc.CONTRACTSTATUSCODE = 'EXECUTED'
and lc.CONTRACTID = Click_Cntr.Contract_ID
and lc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and c.UIDCONTRACT = lc.UIDCONTRACT
and c.CLICKTYPECODE = 'OFF_PEAK' --''OFF_PEAK ''ON_PEAK
and TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy')) = 2011
group by lc.CONTRACTID, c.CLICKTYPECODE
union
select lc.CONTRACTID
,c.CLICKTYPECODE
,count(*) NumberOfSlices
,ROUND(SUM(c.slicevolume),0) as SliceVolume
from contract lc
,click c
,(
-- De losse clickcontracten die gesommeeerd worden en dan tevens voor de
sommatie
-- o.a. het contract id mee moeten retourneren
select clc.CONTRACTID as Contract_ID
from contract clc
,click cc
where clc.CONTRACTSTATUSCODE = 'EXECUTED'
and clc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and cc.UIDCONTRACT = clc.UIDCONTRACT
and TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy')) = 2011
group by clc.CONTRACTID
) Click_Cntr
where lc.CONTRACTSTATUSCODE = 'EXECUTED'
and lc.CONTRACTID = Click_Cntr.Contract_ID
and lc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and c.UIDCONTRACT = lc.UIDCONTRACT
and c.CLICKTYPECODE = 'ON_PEAK' --''OFF_PEAK ''ON_PEAK
and TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy')) = 2011
group by lc.CONTRACTID, c.CLICKTYPECODE;

__


You actually want the TOADSQL forum. Anyway… You should replace your to_number(…) = 2011 with to_number(…) IN (2011,2012,2013,2014). No looping is associated with SQL. If I am off base, someone will correct me.

The years can every time been different but now
I want the result for the years 2010 till 2014?

TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , ‘yyyy’)) = 2011

to_number(…) IN (2011,2012,2013,2014)

Here’s some general tips:

  1. Check to see if the approvedtime column is indexed. If it is, you’ll want to avoid using a function call on the column unless the exact same function call was built into the index (highly unlikely with a double function call in use). This is a performance tip.

  2. How is the report going to be called? When a parameter selection is necessary, someone has to enter those parameters somewhere. That’s a “manual” step you’ll not be able to avoid unless the parameters are rule-based. From what was said, the date and product are parameters.

a) Assuming the normal date flow of a range, you have two parameters: p_start_year, p_end_year. You could use between or “>=” combined with "

If your dates are arbitrary (for example 2001, 2005, 2006 and 2010) then a lot has just increased in complexity from your query to how you acquire all of the parameters (is it always 4, can there be only 3 or as many as 7?).

b) Assuming you can attach a business rule such as “the report will always be run against the previous 4 fiscal years” then by finding out the definition which is meant of “fiscal year” (might correspond to the calendar year, might not) you could alter the logic accordingly.

  1. If you truly want to run the sql stand-alone then having the dates in one location may be of use. You could use either the “with as” functionality or the define value (defaults to &) functionality.

Roger S.

This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.