Home » SQL & PL/SQL » SQL & PL/SQL » phone type sql
| phone type sql [message #665359] |
Fri, 01 September 2017 06:22  |
 |
suji6281
Messages: 134
Registered: September 2014
|
Senior Member |
|
|
Hi Team,
We have a table PHONE_TBL with phone_type as a field. The values in the phone_type are BUSN, MAIN, CELL, FAX.
We need to create a report which takes the phone number as per the order preference.
If BUSN is populated then the phone number should be BUSN number else MAIN (if value exists) else CELL (if value exists).
We need to achieve this using a sql.
Final Output should be as below:
vendorId Name Phone_Type Phone FAX
V12345 V12345 BUSN 473-781-1111 473-781-5555
V12346 V12346 MAIN 572-127-5478 572-127-5480
V12347 V12347 CELL 317-259-1541 317-259-1115
CREATE TABLE PHONE_TBL (
vendorId varchar(6),
Name varchar(10),
Phone_Type varchar(4),
PHONE varchar(12)
);
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12345', 'V12345', 'BUSN', '473-781-1111');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12345', 'V12345', 'MAIN', '473-781-2222');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12345', 'V12345', 'CELL', '473-781-3333');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12345', 'V12345', 'FAX', '473-781-5555');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12346', 'V12346', 'MAIN', '572-127-5478');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12346', 'V12346', 'CELL', '572-127-5479');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12346', 'V12346', 'FAX', '572-127-5480');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12347', 'V12347', 'CELL', '317-259-1541');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12347', 'V12347', 'FAX', '317-259-1115');
SELECT * FROM PHONE_TBL;
vendorId Name PhoneType Phone
V12345 V12345 BUSN 473-781-1111
V12345 V12345 MAIN 473-781-2222
V12345 V12345 CELL 473-781-3333
V12345 V12345 FAX 473-781-5555
V12346 V12346 MAIN 572-127-5478
V12346 V12346 CELL 572-127-5479
V12346 V12346 FAX 572-127-5480
V12347 V12347 CELL 317-259-1541
V12347 V12347 FAX 317-259-1115
--moderator update: I've corrected your typing error in the CREATE TABLE. Please test your code before posting it.
[Updated on: Fri, 01 September 2017 07:58] by Moderator Report message to a moderator |
|
|
|
| Re: phone type sql [message #665369 is a reply to message #665359] |
Fri, 01 September 2017 08:46  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
SELECT Vendorid,
Name,
Phone_type,
Phone,
Fax
FROM (SELECT A.Vendorid,
A.Name,
A.Phone_type,
A.Phone,
ROW_NUMBER ()
OVER (
PARTITION BY A.Vendorid
ORDER BY
CASE
WHEN A.Phone_type = 'BUSN' THEN 1
WHEN A.Phone_type = 'MAIN' THEN 2
WHEN A.Phone_type = 'CELL' THEN 3
ELSE 4
END ASC)
Rn,
B.Phone
Fax
FROM Phone_tbl A
LEFT OUTER JOIN Phone_tbl B
ON A.Vendorid = B.Vendorid AND B.Phone_type = 'FAX')
WHERE Rn = 1
ORDER BY Vendorid;[Updated on: Fri, 01 September 2017 08:53] Report message to a moderator |
|
|
|
| Re: phone type sql [message #665371 is a reply to message #665369] |
Fri, 01 September 2017 09:19 |
 |
quirks
Messages: 82
Registered: October 2014
|
Member |
|
|
WITH
PHONE_TBL
AS
(SELECT 'V12345' AS VENDORID, 'V12345' AS NAME, 'BUSN' AS PHONE_TYPE, '473-781-1111' AS PHONE FROM DUAL
UNION ALL
SELECT 'V12345', 'V12345', 'MAIN', '473-781-2222' FROM DUAL
UNION ALL
SELECT 'V12345', 'V12345', 'CELL', '473-781-3333' FROM DUAL
UNION ALL
SELECT 'V12345', 'V12345', 'FAX', '473-781-5555' FROM DUAL
UNION ALL
SELECT 'V12346', 'V12346', 'MAIN', '572-127-5478' FROM DUAL
UNION ALL
SELECT 'V12346', 'V12346', 'CELL', '572-127-5479' FROM DUAL
UNION ALL
SELECT 'V12346', 'V12346', 'FAX', '572-127-5480' FROM DUAL
UNION ALL
SELECT 'V12347', 'V12347', 'CELL', '317-259-1541' FROM DUAL
UNION ALL
SELECT 'V12347', 'V12347', 'FAX', '317-259-1115' FROM DUAL
UNION ALL
SELECT 'V12348', 'V12347', 'FAX', '317-259-1234' FROM DUAL
UNION ALL
SELECT 'V12349', 'V12347', 'MAIN', '317-259-9999' FROM DUAL)
SELECT VENDORID
,NAME
,CASE
WHEN BUSN IS NOT NULL THEN 'BUSN'
WHEN MAIN IS NOT NULL THEN 'MAIN'
WHEN CELL IS NOT NULL THEN 'CELL' END PHONE_TYPE
,COALESCE(BUSN, MAIN, CELL) AS PHONE
,FAX
FROM PHONE_TBL PIVOT (MAX(PHONE) FOR PHONE_TYPE IN ('BUSN' AS BUSN, 'MAIN' AS MAIN, 'CELL' AS CELL, 'FAX' AS FAX))
ORDER BY VENDORID |
|
|
|
|
|
|
|
| Re: phone type sql [message #665417 is a reply to message #665416] |
Tue, 05 September 2017 07:22 |
|
suji6281
Messages: 134
Registered: September 2014
|
Senior Member |
|
|
Hi Bill,
for your information, modified PHONE_TBL by adding 'contact_seq_num' field and provided sample data.
CREATE TABLE PHONE_TBL (
vendorId varchar(6),
contact_seq_num int,
Name varchar(10),
PhoneType varchar(4),
PHONE varchar(12)
);
INSERT INTO PHONE_TBL (vendorId, contact_seq_num, Name, PhoneType, PHONE) VALUES ('V12348', 1, 'V12348', 'BUSN', '317-259-1121');
INSERT INTO PHONE_TBL (vendorId, contact_seq_num, Name, PhoneType, PHONE) VALUES ('V12348', 2, 'V12348', 'FAX', '317-259-1122');
INSERT INTO PHONE_TBL (vendorId, contact_seq_num, Name, PhoneType, PHONE) VALUES ('V12348', 2, 'V12348', 'BUSN', '317-259-1123');
Output should be as below:
vendorId contact_seq_num Name PhoneType Phone FAX
V12348 1 V12348 BUSN 317-259-1121
V12348 2 V12348 BUSN 317-259-1123 317-259-1122
vendorId contact_seq_num Name PhoneType Phone FAX
V12348 2 V12348 BUSN 317-259-1123 317-259-1122
Regards
Sekhar
|
|
|
|
| Re: phone type sql [message #665419 is a reply to message #665417] |
Tue, 05 September 2017 07:30 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Bill's query will not reject vendors that don't have FAX records because he uses an outer-join.
To make it work with contact_seq_num just add that column to the partition clause of the ROW_NUMBER function.
|
|
|
|
|
|
| Re: phone type sql [message #665421 is a reply to message #665420] |
Tue, 05 September 2017 08:04 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
The problem is that you needed to add it to the outer join
FROM Phone_tbl A
LEFT OUTER JOIN Phone_tbl B
ON A.Vendorid = B.Vendorid AND B.Phone_type = 'FAX' AND A.contact_seq_num = B.contact_seq_num)
|
|
|
|
|
|
| Re: phone type sql [message #665423 is a reply to message #665420] |
Tue, 05 September 2017 08:07 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You must have done something else to lose the FAX records, because that works:
SQL> SELECT Vendorid,
2 Name,
3 Phonetype,
4 Phone,
5 Fax,
6 contact_seq_num
7 FROM (SELECT A.Vendorid,
8 A.Name,
9 A.Phonetype,
10 A.Phone,
11 a.contact_seq_num,
12 ROW_NUMBER ()
13 OVER (
14 PARTITION BY A.Vendorid, a.contact_seq_num
15 ORDER BY
16 CASE
17 WHEN A.Phonetype = 'BUSN' THEN 1
18 WHEN A.Phonetype = 'MAIN' THEN 2
19 WHEN A.Phonetype = 'CELL' THEN 3
20 ELSE 4
21 END ASC)
22 Rn,
23 B.Phone
24 Fax
25 FROM Phone_tbl A
26 LEFT OUTER JOIN Phone_tbl B
27 ON A.Vendorid = B.Vendorid AND B.Phonetype = 'FAX')
28 WHERE Rn = 1
29 ORDER BY Vendorid;
VENDORID NAME PHONETYPE PHONE FAX CONTACT_SEQ_NUM
-------- ---------- --------- ------------ ------------ ---------------------------------------
V12348 V12348 BUSN 317-259-1121 317-259-1122 1
V12348 V12348 BUSN 317-259-1123 317-259-1122 2
SQL>
SQL> SELECT Vendorid,
2 Name,
3 Phonetype,
4 Phone,
5 Fax,
6 contact_seq_num
7 FROM (SELECT A.Vendorid,
8 A.Name,
9 A.Phonetype,
10 A.Phone,
11 a.contact_seq_num,
12 ROW_NUMBER ()
13 OVER (
14 PARTITION BY A.Vendorid, a.contact_seq_num
15 ORDER BY
16 CASE
17 WHEN A.Phonetype = 'BUSN' THEN 1
18 WHEN A.Phonetype = 'MAIN' THEN 2
19 WHEN A.Phonetype = 'CELL' THEN 3
20 ELSE 4
21 END ASC)
22 Rn,
23 B.Phone
24 Fax
25 FROM Phone_tbl A
26 LEFT OUTER JOIN Phone_tbl B
27 ON A.Vendorid = B.Vendorid AND a.contact_seq_num = b.contact_seq_num and B.Phonetype = 'FAX')
28 WHERE Rn = 1
29 ORDER BY Vendorid;
VENDORID NAME PHONETYPE PHONE FAX CONTACT_SEQ_NUM
-------- ---------- --------- ------------ ------------ ---------------------------------------
V12348 V12348 BUSN 317-259-1121 1
V12348 V12348 BUSN 317-259-1123 317-259-1122 2
SQL>
|
|
|
|
| Re: phone type sql [message #665425 is a reply to message #665423] |
Tue, 05 September 2017 08:25 |
 |
JPBoileau
Messages: 88
Registered: September 2017
|
Member |
|
|
Having a PHONE_TYPE table along with an ORDER would simplify things somewhat.
-- DROP TABLE PHONE_TYPE;
CREATE TABLE PHONE_TYPE (
PHONE_TYPE VARCHAR2(4) PRIMARY KEY,
PHONE_ORDER NUMBER(1) );
INSERT INTO PHONE_TYPE VALUES ('BUSN', 1);
INSERT INTO PHONE_TYPE VALUES ('MAIN', 2);
INSERT INTO PHONE_TYPE VALUES ('CELL', 3);
INSERT INTO PHONE_TYPE VALUES ('FAX', 4);
COMMIT;
-- DROP TABLE PHONE_TBL;
CREATE TABLE PHONE_TBL (
vendorId varchar(6),
Name varchar(10),
Phone_Type varchar(4) REFERENCES PHONE_TYPE,
PHONE varchar(12)
);
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12345', 'V12345', 'BUSN', '473-781-1111');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12345', 'V12345', 'MAIN', '473-781-2222');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12345', 'V12345', 'CELL', '473-781-3333');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12345', 'V12345', 'FAX', '473-781-5555');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12346', 'V12346', 'MAIN', '572-127-5478');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12346', 'V12346', 'CELL', '572-127-5479');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12346', 'V12346', 'FAX', '572-127-5480');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12347', 'V12347', 'CELL', '317-259-1541');
INSERT INTO PHONE_TBL (vendorId, Name, Phone_Type, PHONE) VALUES ('V12347', 'V12347', 'FAX', '317-259-1115');
SELECT * FROM (
SELECT VENDORID, NAME, P.PHONE_TYPE, PHONE, PHONE_ORDER, MIN(T.PHONE_ORDER) OVER (PARTITION BY VENDORID) AS MIN_PHONE_ORDER
FROM PHONE_TBL P, PHONE_TYPE T
WHERE
P.PHONE_TYPE = T.PHONE_TYPE)
WHERE PHONE_ORDER = MIN_PHONE_ORDER
ORDER BY VENDORID, NAME;
VENDOR NAME PHON PHONE PHONE_ORDER MIN_PHONE_ORDER
------ ---------- ---- ------------ ----------- ---------------
V12345 V12345 BUSN 473-781-1111 1 1
V12346 V12346 MAIN 572-127-5478 2 2
V12347 V12347 CELL 317-259-1541 3 3
JP
|
|
|
|
| Re: phone type sql [message #665427 is a reply to message #665425] |
Tue, 05 September 2017 08:28 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
While you would need the vendor ID in the phone table, you do NOT want to duplicate the vendor name that will also be in the vendor table. Do not store duplicate data in multiple tables.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 23:42:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
