Skip to Main Content
Forums

Java SE (Java Platform, Standard Edition)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Date Picker in JTable.

701922Jul 29 2009 — edited Mar 9 2010
Hi !

I have JTable like below. In the column 1 and 2 user would input a date.

Now, I want to have date picker ie [ Calenlandar pop up ] to enable the user to pick the date.

Any help ?

By the way I tried http://www.toedter.com/en/jcalendar/index.html . I need help in integrating with JTable.

Thanks.
jTable1.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null},
                {null, null, null, null, null, null, null}
            },
            new String [] {
                "Type of Env", "Start Date", "End Date", "Duration in weeks", "No.of patches", "No.of fresh installation", "No.of upgrades"
            }
        ) {
            boolean[] canEdit = new boolean [] {
                false, false, false, false, false, false, false
            };

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        jScrollPane1.setViewportView(jTable1);

Comments

Frank Kulash

Hi,

Would you like to get answers that work?  Then make sure the CREATE TABLE and INSERT statements you post work, too.  Test (and, if necessary, fix) them before you post them. A lot of the INSERT statements you posted fail.

Also make sure the results you post are exactly what you want.  Do you want a row that has hr_status='This Row not required ...'?  If that was just a comment, don't include it in the result set; put comments before or after the results.  Do you really want "31-JUN" as a date?

EdStevens 

PS_CTS_JOB_DTL Table Build and Insert Scripts:
  1. CREATETABLEPS_CTS_JOB_DTL(EMPLIDVARCHAR2(11)NOTNULL,
  2. BUSINESS_UNITVARCHAR2(5)NOTNULL,
  3. EMPL_RCDSMALLINTNOTNULL,
  4. EFFDTDATE,
  5. EFFSEQSMALLINTNOTNULL,
  6. ACTIONVARCHAR2(3)NOTNULL);
  7. InsertintoPS_CTS_JOB_DTL(EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION,ACTION_DT)values('733930','US410',0,to_date('27-MAY-19','DD-MON-YY'),0,'ADD');

'Those who cannot remember the past are condemned to repeat it.' (George Santayana-1905)

Does the term "Y2K" mean anything to you?  Do you remember 1999 and 2000?

20 years ago I and armies of guys like me were busting our butts to insure that most of the computer programs in the world did not break shortly after 12:01 AM  on January 1, 2000.

TWENTY ******* YEARS AGO!

It's long, long, long past time to quit using 2-digit years and specify 4-digit years.  Every time.  EVERY TIME!

2 people found this helpful
Vemula Muni

Hi @"Frank Kulash", Create table and insert statements are updated.

Frank Kulash

Hi,

Vemula Muni wrote:
Hi Frank Kulash, Create table and insert statements are updated.

Okay; what about the desired results?  If you really want '31-JUN-19' in the output, then explain.

(I bet that's a mistake, but if you posted that mistake even after it was pointed out in reply #1, then there's a good chance the posted results have other mistakes.)

Employee is active as CWR and BU is in IQN_BU setup table
Vemula Muni

Hi @"Frank Kulash" , Sorry for the incorrect date. It should be 30-Jun-19. Edited the same.

Frank Kulash
Answer

Hi,

Since you're using Oracle 12, you can take advantage of the OUTER APPLY feature.  Here's one way:

WITH    parameters    AS
(
    SELECT  TO_DATE ('01-JUN-2019', 'DD-MON-YYYY') AS startdt  -- Always use 4 digits for years
    ,       TO_DATE ('30-JUN-2019', 'DD-MON-YYYY') AS enddt
    ,       'CWR'                                  AS per_org
    FROM    dual
)
,    got_business_unit   AS
(
    SELECT  p.startdt, p.enddt
    ,       d1.emplid, d1.per_org, d1.effdt
    ,       CASE
                WHEN  b.business_unit  IS NULL
                THEN  'I'
                ELSE  d1.hr_status
            END         AS hr_status
    ,       b.business_unit
    FROM    parameters       p
    JOIN    ps_cts_job_dtl1  d1   ON  d1.per_org      = p.per_org
                                  AND d1.effdt       <= p.enddt
    OUTER APPLY (
                    SELECT  MAX (b.business_unit)  AS business_unit
                    FROM    ps_cts_job_dtl  d
                    JOIN    ps_cts_iqn_bu   b   ON  b.business_unit = d.business_unit
                    WHERE   d.emplid        = d1.emplid
                    AND     d.effdt         = d1.effdt
                )            b
)
SELECT    g.emplid
,         GREATEST ( g.effdt
                   , g.startdt
                   )  AS startdt
,         LEAST ( e.enddt
                , g.enddt
                )     AS enddt
,         g.per_org
,         g.hr_status
,         'Employee is active as CWR and BU is in IQN_BU setup table'
                   AS reason
FROM      got_business_unit  g
OUTER APPLY (
                SELECT  NVL ( MIN (gs.effdt) - 1
                            , DATE '9999-12-31'
                            )  AS enddt
                FROM    got_business_unit  gs
                WHERE   gs.emplid    = g.emplid
                AND     gs.effdt     > g.effdt
                AND     gs.hr_status = 'I'
            )                e
WHERE     hr_status  = 'A'
AND       e.enddt    > g.startdt
ORDER BY  emplid
,         startdt
;

The output from your sample data is just what you requested:

EMPLID      STARTDT     ENDDT       PER_ORG HR_STATUS REASON
----------- ----------- ----------- ------- --------- ---------------------------------------------------------
733930      01-Jun-2019 03-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table
733930      15-Jun-2019 18-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table
733930      26-Jun-2019 30-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table
733931      06-Jun-2019 09-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table
733931      26-Jun-2019 30-Jun-2019 CWR     A         Employee is active as CWR and BU is in IQN_BU setup table

You want a row of output for each row in the table that has hr_status='A' (and is in the given date range, and has the given per_org).  However, if a row does not have any matching row in ps_cts_iqn_bu, then you want to count its hr_status as 'I'.
The sub-query called b above finds one of the matching business_units in ps_cts_iqn_bu, if there is one.  It's not important which one it finds, you just need to know if it found any.
The sub-query called e finds the 'I' row corresponding to each 'A' row, that is, the earliest 'I' row matching each 'A' row.  If there is no matching 'I' row, that means the emplid is still active, so act as if the inactive date is far in the future.

Marked as Answer by Vemula Muni · Sep 27 2020
Vemula Muni

Hi @"Frank Kulash", According to above data employee 733930 is active from 01-JUN-2019 to 03-JUN-19. after that i am inserted one more row with effdt '02-JUN-19' with HR status 'A'. ( in this case also query should fetch only one for 01-JUN-2019 to 03-JUN-19 ) but query is fetching from '01-Jun-19' to '03-Jun-19' one row, and '02-Jun-19' to '03-Jun-19'.

For employee 733931 i am inserted one more with effdt '28-JUN-19' and HR status is Active. in this case also query should fetch one row from 26-JUN-19 to 30-Jun-19, But query is fetching from 26-Jun-19 to 28-Jun-19.

New Insert Statements: ( newly added rows for existing data, same statements included in question as well )

Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('02-JUN-19','DD-MON-YY'),0,'DTA');

Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-19','DD-MON-YY'),0,'DTA');

Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('02-JUN-19','DD-MON-YY'),0,'A','CWR');

Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',3,to_date('28-JUN-19','DD-MON-YY'),0,'A','CWR');

Out put from the System :

pastedImage_3.png

Expected Output :

| EMPLID | FROM_DATE | To_DATE | PER_ORG | HR_STATUS | Reason |
| 733930 | 1-Jun-19 | 3-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733930 | 15-Jun-19 | 18-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733930 | 26-Jun-19 | 30-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733931 | 6-Jun-19 | 9-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733931 | 26-Jun-19 | 30-JUN-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |

Frank Kulash

Hi,

Vemula Muni wrote:

Hi [Frank Kulash](/people/Frank Kulash?customTheme=otn), According to above data employee 733930 is active from 01-JUN-2019 to 03-JUN-19. after that i am inserted one more row with effdt '02-JUN-19' with HR status 'A'. ( in this case also query should fetch only one for 01-JUN-2019 to 03-JUN-19 ) but query is fetching from '01-Jun-19' to '03-Jun-19' one row, and '02-Jun-19' to '03-Jun-19'.

For employee 733931 i am inserted one more with effdt '28-JUN-19' and HR status is Active. in this case also query should fetch one row from 26-JUN-19 to 30-Jun-19, But query is fetching from 26-Jun-19 to 28-Jun-19.

New Insert Statements: ( newly added rows for existing data, same statements included in question as well )

Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('02-JUN-19','DD-MON-YY'),0,'DTA');

Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-19','DD-MON-YY'),0,'DTA');

Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('02-JUN-19','DD-MON-YY'),0,'A','CWR');

Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',3,to_date('28-JUN-19','DD-MON-YY'),0,'A','CWR');

Out put from the System :

pastedImage_3.png

Expected Output :

| EMPLID | FROM_DATE | To_DATE | PER_ORG | HR_STATUS | Reason |
| 733930 | 1-Jun-19 | 3-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733930 | 15-Jun-19 | 18-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733930 | 26-Jun-19 | 30-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733931 | 6-Jun-19 | 9-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733931 | 26-Jun-19 | 30-JUN-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |

How was anyone supposed to know this from reading your original message? Don't wat until reply #7 to give your requirements: say exactly what you want from the beginning.

I made a guess earlier. I guessed wrong, and wasted lot of time. Don't make people guess. Describe exactly what you want (including what each row in the result set represents) and how you get the desired results from your sample data. Include examples of any special cases (such multiple consecutive rows with hr_status='A') in your sample data, results and explanation.

Vemula Muni

Hi @"Frank Kulash", Sorry for giving trouble. there is a chance to have an employee multiple consecutive rows with hr_status='A' when his Data ,location ,assignment and pay scale changed.

Vemula Muni

Hi @"Frank Kulash" , Please take me out from this issue. Due to this issue my development got struck.

Frank Kulash

Hi,

Vemula Muni wrote:
Hi Frank Kulash , Please take me out from this issue. Due to this issue my development got struck.

Sorry, I don't understand what you're saying.

Are you saying you no longer need to solve this problem?  If so, mark the thread as "Answered".

Are you saying you still want help?  Then post a complete explanation, as requested in reply #8.

Vemula Muni

Hi @"Frank Kulash", Below is the requirement details.

  1. I want to fetch the contract employee information with HR_Status = 'A'. ( per_org = 'CWR' in PS_CTS_JOB_DTL1 table ).
  2. And active contract employee Business_Unit value of 'PS_CTS_JOB_DTL' table should be present in PS_CTS_IQN_BU table.
  3. there is a chance to have an employee multiple consecutive rows with hr_status='A'

DBA Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Below is my "PS_CTS_JOB_DTL" table structure and data. in this table (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) are key fields.

EMPL_RCD : when ever employee converted from contract to full time and from full time to contract this value will increase. (0,1,2,...)

EFFDT : this filed use to track when change happens.

EFFSEQ : this field will increase from 0 to 1,2 .. when there is more than one transaction in same date.( with same effdt )

ACTION : Will determine which action they taken on employee ( please don't consider action field to achieve output as below. because action codes may different in each time)

| JOB Table |
| EMPLID | BUSINESS_UNIT | EMPL_RCD | EFFDT | EFFSEQ | ACTION |
| 733930 | US410 | 0 | 27-May-19 | 0 | ADD |
| 733930 | US410 | 0 | 02-Jun-19 | 0 | DTA |
| 733930 | US410 | 0 | 4-Jun-19 | 0 | TER |
| 733930 | US410 | 1 | 6-Jun-19 | 0 | HIR |
| 733930 | US410 | 1 | 15-Jun-19 | 0 | TER |
| 733930 | US410 | 2 | 15-Jun-19 | 1 | ADD |
| 733930 | US608 | 2 | 19-Jun-19 | 0 | ASC |
| 733930 | CN512 | 2 | 26-Jun-19 | 0 | ASG |
| 733931 | US410 | 0 | 26-Apr-19 | 0 | HIR |
| 733931 | US410 | 0 | 5-Jun-19 | 0 | TER |
| 733931 | IN208 | 1 | 6-Jun-19 | 0 | ADD |
| 733931 | IN208 | 1 | 10-Jun-19 | 0 | TER |
| 733931 | IN208 | 2 | 12-Jun-19 | 0 | HIR |
| 733931 | IN408 | 2 | 18-Jun-19 | 0 | ASC |
| 733931 | IN408 | 2 | 19-Jun-19 | 0 | TER |
| 733931 | IN208 | 3 | 26-Jun-19 | 0 | ADD |
| 733931 | IN208 | 3 | 28-Jun-19 | 0 | DTA |

Below is my "PS_CTS_JOB_DTL1" table structure and data. in this table (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) are key fields.

EMPL_RCD : when ever employee converted from contract to full time and from full time to contract this value will increase. (0,1,2,...)

EFFDT : this filed use to track when change happens.

EFFSEQ : this field will increase from 0 to 1,2 .. when there is more than one transaction in same date.( with same effdt )

HR_STATUS : To identify employee status ( A = Active, I = Inactive ).

PER_ORG : to identify employee type ( CWR = Contract, EMP = Employee )

Note : employee may contains more then one row with HR_STATUS = 'A' before he is going to inactive ( HR_STSTUS = 'I' )

| EMPL_ID | EMPL_RCD | EFFDT | EFFSEQ | HR_STATUS | PER_ORG |
| 733930 | 0 | 27-May-19 | 0 | A | CWR |
| 733930 | 0 | 02-Jun-19 | 0 | A | CWR |
| 733930 | 0 | 4-Jun-19 | 0 | I | CWR |
| 733930 | 1 | 6-Jun-19 | 0 | A | EMP |
| 733930 | 1 | 15-Jun-19 | 0 | I | EMP |
| 733930 | 2 | 15-Jun-19 | 1 | A | CWR |
| 733930 | 2 | 19-Jun-19 | 0 | A | CWR |
| 733930 | 2 | 26-Jun-19 | 0 | A | CWR |
| 733931 | 0 | 26-Apr-19 | 0 | A | EMP |
| 733931 | 0 | 5-Jun-19 | 0 | I | EMP |
| 733931 | 1 | 6-Jun-19 | 0 | A | CWR |
| 733931 | 1 | 10-Jun-19 | 0 | I | CWR |
| 733931 | 2 | 12-Jun-19 | 0 | A | EMP |
| 733931 | 2 | 18-Jun-19 | 0 | A | EMP |
| 733931 | 2 | 19-Jun-19 | 0 | I | EMP |
| 733931 | 3 | 26-Jun-19 | 0 | A | CWR |
| 733931 | 3 | 28-Jun-19 | 0 | A | DTA |

Below is my "PS_CTS_IQN_BU" table structure and data.

| IQN BU Setp |
| Country | Business_Unit |
| USA | US410 |
| USA | US412 |
| CAN | CN512 |
| IND | IN208 |

Based on above three tables i need to fetch below output. When i pass from date and to date in query (month start and end dates i.e like '01-JUN-19' and '30-JUN-19' ) , query should fetch employee active from active to dates as CWR and that employee Business unit should be in PS_CTS_IQN_BU table.

Required output:

Note : Reason column for understanding, no need to display reason column

While fetching below out put Please map EMPLID,EMPL_RCD,EFFDT,EFFSEQ Key fields in both JOB_DTL and JOB_DTL1 ( Map Key fields is very important )

  • Employee 733930 is active CWR in between '01-Jun-19' to '03-Jun-19' and employee have only one row with HR_STATUS ='A' ( employee have more than one row with HR_STATUS = 'A' ) and Business_unit value present in PS_CTS_IQN_BU table.
  • Employee 733930 is active CWR in between '15-Jun-19' to '18-Jun-19' and employee have only one row with HR_STATUS ='A' and Business_unit value present in PS_CTS_IQN_BU table.
  • Employee 733930 is active as CWR in between '19-JUN-19' to '25-JUN-19' in different BU which not in IQN_BU setup table, so i want to exclude that information.
  • Employee 733930 is active CWR from '26-Jun-19' with HR_STATUS ='A' and Business_unit value present in PS_CTS_IQN_BU table.
  • Employee 733931 is active CWR from '06-Jun-19' to '09-Jun-19' and, employee have only one row with HR_STATUS ='A' and Business_unit value present in PS_CTS_IQN_BU table.
  • Employee 733931 is active CWR from '26-Jun-19' to '30-Jun-19' and he is having more than one row with HR_STATUS = 'A' and Business_unit value present in PS_CTS_IQN_BU table.

| EMPLID | FROM_DATE | To_DATE | PER_ORG | HR_STATUS | Reason |
| 733930 | 1-Jun-19 | 3-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733930 | 15-Jun-19 | 18-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733930 | 26-Jun-19 | 30-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733931 | 6-Jun-19 | 9-Jun-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |
| 733931 | 26-Jun-19 | 30-JUN-19 | CWR | A | Employee is active as CWR and BU is in IQN_BU setup table |

The query which you shared in the #reply 6 fetching the below data. Requesting you to alter the query to fetch out put like above.

pastedImage_9.png

PS_CTS_JOB_DTL Table Build and Insert Scripts:

  1. CREATE TABLE PS_CTS_JOB_DTL (EMPLID VARCHAR2(11) NOT NULL,
  2. BUSINESS\_UNIT VARCHAR2(5) NOT NULL, 

  3. EMPL\_RCD INT NOT NULL, 

  4. EFFDT DATE, 

  5. EFFSEQ
1 - 12
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 6 2010
Added on Jul 29 2009
#swing
8 comments
3,023 views