asked on

Decode fields with dates

hi All,

Sorry if this is two much of a newbie question........yes i am only started with oracle and sql.

If its in the wrong area then please let me know and I am sorry for the mixup

I have created the following script with the intent to retrieve informaton relating to date periods.

As you can see from the script below after decode;

Basically if JO.DATE_FROM does not have a value (DATE format or null) then check the JO.DATE_TO field and if its null as well place in Vacant to the JOB_STATUS field otherwise check the JO.DATE_TO field, now its a little difficult..........

If the JO.DATE_FROM does have a date ( eg; 16/05/2003 )and JO.DATE_TO field is null then add occupied to the JOB_STATUS field, however if the JO.DATE_TO is before sysdate (eg; last month) then add vacant to the JOB_STATUS field.

As you can see J.JOB_ID is the primary key........so one key may have two entries eg; the first one is from 01/01/2004 to 31/05/2004 and the second with the same key may be as follows 01/06/2004 to 31/07/2004 (both JOB_STATUS field's should show vacant for the 2 entries). Would it be possible to filter the first one out and keep the second entry or in this case the latest one?........The J.JOB_ID field is a number

The tables are stored on an 9i server.

Select J.JOB_ID, J.CAT_CD, J.DATE_FROM JOB_DATE_FROM, J.ESTABL_PLAN_ONLY_FLAG, J.FUNC_ORG_CHART_FLAG, J.HEAD_OF_ORG_UNIT_FLAG, J.MGMT_JOB_FLAG, J.ORG_CHART_FLAG, J.SENS_DATE_FROM, J.SENS_JOB_FLAG,
U.ACR_COMPL, JO.DATE_FROM OCCP_DATE_FROM, JO.DATE_TO OCCP_DATE_TO,
decode(JO.DATE_FROM, null, 'VACANT' , 'OCCUPIED') as JOB_STATUS,
P.LOGIN
From SP2_JOBS J,
SP2_JOB_OCCUPATION_STATUSES JO,
SP2_ORG_UNIT_VERSIONS U,
SP2_PERSONS P
Where J.DATE_TO is null
AND J.JOB_ID = JO.JOB_ID (+)
AND JO.PER_ID = P.PER_ID (+)
AND J.OU_ID = U.OU_ID

AND U.ACR_COMPL LIKE 'RTD%'
AND (U.DATE_TO is null OR U.DATE_TO >= sysdate)
AND (JO.DATE_FROM is null or JO.DATE_FROM <= sysdate)
AND J.DATE_FROM is not null
Order by U.ACR_COMPL

Thanks very much in advance and I hope that someone may be able to help.

Mac

dmcdconsult

If I understand the logic behind the data correctly, you cannot have a JO.DATE_TO entry if there isn't a JO.DATE_FROM entry. Correct? (ie. you can't have an end date if there never was a start date)

IF so - try the following: in place of your decode within the select...
(DECODE(JO.DATE_FROM,
NULL, 'VACANT',
(DECODE(JO.DATE_TO,
NULL, 'OCCUPIED',
'VACANT')))) JOB_STATUS,

AND TO ELIMINATE the earlier records (those with an earlier FROM_DATE) add the following to the where clause:

WHERE JO.DATE_FROM = (SELECT MAX(JO2.DATE_FROM)
FROM XXXL_TEST_TABLE JO2)

dmcdconsult

Sorry forgot to mention - if you want the latest record (again going by the FROM_DATE) whether VACANT OR OCCUPIED, you will need to remove the ' J.DATE_TO is null ' portion of the where clause (replace it with the where clause from above).

Dave

mac170570

ASKER

Hi Dave,

I have inserted the first part of the code and it works to a point..........

I still need to check the present date against existing dates within the field JO.DATE_TO.....

The above clause puts Vacant for all dates that have a value...........

What would be good is to check today (date) against the date that is there and place vacant if its less (before)

I hope that you understand.

best regards.

mac

ASKER CERTIFIED SOLUTION

dmcdconsult

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mac170570

ASKER

Hi Dave,

Here is the new script that I have, however it stops at "FROM XXXL_TEST_TABLE JO2" as it says that the table does not exist.

Please remember that I am very new to this..............

I have the function done ........

It worked OK (well it gave me the double info for some records) when I had the ' J.DATE_TO is null ' in

Now it is saying that the table does not exist when I remove it and have the script below in....

i do hope that you can understand this and again thanks for all your help

Mac

Select J.JOB_ID, J.CAT_CD, J.DATE_FROM JOB_DATE_FROM, J.ESTABL_PLAN_ONLY_FLAG, J.FUNC_ORG_CHART_FLAG, J.HEAD_OF_ORG_UNIT_FLAG,
J.MGMT_JOB_FLAG, J.ORG_CHART_FLAG, J.SENS_DATE_FROM, J.SENS_JOB_FLAG, U.ACR_COMPL, JO.DATE_FROM OCCP_DATE_FROM,
       JO.DATE_TO OCCP_DATE_TO, (DECODE(JO.DATE_FROM,
NULL, 'VACANT',
(DECODE(JO.DATE_TO,
NULL, 'OCCUPIED',
'VACANT')))) JOB_STATUS,
       P.LOGIN, P.DN_USUAL_FAMILY_NAME_UPPER, P.DN_USUAL_GIVEN_NAME_UPPER
From SP2_JOBS J,
SP2_JOB_OCCUPATION_STATUSES JO,
       SP2_JOB_OCCUPATION_STATUSES JO2,
       SP2_ORG_UNIT_VERSIONS U,
       SP2_PERSONS P
Where /*AND J.DATE_TO is null*/
JO.DATE_FROM = (SELECT MAX(JO2.DATE_FROM)
FROM XXXL_TEST_TABLE JO2)
AND J.JOB_ID = JO.JOB_ID (+)
AND JO.PER_ID = P.PER_ID (+)
AND J.OU_ID = U.OU_ID
AND U.ACR_COMPL LIKE 'RTD%'
AND (U.DATE_TO is null OR U.DATE_TO >= sysdate)
AND (JO.DATE_FROM is null or JO.DATE_FROM <= sysdate)
AND J.DATE_FROM is not null
Order by U.ACR_COMPL

mac170570

ASKER

Just a comment,

When I remove J.DATE_TO is null from the statement it gives me two results for the one key id.
The dates are the same and this happens even when a new entry is included with a new JO.DATE_FROM......

How can I create the above table for it to test against it.........

Again thanks very much

Mac

dmcdconsult

Sorry replace xxxl_test_table with your table name: SP2_JOB_OCCUPATION_STATUSES
this should allow it to run. Let me know if you still receive duplicate records...

mac170570

ASKER

Hi Dave,

OK here is what i am using and in that order.............

First I run this script..........

CREATE OR REPLACE FUNCTION xxxl_test_sysdate (JO_DATE_TO DATE)
RETURN VARCHAR2
IS
tmpvar VARCHAR2(5);
/******************************************************************************
NAME: xxxl_test_sysdate
PURPOSE: Return YES if Sysdate is less than value in JO.DATE_TO field.

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 8/31/2004 1. Created this function.

NOTES:
******************************************************************************/
BEGIN
IF (JO_DATE_TO > SYSDATE)
THEN
tmpvar := 'YES';
ELSE
tmpvar := 'NO';
END IF;

RETURN tmpvar;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END xxxl_test_sysdate;
/

then I run this script on the same server ( It says that it creates the view..........)

CREATE OR REPLACE VIEW R_TEST_SYSPER2 (JOB_ID, CAT_CD, JOB_DATE_FROM, ESTABL_PLAN_ONLY_FLAG, FUNC_ORG_CHART_FLAG,
HEAD_OF_ORG_UNIT_FLAG, MGMT_JOB_FLAG, ORG_CHART_FLAG, SENS_DATE_FROM, SENS_JOB_FLAG, ACR_COMPL,
OCCP_DATE_FROM, OCCP_DATE_TO, JOB_STATUS, LOGIN, DN_USUAL_FAMILY_NAME_UPPER, DN_USUAL_GIVEN_NAME_UPPER) AS
       (
SELECT J.JOB_ID, J.CAT_CD, J.DATE_FROM JOB_DATE_FROM, J.ESTABL_PLAN_ONLY_FLAG, J.FUNC_ORG_CHART_FLAG, J.HEAD_OF_ORG_UNIT_FLAG,
J.MGMT_JOB_FLAG, J.ORG_CHART_FLAG, J.SENS_DATE_FROM, J.SENS_JOB_FLAG, U.ACR_COMPL, JO.DATE_FROM OCCP_DATE_FROM,
       JO.DATE_TO OCCP_DATE_TO, (DECODE(JO.DATE_FROM,
NULL, 'VACANT',
(DECODE(JO.DATE_TO,
NULL, 'OCCUPIED',
DECODE (xxxl_test_sysdate(JO.DATE_TO),
'YES', 'OCCUPIED',
'Vacant'))))) JOB_STATUS,
       P.LOGIN, P.DN_USUAL_FAMILY_NAME_UPPER, P.DN_USUAL_GIVEN_NAME_UPPER
FROM SP2_JOBS@sysper2.World J,
SP2_JOB_OCCUPATION_STATUSES@sysper2.World JO,
       SP2_JOB_OCCUPATION_STATUSES@sysper2.World JO2,
       SP2_ORG_UNIT_VERSIONS@sysper2.World U,
       SP2_PERSONS@sysper2.World P
Where
/*AND J.DATE_TO is null*/
JO.DATE_FROM = (SELECT MAX(JO2.DATE_FROM) FROM SP2_JOB_OCCUPATION_STATUSES@sysper2.World JO2)
AND J.JOB_ID = JO.JOB_ID (+)
AND JO.PER_ID = P.PER_ID (+)
AND J.OU_ID = U.OU_ID
AND U.ACR_COMPL LIKE 'RTD%'
AND (U.DATE_TO is null OR U.DATE_TO >= sysdate)
AND (JO.DATE_FROM is null or JO.DATE_FROM <= sysdate)
AND J.DATE_FROM is not null
);

Due to security a DB link was created for me from the main DWH to our local server.

When I check the view I have no information in the data...........and if I run the function script again it puts a red cross against the view name.......

I am using Toad version 7.6.0.11.........

I do thank you very much in advance and I am sure that its something that I am doing wrong.........

Mac

dmcdconsult

Is the view under the apps (application) schema? If so (and it does appear to be), and it draws its data from the application tables (which it appears to do) you must set the multi-org value. This allows TOAD or PLSQL to "See" the application views. Use the following:

Begin
dbms_application_info.set_client_info(xx);
end;

where xx is the Org ID for your set of books
(USE THE FOLLOWING SCRIPT TO GET A LIST OF POSSIBLE VALUES FOR xx)
SELECT ORGANIZATION_ID
FROM hr_ORGANIZATION_INFORMATION
WHERE UPPER(ORG_INFORMATION1) = 'OPERATING_UNIT'

mac170570

ASKER

Hi Dave,

Just in.

Thanks for the comment..........

It leaves me a little baffled......but I will try to explain.

Yes the view is under the schema............however this view is on a different server from the one that the orginal information is retrieved from.

A DBLINK was created to allow me to run scripts on the Data WH.

So when i run the script to create the view i am retrieving the information from one server and placing it on a different one.

I hope that you understand me.

What do you mean Org ID for my set of books?

Again thanks in advance.

If you like i can grant you the points if you would still like to help me.

Mac

dmcdconsult

If you run Oracle Applications and want info from the application tables you must set the Org_ID with in the client (Toad, SQL PLUS, etc) this (basically) tells the server you are looking for application data. (This ID is set automatically when you log into the applications.)

So for you would have to follow the following steps.

1. Open Toad.
2. Run the Following:
Select your ID:
(USE THE FOLLOWING SCRIPT TO GET A LIST OF POSSIBLE VALUES FOR xx)

SELECT ORGANIZATION_ID
FROM hr_ORGANIZATION_INFORMATION
WHERE UPPER(ORG_INFORMATION1) = 'OPERATING_UNIT'

3. Enter the result from above into xx - you must keep the parenthesis!

Begin
dbms_application_info.set_client_info(xx);
end;

4. Then try to run your view:

Select *
from R_TEST_SYSPER2

Hope this helps! You may also check with your 1. dba or 2. lead GL accounting functional person - the may be able to give you the appropriate value for the xx. You are looking for the set of Books Org_ID.

Dave

mac170570

ASKER

Hi Dave,

Thanks a million and your patience,

I created a view that placed all the info onto my local server and then run the second script that also used the function.

Again thanks.

Mac

dmcdconsult

Glad I could help. Best of luck.

Dave