Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Daniel.

Asked: April 29, 2004 - 12:12 pm UTC

Last updated: May 03, 2004 - 1:01 pm UTC

Version: 9.2.0.1.0

Viewed 1000+ times

You Asked

I need all the records of a first table plus one field from a second table that meet a certain condition.

With these tables ...

CREATE TABLE T1 (
ID INTEGER,
FIELDA VARCHAR2 (10))

INSERT INTO T1 (ID,FIELDA) VALUES (1,'A1')
INSERT INTO T1 (ID,FIELDA) VALUES (2,'A2')
INSERT INTO T1 (ID,FIELDA) VALUES (3,'A3')
INSERT INTO T1 (ID,FIELDA) VALUES (4,'A4')

CREATE TABLE T2 (
ID INTEGER,
NAME VARCHAR2 (10),
CONDITION INTEGER)

INSERT INTO T2 (ID, NAME, CONDITION) VALUES (1,'Name1',10)
INSERT INTO T2 (ID, NAME, CONDITION) VALUES (2,'Name2',10)
INSERT INTO T2 (ID, NAME, CONDITION) VALUES (2,'Name3',20)
INSERT INTO T2 (ID, NAME, CONDITION) VALUES (3,'Name4',20)


I need this

select t1.*,(select t2.name from t2 where t1.ID=t2.ID and t2.condition=10) as NAME from t1

ID FIELDA NAME
-- ------ -----
1 A1 Name1
2 A2 Name2
3 A3
4 A4

because with the joins I don't know how to obtain the same result:

select t1.*,t2.name from t1,t2 where t1.id=t2.id(+) and (t2.condition=10 or condition is null)

ID FIELDA NAME
-- ------ -----
1 A1 Name1
2 A2 Name2
4 A4

Anyway the first select statement works ok if I execute it directly to the Oracle database but when I execute it through OLEDB (Release 9.2) it gives me and "ORA-00907: missing right parenthesis" error.
I haven't find any information about this error and now I'm searching for another way to resolve the problem.

Thanks for your time.


and Tom said...

well, a view would work -- or:

ops$tkyte@ORA9IR2> select t1.*, t2.name
2 from t1, t2
3 where t1.id = t2.id(+)
4 and 10 = t2.condition(+)
5 /

ID FIELDA NAME
---------- ---------- ------------------------------
1 A1 Name1
2 A2 Name2
3 A3
4 A4

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select t1.*, t2.name
2 from t1, (select * from t2 where condition = 10) t2
3 where t1.id = t2.id(+)
4 /

ID FIELDA NAME
---------- ---------- ------------------------------
1 A1 Name1
2 A2 Name2
3 A3
4 A4


will work

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

OLEDB (Release 9.2) problem.

Daniel Garcia, April 30, 2004 - 10:31 am UTC

I've tried to apply your first suggestion to my select statement and it doesn't works (it works but returns more rows), maybe the example was too simple*.
The second it's not applicable because the application must run with Sql Server too and I can't put an inline view in the from clause.

Anyway, I've change the OLEDB Release 9.2 for the Release 9.01 and now the select statement with the inline view in the select clause works ok.
Maybe this is a bug of OLEDB Release 9.2?

*The original statement was :
select recurso.*,(select e0.nombre from EMPRESA e0,CONTRATO c0,CONTRATO_RECURSO cr0,RECURSO r0 where c0.idempresa = e0.idempresa And c0.idcontrato = cr0.idcontrato And cr0.idRecurso = r0.idRecurso And r0.idrecurso=recurso.idrecurso and c0.fechainicio <= to_date('30/4/2004','dd-mm-yyyy') AND c0.fechafin >= to_date('30/4/2004','dd-mm-yyyy')) as nomempresa from RECURSO where recurso.activo=1 order by RECURSO.idrecurso
and the statement changed :
select r0.*, e0.nombre as nomempresa from RECURSO r0, CONTRATO_RECURSO cr0, CONTRATO c0, EMPRESA e0 where r0.activo = 1 and r0.IDRECURSO = cr0.IDRECURSO (+) And cr0.idcontrato = c0.idcontrato (+) and c0.idempresa = e0.idempresa (+) And c0.fechainicio (+) <= to_date ('29/4/2004','dd-mm-yyyy') AND c0.fechafin (+) >= to_date ('29/4/2004','dd-mm-yyyy') order by R0.idrecurso


Tom Kyte
April 30, 2004 - 4:45 pm UTC

sorry -- impossible, totally impossible to tune, or really be successful with

"because the application must run with Sql Server
too and I can't put an inline view in the from clause.
"

you'll only get applications that run barely ok on a single database and really bad on all of the rest.


but, give me a set of create tables and example data -- and an example where it returns "too many rows" and we'll take a look see.

Daniel Garcia, May 03, 2004 - 7:55 am UTC

Sorry for don't give you the 'create table's and the example data. As I resolve the problem using OLEDB 9.01 instead OLEDB 9.2 I thought to don't bother more and study the problem by myself when I'll have time.
Related to doing applications against different databases I'm totally agree with you but I couldn't choose.

CREATE TABLE REC (
IDRECURSO VARCHAR2 (10) NOT NULL,
NOMBRE VARCHAR2 (50) NOT NULL,
ACTIVO NUMBER (1) DEFAULT (1) ) ;

ALTER TABLE REC ADD CONSTRAINT PK_REC
UNIQUE (IDRECURSO) ;

INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M01' ,'Nombre y apellido Operario A' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M03' ,'SISTEMA DE GESTIÓN INSTALACIONES' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M05' ,'GRUPO ELECTRÓGENO' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M10' ,'ASCENSOR Nº 12637 EDIFICIO B' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M11' ,'ASCENSOR Nº 12704 Y 12705 EDIFICIO C' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M12' ,'ASCENSOR Nº 12680 Y 12681 EDIFICIO B' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M13' ,'ASCENSOR Nº 12636 EDIFICIO C' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M14' ,'GÓNDOLA Nº 1752' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M02' ,'Nombre y apellidos operario C' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M04' ,'GRUPO P. CONTRA INCENDIOS, BOMBAS WILO Y CALPEDA' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M06' ,'GÓNDOLA Nº 1744' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M07' ,'EXUTORIOS Y EVACUACIÓN DE HUMOS' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M08' ,'PLANTAS ENFRIADORAS Y BOMBAS DE CALOR' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M09' ,'INSTALACIÓN DETECCIÓN Y EXTINCIÓN INCENDIOS' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M15' ,'GÓNDOLA Nº 1745' ,1);
INSERT INTO REC (IDRECURSO,NOMBRE,ACTIVO) VALUES ('M00' ,'Nombre y apellido Operario C' ,0);

CREATE TABLE EMP (
IDEMPRESA NUMBER (10) NOT NULL,
NOMBRE VARCHAR2 (50) NOT NULL ) ;

ALTER TABLE EMP ADD CONSTRAINT PK_EMP
UNIQUE (IDEMPRESA) ;

INSERT INTO EMP (IDEMPRESA,NOMBRE) VALUES (7,'SDMO');
INSERT INTO EMP (IDEMPRESA,NOMBRE) VALUES (10,'CONTROLLI TREND');
INSERT INTO EMP (IDEMPRESA,NOMBRE) VALUES (27,'AGUILERA ELECTRÓNICA');
INSERT INTO EMP (IDEMPRESA,NOMBRE) VALUES (47,'TALLERES NAVARRETE');
INSERT INTO EMP (IDEMPRESA,NOMBRE) VALUES (48,'MANLIN, S.L.');
INSERT INTO EMP (IDEMPRESA,NOMBRE) VALUES (49,'EURO SISTEMA INCENDIO, S.L.');
INSERT INTO EMP (IDEMPRESA,NOMBRE) VALUES (50,'DIFUSORA DEL CLIMA R.R., S.L.');
INSERT INTO EMP (IDEMPRESA,NOMBRE) VALUES (52,'KONE ELEVADORES, S.A.');
INSERT INTO EMP (IDEMPRESA,NOMBRE) VALUES (42,'ROCKWOOL');
INSERT INTO EMP (IDEMPRESA,NOMBRE) VALUES (43,'KAIMANNFLEX');
INSERT INTO EMP (IDEMPRESA,NOMBRE) VALUES (58,'COMPRESORES JOSVAL');

CREATE TABLE CONTR (
IDCONTRATO NUMBER (10) NOT NULL,
IDEMPRESA NUMBER (10) NOT NULL,
FECHAINICIO DATE NOT NULL,
FECHAFIN DATE NOT NULL ) ;

ALTER TABLE CONTR ADD CONSTRAINT PK_CONTR
UNIQUE (IDCONTRATO) ;

ALTER TABLE CONTR ADD CONSTRAINT FK_CONTR_EMP
FOREIGN KEY (IDEMPRESA)
REFERENCES EMP (IDEMPRESA) ;

INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (1,10,to_date('01/01/2004','dd-mm-yyyy'),to_date('31/12/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (2,47,to_date('01/01/2004','dd-mm-yyyy'),to_date('31/12/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (3,7,to_date('01/01/2004','dd-mm-yyyy'),to_date('31/12/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (4,47,to_date('01/01/2005','dd-mm-yyyy'),to_date('31/12/2006','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (5,48,to_date('01/01/2004','dd-mm-yyyy'),to_date('31/12/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (6,49,to_date('01/01/2004','dd-mm-yyyy'),to_date('31/12/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (7,50,to_date('01/01/2004','dd-mm-yyyy'),to_date('31/07/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (8,50,to_date('01/08/2004','dd-mm-yyyy'),to_date('31/12/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (9,50,to_date('01/01/2005','dd-mm-yyyy'),to_date('31/12/2005','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (10,27,to_date('01/01/2004','dd-mm-yyyy'),to_date('31/12/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (11,52,to_date('01/07/2003','dd-mm-yyyy'),to_date('30/06/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (12,52,to_date('01/07/2003','dd-mm-yyyy'),to_date('30/06/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (13,52,to_date('10/07/2003','dd-mm-yyyy'),to_date('09/07/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (14,52,to_date('01/07/2003','dd-mm-yyyy'),to_date('30/06/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (17,48,to_date('01/01/2004','dd-mm-yyyy'),to_date('31/12/2004','dd-mm-yyyy'));
INSERT INTO CONTR (IDCONTRATO,IDEMPRESA,FECHAINICIO,FECHAFIN) VALUES (18,48,to_date('01/01/2004','dd-mm-yyyy'),to_date('31/12/2004','dd-mm-yyyy'));

CREATE TABLE CONTR_REC (
IDCONTRATO NUMBER (10) NOT NULL,
IDRECURSO VARCHAR2 (10) NOT NULL ) ;

ALTER TABLE CONTR_REC ADD CONSTRAINT PK_CONTR_REC
UNIQUE (IDCONTRATO, IDRECURSO) ;

ALTER TABLE CONTR_REC ADD CONSTRAINT FK_CONTR_REC_CONTR
FOREIGN KEY (IDCONTRATO)
REFERENCES CONTR (IDCONTRATO) ;

ALTER TABLE CONTR_REC ADD CONSTRAINT FK_CONTR_REC_REC
FOREIGN KEY (IDRECURSO)
REFERENCES REC (IDRECURSO) ;

INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (1,'M03');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (2,'M04');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (3,'M05');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (5,'M06');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (6,'M07');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (7,'M08');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (8,'M08');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (9,'M08');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (10,'M09');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (11,'M10');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (12,'M11');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (13,'M12');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (14,'M13');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (17,'M14');
INSERT INTO CONTR_REC (IDCONTRATO,IDRECURSO) VALUES (18,'M15');

The original select statement (whith the results I want):

select REC.*,(select e0.nombre from EMP e0,CONTR c0,CONTR_REC
cr0,REC r0 where c0.idempresa = e0.idempresa And c0.idcontrato =
cr0.idcontrato And cr0.idRecurso = r0.idRecurso And
r0.idrecurso=REC.idrecurso and c0.fechainicio <=
to_date('30/4/2004','dd-mm-yyyy') AND c0.fechafin >=
to_date('30/4/2004','dd-mm-yyyy')) as nomempresa from REC where
REC.activo=1 order by REC.idrecurso

IDRECURSO NOMBRE ACTIVO NOMEMPRESA
M01 Nombre y apellido Operario A 1
M02 Nombre y apellidos operario B 1
M03 SISTEMA DE GESTIÓN INSTALACIONES 1 CONTROLLI TREND
M04 GRUPO P. CONTRA INCENDIOS, BOMBAS WILO Y CALPEDA 1 TALLERES NAVARRETE
M05 GRUPO ELECTRÓGENO 1 SDMO
M06 GÓNDOLA Nº 1744 1 MANLIN, S.L.
M07 EXUTORIOS Y EVACUACIÓN DE HUMOS 1 EURO SISTEMA INCENDIO, S.L.
M08 PLANTAS ENFRIADORAS Y BOMBAS DE CALOR 1 DIFUSORA DEL CLIMA R.R., S.L.
M09 INSTALACIÓN DETECCIÓN Y EXTINCIÓN INCENDIOS 1 AGUILERA ELECTRÓNICA
M10 ASCENSOR Nº 12637 EDIFICIO B 1 KONE ELEVADORES, S.A.
M11 ASCENSOR Nº 12704 Y 12705 EDIFICIO C 1 KONE ELEVADORES, S.A.
M12 ASCENSOR Nº 12680 Y 12681 EDIFICIO B 1 KONE ELEVADORES, S.A.
M13 ASCENSOR Nº 12636 EDIFICIO C 1 KONE ELEVADORES, S.A.
M14 GÓNDOLA Nº 1752 1 MANLIN, S.L.
M15 GÓNDOLA Nº 1745 1 MANLIN, S.L.

The select statement I tried (with too many rows returned, 'M08' is repeated):

select r0.*, e0.nombre as nomempresa from REC r0, CONTR_REC cr0,
CONTR c0, EMP e0 where r0.activo = 1 and r0.IDRECURSO = cr0.IDRECURSO (+)
And cr0.idcontrato = c0.idcontrato (+) and c0.idempresa = e0.idempresa (+) And
c0.fechainicio (+) <= to_date ('29/4/2004','dd-mm-yyyy') AND c0.fechafin (+) >=
to_date ('29/4/2004','dd-mm-yyyy') order by R0.idrecurso

IDRECURSO NOMBRE ACTIVO NOMEMPRESA
M01 Nombre y apellido Operario A 1
M02 Nombre y apellidos operario B 1
M03 SISTEMA DE GESTIÓN INSTALACIONES 1 CONTROLLI TREND
M04 GRUPO P. CONTRA INCENDIOS, BOMBAS WILO Y CALPEDA 1 TALLERES NAVARRETE
M05 GRUPO ELECTRÓGENO 1 SDMO
M06 GÓNDOLA Nº 1744 1 MANLIN, S.L.
M07 EXUTORIOS Y EVACUACIÓN DE HUMOS 1 EURO SISTEMA INCENDIO, S.L.
M08 PLANTAS ENFRIADORAS Y BOMBAS DE CALOR 1 DIFUSORA DEL CLIMA R.R., S.L.
M08 PLANTAS ENFRIADORAS Y BOMBAS DE CALOR 1
M08 PLANTAS ENFRIADORAS Y BOMBAS DE CALOR 1
M09 INSTALACIÓN DETECCIÓN Y EXTINCIÓN INCENDIOS 1 AGUILERA ELECTRÓNICA
M10 ASCENSOR Nº 12637 EDIFICIO B 1 KONE ELEVADORES, S.A.
M11 ASCENSOR Nº 12704 Y 12705 EDIFICIO C 1 KONE ELEVADORES, S.A.
M12 ASCENSOR Nº 12680 Y 12681 EDIFICIO B 1 KONE ELEVADORES, S.A.
M13 ASCENSOR Nº 12636 EDIFICIO C 1 KONE ELEVADORES, S.A.
M14 GÓNDOLA Nº 1752 1 MANLIN, S.L.
M15 GÓNDOLA Nº 1745 1 MANLIN, S.L.

Tom Kyte
May 03, 2004 - 8:25 am UTC

I'll have to recommend a VIEW with the scalar subquery. In that fashion, you'll have one piece of code that works "ok" for both databases.

Without using advanced sql features that sqlserver does not have (analytic functions in particular), I do not see an efficient method of doing this in a generic fashion.

Daniel Garcia, May 03, 2004 - 1:01 pm UTC

First try has been:

CREATE OR REPLACE VIEW PROBA ( IDRECURSO,
NOMBRE) AS
select r0.idrecurso, e0.nombre from EMP e0,CONTR c0,CONTR_REC
cr0,REC r0 where c0.idempresa = e0.idempresa And c0.idcontrato =
cr0.idcontrato And cr0.idRecurso = r0.idRecurso And
c0.fechainicio <=
to_date('30/4/2004','dd-mm-yyyy') AND c0.fechafin >=
to_date('30/4/2004','dd-mm-yyyy');

The problem is that '30/4/2004' is not a fixed value so I've search for parameterized view and I've found </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1448404423206 <code>where you explain that is not posible to pass parameters. This question was with release 8.1, it's now posible to pass parameters to a view? I supose no, so applying your example I create next view:

CREATE OR REPLACE VIEW PROBA ( IDRECURSO,
NOMBRE) AS
select r0.idrecurso, e0.nombre from EMP e0,CONTR c0,CONTR_REC
cr0,REC r0 where c0.idempresa = e0.idempresa And c0.idcontrato =
cr0.idcontrato And cr0.idRecurso = r0.idRecurso And
c0.fechainicio <= userenv('client_info')
AND c0.fechafin >= userenv('client_info');

and then :

exec dbms_application_info.set_client_info(to_date('30/4/2004','dd-mm-yyyy'))
select rec.*,proba.nombre from rec,proba where rec.idrecurso=proba.idrecurso(+) and rec.activo=1;

Anyway I can't apply anythig of this to Sql Sever but at least I now know how to do it with the Oracle. Thanks a lot.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.