Doubt in constraint with ranges of values

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 29 Dec 2008 2:59 pm

Guys ... check it out ...

I have to create a report in which I have to restrict the values as follows: 1 the actual 400 401 the 600601 the 800801 the 1000 1001 to 1200 above 1201 ...

I did a workaround here: he was like that ...

u0009u0009u0009u0009u0009
Code: Select all
       AND
                      ((&intervalo = 6 AND nfs.nfs_vltot > 1201) OR
                        nfs.nfs_vltot BETWEEN
                        decode(&intervalo, 1, 1, 2, 401, 3, 601, 4, 801, 5, 1001) AND
                        decode(&intervalo, 1, 400, 2, 600, 3, 800, 4, 1000, 5, 1200))
can someone improve?

Thanks!
Diego_Mello
Location: Igrejinha - RS

Diego Mello
Igrejinha - RS
www.twitter.com/diegolmello

Poston Mon, 29 Dec 2008 5:06 pm

Brother, you could do with UNION with the tracks, changing a column from querie, being your nro breaking, or making your collation sequence, if his soul.

qualquer thing, send it to us.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Tue, 30 Dec 2008 8:38 am

That's exactly what I needed ...

Hadn't considered the fact of having to show the range of values the record belonged ...
Soon, the way I was doing, would complicate me to consider ...

Thanks! :-o
Diego_Mello
Location: Igrejinha - RS

Diego Mello
Igrejinha - RS
www.twitter.com/diegolmello

Poston Fri, 02 Jan 2009 5:41 am

Cool, man!
So that everything goes right, put us here as the final solution, please?
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Tue, 06 Jan 2009 2:54 pm

Quiet, brother ...

In summary, it was so ...

Code: Select all
SELECT faixa
         ,ordem
   FROM (
            --// INTERVALO = 1
            SELECT '1 a 400' faixa
                      ,1 ordem
               FROM . . .
             WHERE (:p_intervalo = 1 OR :p_intervalo = 7)
                      AND round(nfs.nfs_vltot) >= 1
                      AND round(nfs.nfs_vltot) <= 400
            UNION ALL
            
            --//INTERVALO = 2
            SELECT '401 a 600' faixa
                     ,2 ordem
               FROM . . .
             WHERE (:p_intervalo = 2 OR :p_intervalo = 7)
                      AND round(nfs.nfs_vltot) >= 401
                      AND round(nfs.nfs_vltot) <= 600
            UNION ALL
            
            --// INTERVALO = 3
            SELECT '601 a 800' faixa
                     ,3 ordem
               FROM . . .
             WHERE (:p_intervalo = 3 OR :p_intervalo = 7)
                      AND round(nfs.nfs_vltot) >= 601
                      AND round(nfs.nfs_vltot) <= 800
            UNION ALL
            
            --//INTERVALO = 4
            SELECT '801 a 1000' faixa
                     ,4 ordem
               FROM . . .
             WHERE (:p_intervalo = 4 OR :p_intervalo = 7)
                      AND round(nfs.nfs_vltot) >= 801
                      AND round(nfs.nfs_vltot) <= 1000
            UNION ALL
            
            --// INTERVALO = 5
            SELECT '1001 a 1200' faixa
                     ,5 ordem
               FROM . . .
             WHERE (:p_intervalo = 5 OR :p_intervalo = 7)
                      AND round(nfs.nfs_vltot) >= 1001
                      AND round(nfs.nfs_vltot) <= 1200
            UNION ALL
            
            --//INTERVALO = 6
            SELECT 'Maior que 1201' faixa
                     ,6 ordem
               FROM . . .
             WHERE (:p_intervalo = 6 OR :p_intervalo = 7)
                      AND round(nfs.nfs_vltot) >= 1201) nft
ORDER BY . . .
I appreciate the help!
Diego_Mello
Location: Igrejinha - RS

Diego Mello
Igrejinha - RS
www.twitter.com/diegolmello

Poston Tue, 06 Jan 2009 4:47 pm

You can also use CASE WHEN. Then you don't need a lot of UNION ALL.

Something like this:
Code: Select all
SELECT
  CAMPO
, VALOR
, CASE WHEN valor >=  0  AND valor <=100 THEN  1
       wHEN valor > 100  AND valor <=200 THEN  2
       wHEN valor > 200  AND valor <=400 THEN  3
       wHEN valor > 400  AND valor <=800 THEN  4
       wHEN valor > 800  AND valor <=1500 THEN 5
       wHEN valor > 1500 AND valor <=5000 THEN 6
  END faixa     
FROM TABELA
in this case, will create only with the track number.
But then you simply \"translate\" this number to a number that the user can understand.
Type: 1 = range Between 0 and 100 track 2 = Between 100 and 200 track 3 = Between 200 and 400 etc
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 07 Jan 2009 7:13 am

Do I need, in addition to bringing in the select, restrict the range of values ...
Soon, we came back to my initial question ...:(
Diego_Mello
Location: Igrejinha - RS

Diego Mello
Igrejinha - RS
www.twitter.com/diegolmello

Poston Wed, 07 Jan 2009 8:10 am

Actually, in my case it just SHOWS what track.

But here's the thing: If your user to choose 7 (all), he will run the query several times and make UNIONs ... Theoretically this is not necessary, because we can do it more easily.

Another thing: it is not necessary to put ROUND, as this will probably generate a lot more cost to your query.

You could combine the two things as follows:
Code: Select all
SELECT
  CAMPO
, VALOR
, CASE WHEN valor >=  0  AND valor <=100 THEN  '0 a 400'
       wHEN valor > 100  AND valor <=200 THEN  '400 a 600'
       wHEN valor > 200  AND valor <=400 THEN  '600 a 800'
       wHEN valor > 400  AND valor <=800 THEN  'etc...'
       wHEN valor > 800  AND valor <=1500 THEN 'etc..'
       wHEN valor > 1500 AND valor <=5000 THEN 'etc..'
  END faixa     
FROM TABELA
WHERE  :p_intervalo = 7
   OR (:p_intervalo = 1 AND nfs.nfs_vltot>=0   AND nfs.nfs_vltot<=400)
   OR (:p_intervalo = 2 AND nfs.nfs_vltot>400  AND nfs.nfs_vltot<=600)
   OR (:p_intervalo = 3 AND nfs.nfs_vltot>600  AND nfs.nfs_vltot<=800)
   .
   .
   .
   etc
in this way, it shows which track fell in CASE WHEN above.
And also filters just informed track parameter. More scam artist! :-)
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 07 Jan 2009 12:50 pm

Brother!
I changed the query, as your tip, but the performance, oddly enough, worsened the. 0 the complete query ran in 36seg before the change.
After she went on to perform in seconds.

In summary, it was so ...

Code: Select all
SELECT
CASE
   WHEN nfs.nfs_vltot >= 0 AND nfs.nfs_vltot <= 400 THEN 1
   WHEN nfs.nfs_vltot > 400 AND nfs.nfs_vltot <= 600 THEN 2
   WHEN nfs.nfs_vltot > 600 AND nfs.nfs_vltot <= 800 THEN 3
   WHEN nfs.nfs_vltot > 800 AND nfs.nfs_vltot <= 1000 THEN 4
   WHEN nfs.nfs_vltot > 1000 AND nfs.nfs_vltot <= 1200 THEN 5
   WHEN nfs.nfs_vltot > 1200 THEN 6
END ordem
FROM tabela
WHERE     (&p_intervalo = 7
               OR (&p_intervalo = 1 AND nfs.nfs_vltot >= 0 AND nfs.nfs_vltot <= 400)
               OR (&p_intervalo = 2 AND nfs.nfs_vltot > 400 AND nfs.nfs_vltot <= 600)
               OR (&p_intervalo = 3 AND nfs.nfs_vltot > 600 AND nfs.nfs_vltot <= 800)
               OR (&p_intervalo = 4 AND nfs.nfs_vltot > 800 AND nfs.nfs_vltot <= 1000)
               OR (&p_intervalo = 5 AND nfs.nfs_vltot > 1000 AND nfs.nfs_vltot <= 1200)
               OR (&p_intervalo = 6 AND nfs.nfs_vltot > 1200))
Diego_Mello
Location: Igrejinha - RS

Diego Mello
Igrejinha - RS
www.twitter.com/diegolmello

Poston Wed, 07 Jan 2009 1:11 pm

Pro 7 interval or others?
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 07 Jan 2009 1:12 pm

7 range
Diego_Mello
Location: Igrejinha - RS

Diego Mello
Igrejinha - RS
www.twitter.com/diegolmello

Poston Sun, 11 Jan 2009 12:36 am

I believe that it will be easier:
Code: Select all
select nfs.nfs_vltot
,      decode(trunc((nfs.nfs_vltot-0.01)/200),0,1,1,1,2,2,3,3,4,4,5,5,6) intervalo
,      decode(decode(trunc((nfs.nfs_vltot-0.01)/200),0,1,1,1,2,2,3,3,4,4,5,5,6),
              1, '    1  a   400',
              2, '  401  a   600',
              3, '  601  a   800',
              4, '  801  a  1000',
              5, ' 1001  a  1200','Acima de  1200')
from   nfs
where  &intervalo = 7
or     &intervalo = decode(trunc((nfs.nfs_vltot-0.01)/200),0,1,1,1,2,2,3,3,4,4,5,5,6)
margaridi
Location: Curitiba - PR

----------------------------
Margaridi, Marco Aurélio
www.margaridi.com

Poston Mon, 12 Jan 2009 9:04 am

Got better now ^^ thanks to everyone for your help! :-o
Diego_Mello
Location: Igrejinha - RS

Diego Mello
Igrejinha - RS
www.twitter.com/diegolmello


  • See also
    Replies
    Views
    Last Post


    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 10 guests