Solved

Decode fields with dates

Posted on 2004-08-30
13
401 Views
Last Modified: 2013-12-01

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
0
Comment
Question by:mac170570
  • 7
  • 6
13 Comments
 
LVL 1

Expert Comment

by:dmcdconsult
Comment Utility
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)
0
 
LVL 1

Expert Comment

by:dmcdconsult
Comment Utility
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
0
 

Author Comment

by:mac170570
Comment Utility
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
0
 
LVL 1

Accepted Solution

by:
dmcdconsult earned 500 total points
Comment Utility
Sorry it currently does put 'vacant' if there is any value in JO.Date_To field.

Consider creating the following function (it returns YES or NO)
CREATE OR REPLACE FUNCTION xxxl_test_sysdate (p_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 (p_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 change the Decode to the following:

                   (DECODE(JO.DATE_FROM,
                         NULL, 'VACANT',
                         (DECODE(JO.DATE_TO,
                                 NULL, 'OCCUPIED',
                                 DECODE (xxxl_test_sysdate(JO.DATE_TO),
                                                         'YES', 'OCCUPIED',
                                         'Vacant'))))) JOB_STATUS

Hope that helps.
0
 

Author Comment

by:mac170570
Comment Utility
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
0
 

Author Comment

by:mac170570
Comment Utility
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Expert Comment

by:dmcdconsult
Comment Utility
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...
0
 

Author Comment

by:mac170570
Comment Utility


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

0
 
LVL 1

Expert Comment

by:dmcdconsult
Comment Utility
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'
0
 

Author Comment

by:mac170570
Comment Utility
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
0
 
LVL 1

Expert Comment

by:dmcdconsult
Comment Utility
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
0
 

Author Comment

by:mac170570
Comment Utility
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
0
 
LVL 1

Expert Comment

by:dmcdconsult
Comment Utility
Glad I could help.  Best of luck.

Dave
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

-Xmx and -Xms are the two JVM options often used to tune JVM heap size.   Here are some common mistakes made when using them:   Assume BigApp is a java class file for the below examples. 1.         Missing m, M, g or G at the end …
Verbose logging is used to diagnose garbage collector problems. By default, -verbose:gc output is written to either native_stderr.log or native_stdout.log.   It is also possible to redirect the logs to a user-specified file. This article will de…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now