Restriction with ranges of values

[Diego_Mello]

Galera ... Look ...

I have to create a report where I have to restrict the values ??as follows:

1 to 400 reais

401 to 600

601 to 800

801 to 1000

1001 1200

above 1201 ...

I made a Gambiarra here: OE was like this ...

0]]

Can anyone improve?

Thanks!

[Trevisolli]

Brother,

would do to do with union with the tracks, changing a column of the crash, being your nro of breaking, or, doing grouping by your sequential, if o Have.

Whatever, send us.

[Diego_Mello]

That was what I needed ...

I had not considered the fact that I have to show the range of values ??the record belonged ...
So, the way I I was doing, I would complicate myself to consider ...

Thanks! :-O

[Toad]

Nice man!
As soon as everything comes right, put us here as the final solution was, please?

[Diego_Mello]

Quiet, Brother ...

In summary, it was like this ...

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 . . . 

Thank you!

[dr_gori]

You can also use Case When. From there you do not need a lot of Union All.

Something like this:

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, it will create only with the track number.
But then you simply "translate" this number to a number that the user understands.
Type:
Track 1 = Between 0 and 100
Range 2 = Between 100 and 200
Track 3 = Between 200 and 400

[Diego_Mello]

It's that I need, in addition to bringing in the select, restricting through this range of values ??...
Soon, we return to my initial doubt ...

[dr_gori]

Really, in my case it just shows which track.

But there is one thing: if your user choose 7 (all), it will run several times the query and make unions ... theoretically this is not necessary, because it can do all this more easily.

Another thing: it is not necessary to put round, as this will probably generate much more cost for your query.

You could combine the two things as follows:

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 

That way, it shows which track dropped into the when case above.
and also filter only the track entered in the parameter! Much more performative!

[Diego_Mello]

Brother!
I changed Query, according to your tip, but the performance, incredible as it may seem, worsened O.0
Complete Query performed in 36seg before the change.
After her, she started to run in 50seg.

In summary, it was like this ...

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)) 

[dr_gori]

Pro range 7 or others?

[Diego_Mello]

interval 7

[margaridi]

I believe that it will be lighter:

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) 

[Diego_Mello]

It was better now ^^

Thanks to everyone for help! :-O