Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 11 May 2016

Query To Get Employee Address Details

SELECT   ppt.user_person_type person_type, papf.full_name employee_name,
         papf.employee_number employee_number, pa.address_line1,
         pa.address_line2, pa.address_line3, pa.town_or_city, pa.postal_code,
         pa.country
    FROM per_all_people_f papf,
         per_all_assignments_f paaf,
         per_addresses pa,
         per_person_types_tl ppt
   WHERE papf.person_id = paaf.person_id
     AND paaf.primary_flag = 'Y'
     --AND PAPF.EMPLOYEE_NUMBER = '30987'
     AND papf.current_employee_flag = 'Y'
     AND papf.business_group_id = paaf.business_group_id
     AND paaf.person_id = pa.person_id
     AND papf.person_type_id = ppt.person_type_id
     AND TRUNC (SYSDATE) BETWEEN pa.date_from
                             AND NVL (pa.date_to, TRUNC (SYSDATE) + 1)
     AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                             AND papf.effective_end_date
     AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                             AND paaf.effective_end_date
--   AND PAPF.FULL_NAME LIKE 'A%'

ORDER BY papf.full_name;
Share this article :

3 comments:

  1. Hi Dayakar,

    Grazie! Grazie! Grazie! Your blog is indeed quite interesting around Query to Get Employee Address Details! I agree with you on lot of points!

    I posted before regarding using oracle UDT stored procedure parameter type to pass a list of records for multiple insert using .net c#.
    I had the below example where a class definition is needed at .net side to map to oracle object. Unfortunately, I did not apply this due to the extra mapping code that is needed.
    Oracle’s wrap utility provides a good way of hiding PL/SQL source code; protecting it and making it difficult for others to view. You can either wrap source code with the wrap utility or use the DBMS_DDL subprograms.
    http://appsjack.blogspot.com.eg/2010/09/pass-custom-udt-types-to-oracle-stored.html
    My question is: did this change with oracle 12c? Can we now use:
    parameter.UdtTypeName = directOracleUDTName; //without the need for .net definition and mapping classes

    Once again thanks for your tutorial.

    Best Regards,
    Lee

    ReplyDelete
  2. Hello There,

    I’ve often thought about this Query To Get Employee Address Details . Nice to have it laid out so clearly. Great eye opener.

    There are 3 big files, 1GB (file_a.txt), 10GB (file_b.txt) and 1TB (file_c.txt);
    - The format of these 3 files: each line with a random string in the file;
    - There is only 100MB memory could be used, disk usage is not limited;
    - Assumption:
    IF AND ONLY IF string A appears within all 3 files, we need to count the total appearing times of this A. Such as, A appears 2 times within file_a.txt, appears 10 times within file_b.txt, appears 100 times within file_c.txt, then we count the total appearing times of A as 2 + 10 + 100 = 112 times.

    Question: please write a program to output the strings with TOP 10 and LAST 10 appearing times in descending order.

    Super likes !!! for this amazing post. I thinks everyone should bookmark this.

    Many Thanks,
    Preethi.

    ReplyDelete
  3. Hi Bro,


    What you’re saying is absolutely correct #topic, but this isn’t the exact situation everywhere. Where most smart folk work on a project - why can’t you do this the Boss asks :).


    from foll. select, how can i get unique values for screen_type and screen

    select a.EMP_NO,a.SCREEN, b.SCREEN_TYPE
    from EMPLOYEE_TAB a, SCREEN_TAB b
    where a.id = b.ID
    and SCREEN_TYPE like '%S';

    EMP_ID SCREEN SCREEN_TYPE
    EMP_123 SCR100 SCRTYPE100S
    EMP_124 SCR100 SCRTYPE100S
    EMP_125 SCR100 SCRTYPE100S
    EMP_127 SCR102 SCRTYPE102S
    EMP_128 SCR102 SCRTYPE102S
    EMP_135 SCR102 SCRTYPE102S
    EMP_136 SCR102 SCRTYPE102Sv






    THANK YOU!! This saved my butt today, I’m immensely grateful.

    morgan

    ReplyDelete