Home » SQL & PL/SQL » SQL & PL/SQL » [HELP] SQL complex query
[HELP] SQL complex query [message #304620] Wed, 05 March 2008 19:19 Go to next message
viko
Messages: 9
Registered: March 2008
Junior Member
I need to do a complex query that will be launched in sqlplus by a script.

perhaps it isn´t so complex, i'm sorry if it should be on newbie section.


i'll explain
i have 3 databases from where i catch principal data with something like this:

SELECT pack FROM(
        SELECT filename||'!'||business||'!NOR' as pack FROM table1 WHERE status1 IN ('E','T') UNION

        SELECT filename||'.OK'||'!'||business||'!CLI' as pack FROM table2 WHERE status2 IN ('E','T') UNION

        SELECT filename||'.OK'||'!'||business||'!COS'||'!'||data_base_code as pack FROM table3  WHERE status3 IN ('E','T')

                ) ORDER BY pack ASC



query's result is one column containing the long strings.

Matter is that last table field "data_base_code" isn't usefull for me. But a field in another table is. So, I need to concatenate that field into my string instead of data_base_code

But, the query to get that field is like this:

SELECT field1 FROM table_x WHERE
table_x.field2=table3.data_base_code


and I don't know how to make this stuff

has someone any idea?

regards,

viko
Re: [HELP] SQL complex query [message #304621 is a reply to message #304620] Wed, 05 March 2008 20:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
So join table3 and table_x and select the columns that you want. There are plenty of examples in the documentation:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#i2066611

Please post future such questions in the newbies forum.

Re: [HELP] SQL complex query [message #304717 is a reply to message #304620] Thu, 06 March 2008 06:11 Go to previous message
viko
Messages: 9
Registered: March 2008
Junior Member
Thaks Barbara, and really sorry by the mistake


before posting i have entered in experts forum, where i have seen threads like this one, that's why i have posted it there... what i didn't see is that those threads were moved to newbies.


just for not going off topic, i'll explain the simple response to my question


it was difficult to me, it's rare how complicate can one make things (i have tried with many subquerys and more)


the way to the what i wanted to is just:

SELECT pack FROM(
SELECT filename||'!'||business||'!NOR' as pack FROM table1 WHERE status1 IN ('E','T') UNION

SELECT filename||'.OK'||'!'||business||'!CLI' as pack FROM table2 WHERE status2 IN ('E','T') UNION

SELECT ltc_filename||'.OK'||'!'||ltc_linea_negocio||'!COS'||'!'||bd_maquina as lote
FROM cfg_lotes_cli, cll_bases_datos
WHERE ltc_estado IN ('E','T')
AND table_x.field2 = table3.data_base_code

) ORDER BY pack ASC

regards

VIKO
Previous Topic: what is the difference between "REF CURSOR" AND "SYS_REFCUSOR"
Next Topic: how to delete all data in all tables in one time ?!
Goto Forum:
  


Current Time: Sun Dec 04 04:56:35 CST 2016

Total time taken to generate the page: 0.10062 seconds