Home » SQL & PL/SQL » SQL & PL/SQL » phone type sql
phone type sql [message #665359] Fri, 01 September 2017 06:22 Go to next message
suji6281
Messages: 49
Registered: September 2014
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 Go to previous messageGo to next message
Bill B
Messages: 1718
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 Go to previous messageGo to next message
quirks
Messages: 73
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 #665373 is a reply to message #665369] Fri, 01 September 2017 09:33 Go to previous messageGo to next message
suji6281
Messages: 49
Registered: September 2014
Member
Thanks Bill.
Sql is working perfectly as expected.
Re: phone type sql [message #665416 is a reply to message #665369] Tue, 05 September 2017 06:56 Go to previous messageGo to next message
suji6281
Messages: 49
Registered: September 2014
Member
Hi Bill,
Good Morning!!!

SQL is working fine. But today we come across with different scenario that - It is not mandatory to have FAX phone type. In such scenarios, row is vanishing even if it has BUSN or MAIN or CELL.
Please suggest me with SQL. Thanks You.

Regards
Sekhar
Re: phone type sql [message #665417 is a reply to message #665416] Tue, 05 September 2017 07:22 Go to previous messageGo to next message
suji6281
Messages: 49
Registered: September 2014
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
but using previous solution provided (we included conatct_seq_num in partion condition) we are getting below result.
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 Go to previous messageGo to next message
cookiemonster
Messages: 13016
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 #665420 is a reply to message #665419] Tue, 05 September 2017 07:42 Go to previous messageGo to next message
suji6281
Messages: 49
Registered: September 2014
Member

Thanks for your quick response.
we tried keeping contact_seq_num column to the partition clause of the ROW_NUMBER function. though it was rejecting that don't have FAX records.
Re: phone type sql [message #665421 is a reply to message #665420] Tue, 05 September 2017 08:04 Go to previous messageGo to next message
Bill B
Messages: 1718
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 #665422 is a reply to message #665421] Tue, 05 September 2017 08:05 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
Paste in your query that you are currently using so I can see the latest version
Re: phone type sql [message #665423 is a reply to message #665420] Tue, 05 September 2017 08:07 Go to previous messageGo to next message
cookiemonster
Messages: 13016
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>     
Doesn't quite give what you want, need to modify the JOIN clause so it links the FAX records on contact_seq_num as well:
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 Go to previous messageGo to next message
JPBoileau
Messages: 11
Registered: September 2017
Junior 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 Go to previous messageGo to next message
Bill B
Messages: 1718
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.
Re: phone type sql [message #665429 is a reply to message #665427] Tue, 05 September 2017 08:35 Go to previous messageGo to next message
JPBoileau
Messages: 11
Registered: September 2017
Junior Member
That was the design posted above...

But yes you're correct.

JP
Re: phone type sql [message #665430 is a reply to message #665429] Tue, 05 September 2017 08:41 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
actually the design posted above was to have all the numbers on a single line, not as multiple rows.
Re: phone type sql [message #665450 is a reply to message #665430] Wed, 06 September 2017 05:17 Go to previous message
quirks
Messages: 73
Registered: October 2014
Member
Or just use my statement Wink
Previous Topic: where clause between to sysdates
Next Topic: updated column on the same table automatically
Goto Forum:
  


Current Time: Fri Feb 23 07:13:30 CST 2018

Total time taken to generate the page: 0.02499 seconds