Home » SQL & PL/SQL » SQL & PL/SQL » Query View
Query View [message #232118] Thu, 19 April 2007 09:23 Go to next message
ekofi
Messages: 8
Registered: February 2007
Junior Member
What's is the best way to build a HISTORICAL Query View?

Thanks.
Re: Query View [message #232123 is a reply to message #232118] Thu, 19 April 2007 09:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you mean by the phrase HISTORICAL Query view?

Re: Query View [message #232125 is a reply to message #232123] Thu, 19 April 2007 09:43 Go to previous messageGo to next message
ekofi
Messages: 8
Registered: February 2007
Junior Member
A Query view that has data effective dated from 01/01/1900 to current date.
Re: Query View [message #232139 is a reply to message #232125] Thu, 19 April 2007 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is an effective date for data?

Regards
Michel
Re: Query View [message #232141 is a reply to message #232139] Thu, 19 April 2007 11:01 Go to previous messageGo to next message
ekofi
Messages: 8
Registered: February 2007
Junior Member
Effective date for Actions=HIR,REH etc.
Re: Query View [message #232146 is a reply to message #232141] Thu, 19 April 2007 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still as clear as mud.
What about posting an exemple of what you have and what you want?
And mentioning your Oracle version?

Regards
Michel

[edit: add missing word]

[Updated on: Thu, 19 April 2007 11:15]

Report message to a moderator

Re: Query View [message #232148 is a reply to message #232118] Thu, 19 April 2007 11:24 Go to previous messageGo to next message
ekofi
Messages: 8
Registered: February 2007
Junior Member
Here is the SQL for the view:
SELECT A.EMPLID, A.EMPL_RCD, TO_CHAR(A.SN_JOB_EFFDT,'YYYY-MM-DD'), A.SN_JOB_EFFSEQ, A.COMPANY, A.DEPTID, A.LOCATION, A.GRADE, TO_CHAR(A.DEPT_ENTRY_DT,'YYYY-MM-DD'), TO_CHAR(A.GRADE_ENTRY_DT,'YYYY-MM-DD'), TO_CHAR(A.JOB_ENTRY_DT,'YYYY-MM-DD'), TO_CHAR(A.POSITION_ENTRY_DT,'YYYY-MM-DD'), A.JOBCODE, TO_CHAR(A.HIRE_DT,'YYYY-MM-DD'), A.EEO_CLASS, TO_CHAR(A.TERMINATION_DT,'YYYY-MM-DD'), A.STD_HOURS, A.SETID_DEPT, A.SETID_JOBCODE, A.SETID_LOCATION, A.SETID_SALARY, A.BUSINESS_UNIT, A.SUPERVISOR_ID, A.EMPL_STATUS, A.ACTION, TO_CHAR(A.ACTION_DATE,'YYYY-MM-DD'), A.ACTION_REASON, A.EMPL_TYPE, A.FLSA_STATUS, A.OFFICER_CD, A.EMPL_CLASS, A.SAL_ADMIN_PLAN, A.COMPRATE, TO_CHAR(A.SN_PERS_EFFDT,'YYYY-MM-DD'), A.MAR_STATUS, A.SEX, TO_CHAR(A.LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), A.LASTUPDOPRID, A.HIGHEST_EDUC_LVL, A.LAST_NAME, A.FIRST_NAME, A.NAME, A.ADDRESS1, A.ADDRESS2, A.ADDRESS3, A.ADDRESS4, A.CITY, A.COUNTY, A.STATE, A.POSTAL, A.COUNTRY, A.SN_INTRNL_ADDR, A.SN_YRS_WRK_EXP, A.SN_PLANT, A.SN_CLASS_NAME, A.SN_BADGE_ID, A.NATIONAL_ID, A.NATIONAL_ID_TYPE, A.ETHNIC_GRP_CD
FROM PS_SN_EMPLOYEE_VW1 A
Re: Query View [message #232155 is a reply to message #232118] Thu, 19 April 2007 11:58 Go to previous messageGo to next message
ekofi
Messages: 8
Registered: February 2007
Junior Member
Table Name: SN_EMPLOYEES_VW

Source Record Source Field Selection Criteria View Field New Field
JOB EmplID EmplID
JOB Empl_Rcd Empl_Rcd
JOB EffDt SN_Job_EffDt Y
JOB EffSeq SN_Job_EffSeq Y
PERSONAL_DATA Name Name
PERSONAL_DATA Last_Name Last_Name
PERSONAL_DATA First_Name First_Name
JOB Empl_Status Empl_Status
JOB Action Action
JOB Action_Dt Action_Dt
JOB Action_Reason Action_Reason
JOB DeptID DeptID
JOB Dept_Entry_Dt Dept_Entry_Dt
JOB Location Location
JOB Grade Grade
JOB Grade_Entry_Dt Grade_Entry_Dt
JOB Hire_Dt Hire_Dt
JOB EEO_Class EEO_Class
JOB Termination_Dt Termination_Dt
JOB STD_Hours STD_Hours
JOB Business_Unit Business_Unit
JOB Supervisor_ID Supervisor_ID
JOB Empl_Type Empl_Type
JOB FLSA_Status FLSA_Status
JOB Officer_Cd Officer_Cd
JOB Empl_Class Empl_Class
PERSONAL_DATA Country Country
PERSONAL_DATA Sex Sex
PERSONAL_DATA Mar_Status Mar_Status
DIVERS_ETHNIC Ethnic_Grp_Cd primary_indicator = Y Ethnic_Grp_Cd
JOB Sal_Admin_Plan Sal_Admin_Plan
JOB SetID_Dept SetID_Dept
JOB SetID_JobCode SetID_JobCode
JOB SetID_Location SetID_Location
Re: Query View [message #232156 is a reply to message #232118] Thu, 19 April 2007 11:59 Go to previous messageGo to next message
ekofi
Messages: 8
Registered: February 2007
Junior Member
Table Name: SN_EMPLOYEE_VW1

Source Record Source Field Selection Criteria View Field New Field
JOB EmplID EmplID
JOB Empl_Rcd Empl_Rcd
JOB EffDt SN_Job_EffDt Y
JOB EffSeq SN_Job_EffSeq Y
NAMES Name Name
NAMES Last_Name Name_Type = PRI, EffDt <= job EffDt Last_Name
NAMES First_Name First_Name
JOB Empl_Status Empl_Status
JOB Action Action
JOB Action_Date Action_Date
JOB Action_Reason Action_Reason
JOB Empl_Type Empl_Type
JOB FLSA_Status FLSA_Status
JOB Grade Grade
JOB Officer_Cd Officer_Cd Y
JOB Empl_Class Empl_Class
JOB Sal_Admin_Plan Sal_Admin_Plan
JOB CompRate CompRate
JOB DeptID DeptID
JOB Dept_Entry_Dt Dept_Entry_Dt
JOB Grade_Entry_Dt Grade_Entry_Dt
JOB Job_Entry_Dt Job_Entry_Dt
JOB Position_Entry_Dt Position_Entry_Dt
JOB JobCode JobCode
JOB Hire_Dt Hire_Dt
JOB EEO_Class EEO_Class
JOB Termination_Dt Termination_Dt
JOB Location Location
JOB Company Company
JOB Business_Unit Business_Unit
JOB Supervisor_ID Supervisor_ID
PERS_DATA_EFF EffDt EffDt <= job EffDt SN_Pers_EffDt
PERS_DATA_EFF Mar_Status Mar_Status
PERS_DATA_EFF Sex Sex
DIVERS_ETHNIC Ethnic_Grp_Cd primary_indicator = Y Ethnic_Grp_Cd
JOB STD_Hours STD_Hours
JOB SetID_Dept SetID_Dept
JOB SetID_JobCode SetID_JobCode
JOB SetID_Location SetID_Location
JOB SetID_Salary SetID_Salary
PERS_DATA_EFF LastUpdDtTm LastUpdDtTm
PERS_DATA_EFF LastUpdOprID LastUpdOprID
PERS_DATA_EFF Highest_Educ_Lvl Highest_Educ_Lvl
ADDRESSES Address1 address_type = HOME, EffDt <= job EffDt Address1
ADDRESSES Address2 Address2
ADDRESSES Address3 Address3
ADDRESSES Address4 Address4
ADDRESSES City City
ADDRESSES County County
ADDRESSES State State
ADDRESSES Postal Postal
ADDRESSES Country Country
SN_EMPLOYMENT SN_Intrnl_Addr SN_Intrnl_Addr
SN_EMPLOYMENT SN_Yrs_Wrk_Exp SN_Yrs_Wrk_Exp
SN_EMPLOYMENT Sn_Plant_Number Sn_Plant_Number
SN_EMPLOYMENT SN_Class_Name SN_Class_Name
SN_EMPLOYMENT SN_Badge_ID SN_Badge_ID
PERS_NID National_ID National_ID
PERS_NID National_ID_Type National_ID_Type
Re: Query View [message #232160 is a reply to message #232156] Thu, 19 April 2007 12:22 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
where effective_date >= to_date('01-jan-1900','dd-mon-yyyy');
Re: Query View [message #232550 is a reply to message #232160] Sat, 21 April 2007 02:04 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And don't convert your dates to chars.
Makes it practically impossible to work with
Previous Topic: dbms_xmlgen
Next Topic: Basic Query (merged)
Goto Forum:
  


Current Time: Fri Dec 02 21:03:59 CST 2016

Total time taken to generate the page: 0.15192 seconds