Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Values in Column (Oracle 11gR2)
Multiple Values in Column [message #574454] Thu, 10 January 2013 10:12 Go to next message
goody
Messages: 14
Registered: January 2011
Location: Maryland
Junior Member
Hello All,

I've been at this for a few days and need some help please. Most of the code is working properly except when it come to a person with more than one email type in the table:

Below is the Code:

select emal_pidm, emal_email_address, emal_emal_code,
     case 
         
         when emal_emal_code = 'PER'
          and lag(emal_emal_code,1,'?') over (partition by emal_pidm order by emal_email_code) = 'EMPL'
          then emal_email_address
         else regexp_replace(regexp_replace(trim(lower(emal_email_address)),'@xxxx.edu'),'@mymail.xxxx.edu')
          end as LOGON_ID
     from emal
     where emal_emal_code in ('EMPL','PER')
     order by emal_pidm


Below is Sample data for testing code:



INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (1024069, 'emmaus.ferdinand@xxxx.edu','EMPL');

INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (1024069, 'emfer1@xxxx.edu','PER');

INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (1194590, 'ashley.smith@xxxx.edu','EMPL');

INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (3456781, 'tomaicka.cherry@xxxx.edu','EMPL');

INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (3456781, 'tomcher55@xxxx.edu','PER');

INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code)
VALUES (1111110, 'kris.morgan@xxxx.edu','EMPL');


The attachment is what the output should look like.

Any help would be greatly appreciated. I am desperate.


Re: Multiple Values in Column [message #574457 is a reply to message #574454] Thu, 10 January 2013 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain WITH WORDS what should be the result.
And post it INLINE IN TEXT, no image, no attachment but formatted and columns aligned.

Regards
Michel
Re: Multiple Values in Column [message #574469 is a reply to message #574457] Thu, 10 January 2013 11:57 Go to previous messageGo to next message
goody
Messages: 14
Registered: January 2011
Location: Maryland
Junior Member
    If a person has two email types (EMPL and PER) then the EMPL address type should always be the LOGON_ID. For example: If a person has EMPL and PER then the logon_ID should be emmaus.fedinand in the LOGON_ID column for both the EMPL address type and the PER address type.


    If a person has only one email type then the LOGON_ID should be the first part of the email address minus the @xxxx.edu


I'm not sure how to format or align columns in this editor when I have more than one column.

Thanks

[Updated on: Thu, 10 January 2013 11:59]

Report message to a moderator

Re: Multiple Values in Column [message #574470 is a reply to message #574469] Thu, 10 January 2013 12:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2050
Registered: January 2010
Senior Member
select  emal_pidm,
        emal_email_address,
        emal_emal_code,
        replace(
                replace(
                        trim(
                             lower(
                                   case
                                     when count(*)
                                          over(partition by emal_pidm) = 1 -- there is only one email
                                       then emal_email_address
                                     else first_value(emal_email_address)
                                          over(
                                               partition by emal_pidm
                                               order by emal_emal_code
                                              ) -- 'EMPL' is less than 'PER'
                                    end
                                   )
                            ),
                        '@xxxx.edu'
                       ),
                '@mymail.xxxx.edu'
               ) logon_id
  from  emal
  where emal_emal_code in ('EMPL','PER')
  order by emal_pidm
/

EMAL_PIDM            EMAL_EMAIL_ADDRESS             EMAL_EMAL_CODE       LOGON_ID
-------------------- ------------------------------ -------------------- ----------------
1024069              emmaus.ferdinand@xxxx.edu      EMPL                 emmaus.ferdinand
1024069              emfer1@xxxx.edu                PER                  emmaus.ferdinand
1111110              kris.morgan@xxxx.edu           EMPL                 kris.morgan
1194590              ashley.smith@xxxx.edu          EMPL                 ashley.smith
3456781              tomaicka.cherry@xxxx.edu       EMPL                 tomaicka.cherry
3456781              tomcher55@xxxx.edu             PER                  tomaicka.cherry

6 rows selected.

SQL> 


SY.

[Updated on: Thu, 10 January 2013 12:22]

Report message to a moderator

Re: Multiple Values in Column [message #574472 is a reply to message #574469] Thu, 10 January 2013 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm not sure how to format or align columns in this editor when I have more than one column.


From your previous topics:

Michel Cadot wrote on Sat, 15 January 2011 06:57
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data.

Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel


Michel Cadot wrote on Thu, 16 February 2012 20:34
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel


So maybe you should read the links we provided you.

Regards
Michel
Re: Multiple Values in Column [message #574473 is a reply to message #574470] Thu, 10 January 2013 12:45 Go to previous message
goody
Messages: 14
Registered: January 2011
Location: Maryland
Junior Member
Solomon,

The code worked like a charm. Thank you so much. I do understand your code. I really appreciate your help.

Regards,
Previous Topic: Index usage
Next Topic: array value in single quote
Goto Forum:
  


Current Time: Thu Oct 30 09:27:29 CDT 2014

Total time taken to generate the page: 0.10627 seconds