| oracle query [message #25217] |
Thu, 03 April 2003 08:58  |
Bharat Mehta
Messages: 4
Registered: February 2002
|
Junior Member |
|
|
I have two tables, peoplesoft and cisemployee
I need to find (ps_soc_sec) from peoplesoft
which does not match (have) in (emp_ssn) cisemployee table.
I have provide elements of both tables below
SQL> desc cisemployee;
Name Null? Type
------------------------------- -------- ----
EMP_EMP_ID NUMBER
EMP_COMPANY_ID NUMBER
EMP_SFC_ID NUMBER
EMP_INSUR_REG NUMBER
EMP_BOFF_ID NUMBER
EMP_ECA CHAR(10)
EMP_TYPE_ID NUMBER
EMP_FIRST_NAME CHAR(32)
EMP_MI CHAR(1)
EMP_LAST_NAME CHAR(32)
EMP_MNGR_EMP_ID NUMBER
EMP_BUT_BUSUNIT_ID NUMBER
EMP_DEP_ID NUMBER
EMP_SSN CHAR(11)
EMP_TITLE CHAR(32)
EMP_IC_REG CHAR(10)
EMP_BONUS_LEV NUMBER
EMP_HIRE_DATE DATE
EMP_TERM_DATE DATE
EMP_WORK_PHONE CHAR(14)
EMP_WORK_EXT CHAR(4)
EMP_ACD_PHONE CHAR(4)
EMP_BEEPER CHAR(14)
EMP_BEEPER_PIN CHAR(10)
EMP_MSG_CENTER CHAR(14)
EMP_CAR_PHONE CHAR(14)
EMP_CELLULAR_PHONE CHAR(14)
EMP_HOME_PHONE CHAR(14)
EMP_ALT_PHONE CHAR(14)
EMP_FAX CHAR(14)
EMP_ALT_FAX CHAR(14)
EMP_E_MAIL CHAR(32)
EMP_VOICE_MAIL CHAR(14)
EMP_COVERED CHAR(1)
EMP_COMMENT VARCHAR2(50)
EMP_STATUS NUMBER
EMP_UPDATE_DATE DATE
EMP_UPDATE_LOGIN CHAR(18)
EMP_MNGR_REP_NO CHAR(5)
Text continued in next action...
02-APR-03 20:55:21 GMT
Text continued from previous action...
EMP_INSTALL_DATE DATE
EMP_SENIOR_FLAG CHAR(1)
EMP_LAST_PAY_DATE DATE
SQL> desc peoplesoft;
Name Null? Type
------------------------------- -------- ----
PS_GIDNUM VARCHAR2(11)
PS_SOC_SEC VARCHAR2(20)
PS_FIRSTNAME VARCHAR2(30)
PS_MID_INIT VARCHAR2(30)
PS_LAST_NAME VARCHAR2(30)
PS_EMP_STATUS VARCHAR2(1)
PS_HIRE_DATE DATE
PS_TERM_DATE DATE
PS_PFA_COSTCTR VARCHAR2(10)
PS_WORK_PHONE VARCHAR2(50)
PS_BRANCH_FAX VARCHAR2(50)
PS_EXTERNAL_EMAIL VARCHAR2(100)
PS_INTERNAL_EMAIL VARCHAR2(100)
LOAD_DATE NOT NULL DATE
ps_soc_sec are nine digit ex. 123456789
emp_ssn are mixed data like
123456789
123-45-6789
If I could retrieve all records which are common in both is ok.
I also need query which will find ps_soc_sec which are not in cisemployee
table.
I have tried using substr(emp_ssn, 1,9), ltrim(rtrim(emp_ssn) to get common
data.
Please help.
|
|
|
|
| Re: oracle query [message #25219 is a reply to message #25217] |
Thu, 03 April 2003 09:25  |
Ivan
Messages: 180
Registered: June 2000
|
Senior Member |
|
|
This will give you the SSN numbers that exist in peoplesoft, but not in cisemployee table.
SELECT PS_SOC_SEC
FROM peoplesoft p
WHERE NOT EXISTS (SELECT 1
FROM cisemployee c
WHERE REPLACE (c.emp_ssn, '-') = p.ps_soc_sec)
As per records that exist in both tables, you'll have to define the criteria, which makes the records a "match"
Regards
|
|
|
|
| Re: oracle query [message #25222 is a reply to message #25217] |
Thu, 03 April 2003 10:49 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
Try this out
SELECT A.PS_FIRST_NAME,A.PS_MID_INIT,A.PS_LAST_NAME,
B.EMP_EMP_ID,B.EMP_SSN
FROM PEOPLESOFT A, CISEMPLOYEE B
WHERE A.PS_SOC_SEC = REPLACE(B.EMP_SSN,'-','')(+);
I dont remeber, whether I can use (+) preceding with functional value, but try it out and let me know how it helps you.
Change the list of SELECT columns as per your requirement, as this is only an example to show how we can use an outer join to resolve your query.
Note: the ones I used in REPLACE command are two single quotes (but not one double-quote) to replace with a null wherever it finds a - in the emp_ssn.
Good luck :)
|
|
|
|
| Re: oracle query [message #25230 is a reply to message #25222] |
Thu, 03 April 2003 21:02 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In order for that method to return the rows from peoplesoft where there is not a corresponding social security number in cisemployee, you will need to add an IS NULL condition and the (+) needs to be immediately after the column name:
SELECT a.ps_first_name, a.ps_mid_init, a.ps_last_name,
b.emp_emp_id, b.emp_ssn
FROM peoplesoft a, cisemployee b
WHERE a.ps_soc_sec = REPLACE (b.emp_ssn (+), '-', '')
AND b.emp_ssn IS NULL;
|
|
|
|
| Re: oracle query [message #25241 is a reply to message #25222] |
Fri, 04 April 2003 09:16 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
Barbara,
You are right in the syntax for outer join where the (+) should appear within REPLACE() rather than following it.
But if you add an AND condtion in the WHERE clause prevents listing the matching rows in PPS and EMPLOYEE;
As Bharat wants to list the rows those are common in both and also non-matching rows from employee table. I think the AND condition is not necessary.
Check out the following example.
SQL> select * from temp10;
EMPNO SSN
--------- ---------------
123 123-34-4567
124 234-45-5678
125 345-56-6789
126 456-67-7890
SQL> select * from temp20;
PPSNO EMPNO SSN
--------- --------- ---------
101 123 123344567
102 124 234455678
103 125 345566789
104 126 456677890
105 127 321123344
106 128
6 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select emp.empno, emp.ssn, pps.empno, pps.ssn from temp10 emp, temp20 pps
2* where pps.ssn = replace(emp.ssn(+),'-','')
SQL> /
EMPNO SSN EMPNO SSN
--------- --------------- --------- ---------
123 123-34-4567 123 123344567
124 234-45-5678 124 234455678
127 321123344
125 345-56-6789 125 345566789
126 456-67-7890 126 456677890
128
6 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select emp.empno, emp.ssn, pps.empno, pps.ssn from temp10 emp, temp20 pps
2* where pps.ssn = replace(emp.ssn(+),'-','') and emp.ssn is null
SQL> /
EMPNO SSN EMPNO SSN
--------- --------------- --------- ---------
127 321123344
128
SQL>
Check it out.
Good luck :)
|
|
|
|
| Re: oracle query [message #25248 is a reply to message #25241] |
Fri, 04 April 2003 21:11 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
|
The wording of my response was poor. What I meant to say was that you would need the IS NULL condition in order to return ONLY the non-matching rows. Using an outer join without the IS NULL condition, you get those non-matching rows and you also get the rows that match, as you have demonstrated. I am not sure which he prefers. It sounded like he was looking to identify the mis-matches alone, but was willing to settle for anything that included them.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
