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;
Hi Dayakar,
ReplyDeleteGrazie! 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
Hello There,
ReplyDeleteI’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.
Hi Bro,
ReplyDeleteWhat 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
Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDeleteI admit, I have not been on this web page in a long time... however it was another joy to see It is such an important topic and ignored by so many, even professionals. I thank you to help making people more aware of possible issues. Employee Attendance
ReplyDelete