Home » SQL & PL/SQL » SQL & PL/SQL » if in field value "Delete", then don't join other table (Oracle 10g)
| if in field value "Delete", then don't join other table [message #610813] |
Mon, 24 March 2014 11:26  |
 |
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
|
Senior Member |
|
|
Hi
SELECT REPLACE (REGEXP_SUBSTR (T.MESSAGE,
'[^ ]+',
1,
3), '"', '')
|| ';'
|| PERSON.FIRSTNAME
|| ';'
|| PERSON.LASTNAME
|| ';'
|| PERSON.DEPARTMENT
|| ';'
|| PERSON.MAILBOX_ID
|| ';'
|| T.ACTION
|| ';'
|| REPLACE (REGEXP_SUBSTR (T.MESSAGE,
'[^ ]+',
1,
6), '"', '')
|| ';'
|| T.UPD_TIME
|| ';'
|| ORDERID
FROM TRANS T
INNER JOIN IIIP_ENT_USER PERSON
ON REPLACE (REGEXP_SUBSTR (T.MESSAGE,
'[^ ]+',
1,
3), '"', '') = PERSON.USER_ID
INNER JOIN IIIP_USER_JC_ORDERID ORDERID
ON REPLACE (REGEXP_SUBSTR (T.MESSAGE,
'[^ ]+',
1,
3), '"', '') = ORDERID.USER_ID
AND ORDERID.JC_NAME = REPLACE (REGEXP_SUBSTR (T.MESSAGE,
'[^ ]+',
1,
6), '"', '')
WHERE T.ENTITY_TABLE = 'user_jc'
AND TO_CHAR (T.UPD_TIME, 'MM.YYYY') =
TO_CHAR (ADD_MONTHS (SYSDATE, 0), 'MM.YYYY')
AND REPLACE (SUBSTR (T.MESSAGE, INSTR (T.MESSAGE, ' ', -1) + 1),
'"',
'') IN
(SELECT JC_NAME
FROM JOB_CODE
WHERE JC_NAME LIKE '%TEST%')
output
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Insert;55TEST45600001;12-MAR-14;test oder
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Insert;55TEST45600002;24-MAR-14;test oder 2
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Insert;55TEST45600001;24-MAR-14;test oder
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Delete;55TEST45600001;24-MAR-14;test oder
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Delete;55TEST45600002;24-MAR-14;test oder 2
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Insert;55TEST45600002;24-MAR-14;test oder 2
I wish this output:
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Insert;55TEST45600001;12-MAR-14;test oder
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Insert;55TEST45600002;24-MAR-14;test oder 2
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Insert;55TEST45600001;24-MAR-14;test oder
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Delete;55TEST45600001;24-MAR-14;
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Delete;55TEST45600002;24-MAR-14;
RUR4356w233;Larkin;Gantenbein;ORI-KIM-NO-RUR-IIW-2;Larkin.Gantenbein1@foundd.com;Insert;55TEST45600002;24-MAR-14;test oder 2
It means if there is a "Delete" (T.ACTION), then don't join to table IIIP_USER_JC_ORDERID. The ORDERID should be empty.
DDL
SQL> DESC TRANS;
Name Null? Type
----------------------------------------- -------- ----------------------------
SERIAL NOT NULL NUMBER(11)
UPD_TIME NOT NULL DATE
MESSAGE NOT NULL VARCHAR2(255 CHAR)
ENTITY_TABLE NOT NULL VARCHAR2(32 CHAR)
ACTION NOT NULL VARCHAR2(12 CHAR)
STATUS NOT NULL NUMBER(11)
SERIAL_NEG NUMBER(11)
RET_CODE NUMBER(11)
PRE_MSG VARCHAR2(4000 CHAR)
ACT_MSG VARCHAR2(4000 CHAR)
POST_MSG VARCHAR2(4000 CHAR)
PLATFORM_NAME VARCHAR2(64 CHAR)
SHORT_NAME NOT NULL VARCHAR2(2 CHAR)
RSS_NAME VARCHAR2(32 CHAR)
RSS_TYPE VARCHAR2(12 CHAR)
MSG_TYPE VARCHAR2(1 CHAR)
REC_TYPE VARCHAR2(1 CHAR)
COMM_MSG CLOB
ADMIN VARCHAR2(255 CHAR)
ADMIN_GROUP VARCHAR2(255 CHAR)
FIND_KEYS VARCHAR2(700 CHAR)
ESS_USER NOT NULL VARCHAR2(20 CHAR)
SIID VARCHAR2(9 CHAR)
HOT_PATH VARCHAR2(1 CHAR)
ORIGIN VARCHAR2(32 CHAR)
SYNC_PW VARCHAR2(1 CHAR)
INITIAL_LOAD NUMBER(11)
DOWNLOAD_METHOD NUMBER(11)
USER_BY_NAME VARCHAR2(255 CHAR)
USER_BY_PREFIX VARCHAR2(255 CHAR)
USER_BY_UG VARCHAR2(255 CHAR)
OE_BY_NAME CLOB
UG_BY_NAME VARCHAR2(255 CHAR)
USER_ID VARCHAR2(20 CHAR)
RSS_USER_NAME VARCHAR2(511 CHAR)
UG_NAME VARCHAR2(255 CHAR)
RES_ID NUMBER(11)
ACE_ID NUMBER(11)
OE_FULL_NAME VARCHAR2(255 CHAR)
EXTERNAL_ID NUMBER(11)
REMARKS VARCHAR2(2000 CHAR)
ONLINE_MODE NOT NULL NUMBER(11)
PARENT_ID NUMBER(11)
PRIORITY NUMBER(11)
AGENT_RELEASE VARCHAR2(12 CHAR)
HD_SHOULD_CREATE_TICKET VARCHAR2(1 CHAR)
HD_TICKET_ID VARCHAR2(64 CHAR)
HD_TICKET_WAS_PROCESSED VARCHAR2(1 CHAR)
ORIGINAL_COMMAND VARCHAR2(32 CHAR)
TIME_CREATED VARCHAR2(23 CHAR)
TIME_SENT VARCHAR2(23 CHAR)
TIME_STARTED VARCHAR2(23 CHAR)
TIME_AGENT_RESPONDED VARCHAR2(23 CHAR)
TIME_ENDED VARCHAR2(23 CHAR)
SQL> desc IIIP_ENT_USER;
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_ID NOT NULL VARCHAR2(20 CHAR)
REVOKED NOT NULL NUMBER(11)
TITLE VARCHAR2(4000)
FIRSTNAME VARCHAR2(4000)
LASTNAME VARCHAR2(4000)
DEPARTMENT VARCHAR2(4000)
MAILBOX_ID VARCHAR2(4000)
BUSINESS_NUMBER VARCHAR2(4000)
BUSINESS_FAX VARCHAR2(4000)
BUSINESS_NATEL VARCHAR2(4000)
LANGUAGE VARCHAR2(4000)
LEGIC_NO VARCHAR2(4000)
PERS_NO VARCHAR2(4000)
LOCATION VARCHAR2(4000)
PLACE VARCHAR2(4000)
OFFICE VARCHAR2(4000)
PERIS_BEGIN VARCHAR2(4000)
PERIS_END VARCHAR2(4000)
COSTCENTER VARCHAR2(4000)
REVOKED_BY_PERIS VARCHAR2(4000)
REVOKE_TIME VARCHAR2(4000)
LINEMANAGER VARCHAR2(4000)
LINEMANAGERMAIL VARCHAR2(4000)
DISPLAY_NAME VARCHAR2(4000)
USER_NAME NOT NULL VARCHAR2(64 CHAR)
SQL> desc IIIP_USER_JC_ORDERID
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_ID NOT NULL VARCHAR2(20 CHAR)
JC_NAME NOT NULL VARCHAR2(25 CHAR)
UPD_TIME DATE
ORDERID VARCHAR2(4000)
BIZ VARCHAR2(4000)
DATETO VARCHAR2(4000)
|
|
|
|
| Re: if in field value "Delete", then don't join other table [message #610815 is a reply to message #610813] |
Mon, 24 March 2014 11:53  |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
And what would be wrong with using the "DECODE()" function?
. . . E t c . . .
|| ';'
|| PERSON.MAILBOX_ID
|| ';'
|| T.ACTION
|| ';'
|| REPLACE (REGEXP_SUBSTR (T.MESSAGE,
'[^ ]+',
1,
6), '"', '')
|| ';'
|| T.UPD_TIME
|| ';'
|| DECODE( T.ACTION, 'Delete','',ORDERID)
FROM TRANS T
INNER JOIN IIIP_ENT_USER PERSON
ON REPLACE (REGEXP_SUBSTR (T.MESSAGE,
'[^ ]+',
1,
. . . .
[Updated on: Mon, 24 March 2014 12:40] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 23:44:09 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
