Complicated select

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
cleberzumba
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 223
Joined: Thu, 14 Dec 2006 10:45 am
Location: Brasília-DF
Cleber Zumba de Souza

Good afternoon,

Next, I am a select as follows

Select all

select tx_anexo 
from table1 t1, table2 t2 
where t1.a = t2.a 
and t1.cod = 561
This select returns two lines, in the Field TX_ANEXO He returned me on the first line an X value and the second line a Y. I wanted to mount this select in the way he returned the value x and y on the same line one on the side of the other. Pdem help me ???
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

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

Brother,

I think it would be more or less this:

Select all

 
 
SELECT t1.tx_anexo, (SELECT tx_anexo 
						     FROM table2 t2 
					    	 WHERE t2.a = t1.a) tx_anexo_2 
  FROM table1 t1 
 WHERE t1.a = 1 
   AND t1.cod = 1000 
 
Who has any more idea / solution, post there for us.
cleberzumba
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 223
Joined: Thu, 14 Dec 2006 10:45 am
Location: Brasília-DF
Cleber Zumba de Souza

Today my SELECT returns like this:

Select all

cd_anexo           cd_pesquisa            tx_anexo 
--------------     -------------------    ----------------------- 
633                561                    aaaaaaaaaaaaaa 
634                561                    bbbbbbbbbbbbbb

And I want it to return like this:

Select all

 tx_anexo1                                    tx_anexo2 
  --------------------------------------    ------------------------------------ 
  aaaaaaaaaaaaaaaaaaaa                      bbbbbbbbbbbbbbbbbb
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

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

Post a few lines of your table so we can take a brother look ...
It can be this case there in specific, however, with tables 1 and 2.

vlw
cleberzumba
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 223
Joined: Thu, 14 Dec 2006 10:45 am
Location: Brasília-DF
Cleber Zumba de Souza

My original SELECT is this:

Select all

select anex.tx_anexo as anexo1,anex.tx_anexo as anexo2   
from db_pesquisa.tbl_anexo anex, 
     db_pesquisa.tbl_pesquisa pesq, 
     db_pesquisa.tbl_pessoa_empresa pessemp, 
     db_pesquisa.tbl_pessoa pes 
where pesq.cd_pesquisa = 561  
  and anex.cd_pesquisa = pesq.cd_pesquisa  
  and pesq.cd_pesquisa = pessemp.cd_pesquisa  
  and pessemp.cd_pessoa = pes.cd_pessoa
That returns me like this:

Select all

ANEXO1                                           ANEXO2 
-----------------------------------------------  ------------------------------- 
A01_0023_Cópia de anexo_B_teste.doc              A01_0023_Cópia de anexo_B_teste.doc 
anexo_D_A01_0023.doc                             anexo_D_A01_0023.doc

Repair which has the Even value for the two columns, only they are different online and I want it to return to me only one line being that each value is in different columns.
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

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

Brother,

Do you want it to leave as exemplified below (in the proposed)?
Just so I stand better ...


Today:
[/b]

Select all

 
 ANEXO1                                          ANEXO2  
----------------------------------------------- -------------------------------  
A01_0023_Cópia de anexo_B_teste.doc             A01_0023_Cópia de anexo_B_teste.doc  
anexo_D_A01_0023.doc                            anexo_D_A01_0023.doc  


proposed:

Select all

 
 ANEXO1                                          ANEXO2  
----------------------------------------------- -------------------------------  
A01_0023_Cópia de anexo_B_teste.doc             anexo_D_A01_0023.doc  
cleberzumba
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 223
Joined: Thu, 14 Dec 2006 10:45 am
Location: Brasília-DF
Cleber Zumba de Souza

EXACTLY ! ! ! !
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

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

beleza BROTHER, I'm going to do some tests here and I'll get you back.
cleberzumba
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 223
Joined: Thu, 14 Dec 2006 10:45 am
Location: Brasília-DF
Cleber Zumba de Souza

That's right, today is returned two lines and one column just and I want you to return a lines and two columns.
cleberzumba
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 223
Joined: Thu, 14 Dec 2006 10:45 am
Location: Brasília-DF
Cleber Zumba de Souza

Did you get something Trevisolli? ... I could not, it's difficult.
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

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

Brother,

with pl / sql can be or, only SQL?
cleberzumba
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 223
Joined: Thu, 14 Dec 2006 10:45 am
Location: Brasília-DF
Cleber Zumba de Souza

It may be what Dêr Trevisolli ...
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Here in the forum it was already spoken about crosstab.
Look why you will find something!
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

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

Truth dr_gori.
Thanks for the tip.

Cleber, try to adapt as [url=http://en.glufke.net/oracle/viewtopic.php?t=844]neste exemplo

whatever, send it there.
cleberzumba
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 223
Joined: Thu, 14 Dec 2006 10:45 am
Location: Brasília-DF
Cleber Zumba de Souza

My field is varchar2 ... I can not add, I can not use group function ... do not have another exit?
cleberzumba
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 223
Joined: Thu, 14 Dec 2006 10:45 am
Location: Brasília-DF
Cleber Zumba de Souza

It worked!!!!

I was able this way:

Select all

select pesq.cd_pesquisa, 
         max(anex.tx_anexo)  Anexo_1, 
         min(anex.tx_anexo)  Anexo_2      
from db_pesquisa.tbl_anexo anex, 
        db_pesquisa.tbl_pesquisa pesq, 
       db_pesquisa.tbl_pessoa_empresa pessemp, 
       db_pesquisa.tbl_pessoa pes 
where pesq.cd_pesquisa = 561  
  and  anex.cd_pesquisa = pesq.cd_pesquisa  
  and  pesq.cd_pesquisa = pessemp.cd_pesquisa  
  and  pessemp.cd_pessoa = pes.cd_pessoa       
group by pesq.cd_pesquisa  
order by 1
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 16 guests