Do you have conditional command to bring more than one line?

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 93
Joined: Tue, 06 Apr 2010 11:31 am
Location: São Paulo - SP

Good, I need to make a condition to bring certain records ... Example:

Select all

select c1,c2, case when c3 = 0 then c5,c6,c7 
                                              else c8,c9,c10 end 
from .....
The above code serves only to exemplify because it does not work with more than one field ...
Would you have any other command to perform the condition?
Thanks and T +
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

Hello XPrata,

I did not understand exactly. In your example, if the C3 field is 0, do you want to bring fields C4, C5, C6 each in one column, or each in your respective column?
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 93
Joined: Tue, 06 Apr 2010 11:31 am
Location: São Paulo - SP

Thank you Return Pelor .... Next ...

If the field C3 = 0

I need to do:

Select all

select c1,c2,c5,c6,c7 from .....
If the field c3 <> 0
must:

Select all

select c1,c2,c8,c9,c10 from .....
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

Considering that the types of C5 = C8, C6 = C9, C7 = C10 you can make use:

Select all

select c1,c2,c5,c6,c7  
  from ..... 
 WHERE campo c3 = 0 
 UNION ALL 
select c1,c2,c8,c9,c10  
  from ..... 
 WHERE campo c3 <> 0  ;
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 93
Joined: Tue, 06 Apr 2010 11:31 am
Location: São Paulo - SP

I gave this example to facilitate understanding ....

but I need to make calculations with the column ...
if c3 = 0

Example:

Select all

select c1,c2, 
if c3=0 
   c4-c10 x,c5-c10 y 
else 
   c4-c3 x,c5-c3 y 
,c11,c12, --mais outras colunas... 
from...
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

But it is possible to make the calculations in the way you want, you just need to separate Union's SELECTS (one if C3 = 0 and another when different) and maintain the correct data type for each column of the general.

Select all

 
select c1 
        , c2 
        , c5 - c6 x  
        , c7 + c4 y 
  from ..... 
WHERE campo c3 = 0 
 
UNION ALL 
 
select c1 
        , c2 
        , c8 - c9 x   
        , c10 * c11 y 
  from ..... 
WHERE campo c3 <> 0  ; 
In the example above X and Y of the two selects need to return even type, either number, text, date ....
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 93
Joined: Tue, 06 Apr 2010 11:31 am
Location: São Paulo - SP

Query in question and very big and applying Union will duplicate ...
But I'm going to take a test with your tip to see if it will run ...
Thanks
Att
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

The size of each query is not important. The important thing is that the first consultation data is different from the second to not have undue duplication.
Reinforcing Union All (best performance), because only Union will try to eliminate possible duplications (as if it were a distinct).
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 17 guests