I have two tables like these:
active
orderno status state name
204 N 0 DANDO A SIN_CONDICION
190 N L JOB_AGENTE_SLEEP_200M
203 N 0 SIN_CONDICION
206 N C EJEMPLO_2
182 N L JOB_AGENTE
195 Y 8 BIENHECHO
186 N L JOB_AGENTE_SLEEP_20M
189 N L JOB_AGENTE_SLEEP_5S
205 Y 8 EJEMPLO_1
184 N L SSL_AGENTE920
183 N L LIFECONTROLM_JOB1_920
191 N L MFTAGENTEPRIMERO
187 N L SSL_AGENTE931
198 N B PTE_HORA_Y_CONDICION
194 N L MFTAGENTESECUNDARIO
201 N B COND2
197 N B PTE_HORA
192 N L 931a920
185 N L LS_AGENTE920_OTRA_EJECUCION
188 N L AGENTE921
200 Y 8 COND1
196 Y 8 BIENHECHO2
193 N C SIN_CONDICION
202 Y 8 DANDO A SIN_CONDICION
199 Y 8 SIN_CONDICION
cmr
cond orderno rowtype date op
SIN_CONDICION 193 I 1107 A
COND1-TO-SIN_CONDICION 193 I 1107 A
COND2-TO-SIN_CONDICION 193 I 1107 A
COND1-TO-SIN_CONDICION 193 O 1107 -
COND2-TO-SIN_CONDICION 193 O 1107 -
BIENHECHO-TO-BIENHECHO2 195 O 1107 +
BIENHECHO-TO-BIENHECHO2 196 I 1107 A
BIENHECHO-TO-BIENHECHO2 196 O 1107 -
PTE_HORA_Y_CONDICION 198 I 1107 A
SIN_CONDICION 199 I 1107 A
SIN_CONDICION 199 O 1107 -
COND1-TO-PTE_HORA_Y_CONDICION 200 O 1107 +
COND1-TO-SIN_CONDICION 200 O 1107 +
COND2-TO-SIN_CONDICION 201 O 1107 +
SIN_CONDICION 202 O 1107 +
SIN_CONDICION 203 I 1107 A
SIN_CONDICION2 203 I 1107 A
SIN_CONDICION2 203 O 1107 -
SIN_CONDICION 204 O 1107 +
EJEMPLO_1-TO-EJEMPLO_2 205 O 1107 +
EJEMPLO_1-TO-EJEMPLO_2 206 I 1107 A
CONDICION3 206 I 1107 A
EJEMPLO_1-TO-EJEMPLO_2 206 O 1107 -
I need these premises: select only lines that have: Row with name rowtype:I and op:A Don't exists another row with same name, same date and rowtype:O and op:+
Then get the orderno of the row and get name from the other table active.
I'm trying to put an example:
CONDICION3 have row rowtype:I and op:A with date 1107.
don't have rowtype:O and op:+ with date 1107
CONDICION3 meets requirementes, I search oderno in active table and it says me the name i'ts EJEMPLO_2
EJEMPLO_1-TO-EJEMPLO_2 have row I-A with date 1107
have row O-+ with date 1107
Nothing happens
Could you help me please? Thanks
You can use Not Exists query to exclude rows:
WITH -- S a m p l e d a ta :
active (ORDERNO, STATUS, A_STATE, A_NAME) AS
( Select 204, 'N', '0', 'DANDO A SIN_CONDICION' From Dual Union All
Select 190, 'N', 'L', 'JOB_AGENTE_SLEEP_200M' From Dual Union All
Select 203, 'N', '0', 'SIN_CONDICION' From Dual Union All
Select 206, 'N', 'C', 'EJEMPLO_2' From Dual Union All
Select 182, 'N', 'L', 'JOB_AGENTE' From Dual Union All
Select 195, 'Y', '8', 'BIENHECHO' From Dual Union All
Select 186, 'N', 'L', 'JOB_AGENTE_SLEEP_20M' From Dual Union All
Select 189, 'N', 'L', 'JOB_AGENTE_SLEEP_5S' From Dual Union All
Select 205, 'Y', '8', 'EJEMPLO_1' From Dual Union All
Select 184, 'N', 'L', 'SSL_AGENTE920' From Dual Union All
Select 183, 'N', 'L', 'LIFECONTROLM_JOB1_920' From Dual Union All
Select 191, 'N', 'L', 'MFTAGENTEPRIMERO' From Dual Union All
Select 187, 'N', 'L', 'SSL_AGENTE931' From Dual Union All
Select 198, 'N', 'B', 'PTE_HORA_Y_CONDICION' From Dual Union All
Select 194, 'N', 'L', 'MFTAGENTESECUNDARIO' From Dual Union All
Select 201, 'N', 'B', 'COND2' From Dual Union All
Select 197, 'N', 'B', 'PTE_HORA' From Dual Union All
Select 192, 'N', 'L', '931a920' From Dual Union All
Select 185, 'N', 'L', 'LS_AGENTE920_OTRA_EJECUCION' From Dual Union All
Select 188, 'N', 'L', 'AGENTE921' From Dual Union All
Select 200, 'Y', '8', 'COND1' From Dual Union All
Select 196, 'Y', '8', 'BIENHECHO2' From Dual Union All
Select 193, 'N', 'C', 'SIN_CONDICION' From Dual Union All
Select 202, 'Y', '8', 'DANDO A SIN_CONDICION' From Dual Union All
Select 199, 'Y', '8', 'SIN_CONDICION' From Dual
),
cmr (COND, ORDERNO, A_ROWTYPE, A_DATE, OP) AS
( Select 'SIN_CONDICION', 193, 'I', '1107', 'A' From Dual Union ALl
Select 'COND1-TO-SIN_CONDICION', 193, 'I', '1107', 'A' From Dual Union ALl
Select 'COND2-TO-SIN_CONDICION', 193, 'I', '1107', 'A' From Dual Union ALl
Select 'COND1-TO-SIN_CONDICION', 193, 'O', '1107', '-' From Dual Union ALl
Select 'COND2-TO-SIN_CONDICION', 193, 'O', '1107', '-' From Dual Union ALl
Select 'BIENHECHO-TO-BIENHECHO2', 195, 'O', '1107', '+' From Dual Union ALl
Select 'BIENHECHO-TO-BIENHECHO2', 196, 'I', '1107', 'A' From Dual Union ALl
Select 'BIENHECHO-TO-BIENHECHO2', 196, 'O', '1107', '-' From Dual Union ALl
Select 'PTE_HORA_Y_CONDICION', 198, 'I', '1107', 'A' From Dual Union ALl
Select 'SIN_CONDICION', 199, 'I', '1107', 'A' From Dual Union ALl
Select 'SIN_CONDICION', 199, 'O', '1107', '-' From Dual Union ALl
Select 'COND1-TO-PTE_HORA_Y_CONDICION', 200, 'O', '1107', '+' From Dual Union ALl
Select 'COND1-TO-SIN_CONDICION', 200, 'O', '1107', '+' From Dual Union ALl
Select 'COND2-TO-SIN_CONDICION', 201, 'O', '1107', '+' From Dual Union ALl
Select 'SIN_CONDICION', 202, 'O', '1107', '+' From Dual Union ALl
Select 'SIN_CONDICION', 203, 'I', '1107', 'A' From Dual Union ALl
Select 'SIN_CONDICION2', 203, 'I', '1107', 'A' From Dual Union ALl
Select 'SIN_CONDICION2', 203, 'O', '1107', '-' From Dual Union ALl
Select 'SIN_CONDICION', 204, 'O', '1107', '+' From Dual Union ALl
Select 'EJEMPLO_1-TO-EJEMPLO_2', 205, 'O', '1107', '+' From Dual Union ALl
Select 'EJEMPLO_1-TO-EJEMPLO_2', 206, 'I', '1107', 'A' From Dual Union ALl
Select 'CONDICION3', 206, 'I', '1107', 'A' From Dual Union ALl
Select 'EJEMPLO_1-TO-EJEMPLO_2', 206, 'O', '1107', '-' From Dual
)
Below is the code that takes from cmr all rows with A_ROWTYPE = 'I' And OP = 'A' if there are no rows, for same ORDERNO and A_DATE, having A_ROWTYPE = 'O' And OP = '+'. Not sure if that's what you need but (if I missunderstood something) you can adjust the conditions the way you need them to be:
-- M a i n S Q L :
Select a.A_NAME,
c.COND, c.ORDERNO, c.A_ROWTYPE, c.A_DATE, c.OP
From cmr c
Left Join active a ON(a.ORDERNO = c.ORDERNO)
Where c.A_ROWTYPE = 'I' And
c.OP = 'A' And
Not Exists ( Select 1
From cmr
Where ORDERNO = c.ORDERNO And
A_DATE = c.A_DATE And
A_ROWTYPE = 'O' And
OP = '+'
)
/* R e s u l t :
A_NAME COND ORDERNO A_ROWTYPE A_DATE OP
--------------------------- ----------------------------- ---------- --------- ------ --
SIN_CONDICION SIN_CONDICION 203 I 1107 A
SIN_CONDICION SIN_CONDICION2 203 I 1107 A
EJEMPLO_2 EJEMPLO_1-TO-EJEMPLO_2 206 I 1107 A
EJEMPLO_2 CONDICION3 206 I 1107 A
PTE_HORA_Y_CONDICION PTE_HORA_Y_CONDICION 198 I 1107 A
BIENHECHO2 BIENHECHO-TO-BIENHECHO2 196 I 1107 A
SIN_CONDICION SIN_CONDICION 193 I 1107 A
SIN_CONDICION COND1-TO-SIN_CONDICION 193 I 1107 A
SIN_CONDICION COND2-TO-SIN_CONDICION 193 I 1107 A
SIN_CONDICION SIN_CONDICION 199 I 1107 A */
Not sure, but it might be that for Not Exists query, it could be that you want COND as condition instead of ORDERNO. In that case the code would be:
-- M i n S Q L :
Select a.A_NAME,
c.COND, c.ORDERNO, c.A_ROWTYPE, c.A_DATE, c.OP
From cmr c
Left Join active a ON(a.ORDERNO = c.ORDERNO)
Where c.A_ROWTYPE = 'I' And
c.OP = 'A' And
Not Exists ( Select 1
From cmr
Where COND = c.COND And
A_DATE = c.A_DATE And
A_ROWTYPE = 'O' And
OP = '+'
)
/* R e s u l t :
A_NAME COND ORDERNO A_ROWTYPE A_DATE OP
--------------------------- ----------------------------- ---------- --------- ------ --
SIN_CONDICION SIN_CONDICION2 203 I 1107 A
EJEMPLO_2 CONDICION3 206 I 1107 A
PTE_HORA_Y_CONDICION PTE_HORA_Y_CONDICION 198 I 1107 A */
Without ORDERNO or COND as conditions in Not Exists query:
-- M i n S Q L :
Select a.A_NAME,
c.COND, c.ORDERNO, c.A_ROWTYPE, c.A_DATE, c.OP
From cmr c
Left Join active a ON(a.ORDERNO = c.ORDERNO)
Where c.A_ROWTYPE = 'I' And
c.OP = 'A' And
Not Exists ( Select 1
From cmr
Where A_DATE = c.A_DATE And
A_ROWTYPE = 'O' And
OP = '+'
)
-- R e s u l t :
-- No rows selected