Hi
I have two tables
TMP_REGRA03 (TABLES OF RULES)
TMP_REGRA03_PED
create table TMP_REGRA03
(
id_pai NUMBER(15) not null,
codigo01 VARCHAR2(6) not null,
codigo02 VARCHAR2(60) not null,
tipo VARCHAR2(1) not null,
excecao VARCHAR2(1) default 'N' not null
);
alter table TMP_REGRA03
add constraint TMP_REGRA03_PK primary key (id_pai, codigo01 , codigo02, tipo)
using index ;
insert into TMP_REGRA03 (id_pai, codigo01, codigo02, tipo, excecao)
values (1, 'CODE01', 'DESC CODE01', 'E', 'N');
insert into TMP_REGRA03(id_pai, codigo01, codigo02, tipo, excecao)
values (1, 'CODE02', 'DESC CODE02', 'E', 'S');
insert into TMP_REGRA03 (id_pai, codigo01, codigo02, tipo, excecao)
values (1, 'CODE03', 'DESC CODE03', 'E', 'N');
COMMIT;
CREATE TABLE TMP_REGRA03_PED
(
CODIGO01 VARCHAR2(6 BYTE),
CODIGO02 VARCHAR2(60 BYTE),
TIPO VARCHAR2(1 BYTE)
)
Insert into TMP_REGRA03_PED
(CODIGO01, CODIGO02, TIPO)
Values
('CODE02', 'DESC CODE02', 'E');
Insert into TMP_REGRA03_PED
(CODIGO01, CODIGO02, TIPO)
Values
('CODE01', 'DESC CODE01', 'E');
Insert into TMP_REGRA03_PED
(CODIGO01, CODIGO02, TIPO)
Values
('CODE04', 'DESC CODE04', 'E');
COMMIT;
I have to return points, depending on whether the records in table TMP_REGRA03_PED are found or not in table TMP_REGRA03,
obeying the following rules
first I will define the following in table TMP_REGRA03_PED I will call the concatenation codigo01||codigo02||TIPO with name ROW
and in table TMP_REGRA03 this concatenation codigo01||codigo02||TIPO with name RULE
01)if table TMP_REGRA03_PED is empty
a) if the TMP\_REGRA03 table contains rows and column excecao equal 'N' return -100000
b) if the TMP\_REGRA03 table contains rows and column excecao equal 'S' return 1
c) if the TMP\_REGRA03 table is empty return 1
02)if table TMP_REGRA03_PED contain rows
d) if the TMP\_REGRA03 table is empty return 1
e) if the TMP\_REGRA03 contains rows and RULE not equal ROW and excecao equal 'S' return 1
f) if the TMP\_REGRA03 contains rows and RULE not equal ROW and excecao equal 'N' return -100000
g) if the TMP\_REGRA03 contains rows and RULE are equal ROW and excecao equal 'S' return -100000
h) if the TMP\_REGRA03 contains rows and RULE are equal ROW and excecao equal 'N' return 100
I tried to implement it, but it works in parts, because when I leave any of the tables empty (deleting) it doesn't work,
with tb_count as (
select (select count(*) from TMP_REGRA03_PED) qtd01,
(select count(\*) from TMP\_REGRA03 where ID\_PAI = 1) qtd02,
(select count(\*) from TMP\_REGRA03 where ID\_PAI = 1 and EXCECAO = 'S' ) qtd03,
(select count(\*) from TMP\_REGRA03 where ID\_PAI = 1 and EXCECAO = 'N' ) qtd04 from dual
)
SELECT CASE WHEN C.QTD01 =0 AND C.QTD02 >0 THEN
CASE WHEN A.EXCECAO = 'N' THEN -100000
ELSE 1
END
ELSE
CASE WHEN C.QTD01 >00 AND C.QTD02 >0 THEN
CASE WHEN (COALESCE( NULLIF(A.CODIGO01, b.CODIGO01), NULLIF(A.CODIGO02,b.CODIGO02),NULLIF(A.TIPO,b.TIPO)) is null and a.EXCECAO ='S') OR
(COALESCE( NULLIF(A.CODIGO01, b.CODIGO01), NULLIF(A.CODIGO02,b.CODIGO02),NULLIF(A.TIPO,b.TIPO)) is NOT null and a.EXCECAO ='N') then -100000
ELSE
CASE WHEN (COALESCE( NULLIF(A.CODIGO01, b.CODIGO01), NULLIF(A.CODIGO02,b.CODIGO02),NULLIF(A.TIPO,b.TIPO)) is null and a.EXCECAO ='N') THEN 100
ELSE 1
END
END
END
END POINTS
---THIS COLUMNS BELOW ONLY HELP , WILL BE DISCLOSED
, A.\*, B.\* ,COALESCE( NULLIF(A.CODIGO01, b.CODIGO01), NULLIF(A.CODIGO02,b.CODIGO02),NULLIF(A.TIPO,b.TIPO)) COLUMNHELP
FROM TMP_REGRA03 A
CROSS JOIN TMP_REGRA03_PED B , tb_count C
--- delete TMP_REGRA03_PED
-- delete TMP_REGRA03
Could someone help me implement or indicate what I can do so that even with empty tables one or both return the correct value
the figure below is how I would like the rules to be
using
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Message was edited by: muttleychess forget figure