This forum is currently locked. You can't register or post questions at this time. (read more)

Get Records From One Column, Display Based On Condition

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Sun Jan 22, 2012 6:50 am

Tim... wrote:Hi.

Try this:

Code: Select all

with
  date_query as (
    SELECT emp_number,
           day
    FROM   (SELECT distinct emp_number
            FROM   real_data
            WHERE  emp_number IN (1234, 2345)
            )
            CROSS JOIN
           (SELECT TO_DATE('12-JAN-2009', 'DD-MON-YYYY') + LEVEL - 1 AS day
            FROM   dual
            CONNECT BY LEVEL <= (TO_DATE('20-JAN-2009', 'DD-MON-YYYY') - TO_DATE('12-JAN-2009', 'DD-MON-YYYY') + 1))
    ORDER BY 1,2
  ),
  pivot_query as ( -- Pivot data data
    SELECT emp_number,
           time_in_out,
           TRUNC(time_in_out) AS day,
           DECODE(specifier, 'IN', time_in_out, NULL) AS TIME_IN,
           DECODE(specifier, 'OUT', time_in_out, NULL) AS TIME_OUT
      FROM real_data
    ORDER BY 1,2
  ),
  analytic_query as ( -- Analytic Query
    SELECT emp_number,
           time_in_out,
           day,
           time_in,
           time_out,
           LAG(time_in, 1, NULL) OVER (PARTITION BY emp_number ORDER BY time_in_out) AS time_in_prev,
           LEAD(time_out, 1, NULL) OVER (PARTITION BY emp_number ORDER BY time_in_out) AS time_out_next
    FROM   pivot_query
    ORDER by 1,2
  ),
  final_query as (
    SELECT aq.emp_number,
           aq.time_in_out,
           aq.day,
           aq.time_in,
           (CASE
              WHEN TRUNC(aq.time_out_next) = TRUNC(aq.time_in) THEN aq.time_out_next
              WHEN aq.time_in IS NULL AND TRUNC(aq.time_in_prev) != TRUNC(aq.time_out) THEN aq.time_out
              ELSE NULL
            END) AS time_out
    FROM   analytic_query aq
    WHERE  (
            aq.time_in IS NOT NULL
            OR     
            (aq.time_in IS NULL and TRUNC(aq.time_in_prev) != TRUNC(aq.time_out))
           )
    ORDER BY 1,2
  )
SELECT dq.emp_number,
       dq.day,
       fq.time_in_out AS event_order,
       NVL(TO_CHAR(fq.time_in, 'DD-MON-YY HH.MI.SS AM'), 'NIL') AS time_in,
       NVL(TO_CHAR(fq.time_out, 'DD-MON-YY HH.MI.SS AM'), 'NIL') AS time_out
FROM   date_query dq
       LEFT OUTER JOIN final_query fq ON dq.emp_number = fq.emp_number AND dq.day = fq.day
ORDER BY 1,2,3;


I was missing the PARTITION BY clause in the analytic functions. I think some edge data was bleeding between customers.
Cheers

Tim...


Tim,

Is it possible to remove this part from the query?

Code: Select all

with
  date_query as

As I am planning to generalize this query and make a view for this,remove hard coded dates and employees.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Sun Jan 22, 2012 8:38 am

Hi.

Wow. This is a blast from the past... :)

A quick look at the query tells me that this section is being used to generate data for any missing rows. If you remove this, you will have gaps in the data. Of course you can remove it, if you are happy to have gaps.

As for making a view of the whole contents. If someone does a "select * from view" without any predicates it is going to do a lot of work because it is going to trawl through all the data. Be careful.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Sun Jan 22, 2012 11:18 am

Yes indeed, blast from the past!. :-)
Idea is to remove the with date_query part and rest and make it more general.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Sun Jan 22, 2012 11:36 am

Hi.

That's fine, but depending on the number of rows I'm guessing it will perform quite badly unless people have query it using some predicates to restrict the output.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Sun Feb 19, 2012 6:52 am

Hi Tim

Again blast from the past

Code: Select all

    with
      date_query as (
        SELECT emp_number,
               day
        FROM   (SELECT distinct emp_number
                FROM   real_data
                WHERE  emp_number IN (1234, 2345)
                )
                CROSS JOIN
               (SELECT TO_DATE('12-JAN-2009', 'DD-MON-YYYY') + LEVEL - 1 AS day
                FROM   dual
                CONNECT BY LEVEL <= (TO_DATE('20-JAN-2009', 'DD-MON-YYYY') - TO_DATE('12-JAN-2009', 'DD-MON-YYYY') + 1))
        ORDER BY 1,2
      ),
      pivot_query as ( -- Pivot data data
        SELECT emp_number,
               time_in_out,
               TRUNC(time_in_out) AS day,
               DECODE(specifier, 'IN', time_in_out, NULL) AS TIME_IN,
               DECODE(specifier, 'OUT', time_in_out, NULL) AS TIME_OUT
          FROM real_data
        ORDER BY 1,2
      ),
      analytic_query as ( -- Analytic Query
        SELECT emp_number,
               time_in_out,
               day,
               time_in,
               time_out,
               LAG(time_in, 1, NULL) OVER (PARTITION BY emp_number ORDER BY time_in_out) AS time_in_prev,
               LEAD(time_out, 1, NULL) OVER (PARTITION BY emp_number ORDER BY time_in_out) AS time_out_next
        FROM   pivot_query
        ORDER by 1,2
      ),
      final_query as (
        SELECT aq.emp_number,
               aq.time_in_out,
               aq.day,
               aq.time_in,
               (CASE
                  WHEN TRUNC(aq.time_out_next) = TRUNC(aq.time_in) THEN aq.time_out_next
                  WHEN aq.time_in IS NULL AND TRUNC(aq.time_in_prev) != TRUNC(aq.time_out) THEN aq.time_out
                  ELSE NULL
                END) AS time_out
        FROM   analytic_query aq
        WHERE  (
                aq.time_in IS NOT NULL
                OR     
                (aq.time_in IS NULL and TRUNC(aq.time_in_prev) != TRUNC(aq.time_out))
               )
        ORDER BY 1,2
      )
    SELECT dq.emp_number,
           dq.day,
           fq.time_in_out AS event_order,
           NVL(TO_CHAR(fq.time_in, 'DD-MON-YY HH.MI.SS AM'), 'NIL') AS time_in,
           NVL(TO_CHAR(fq.time_out, 'DD-MON-YY HH.MI.SS AM'), 'NIL') AS time_out
    FROM   date_query dq
           LEFT OUTER JOIN final_query fq ON dq.emp_number = fq.emp_number AND dq.day = fq.day
    ORDER BY 1,2,3;


In the above how can make thi spart be more dynamic

Code: Select all

 CONNECT BY LEVEL <= (TO_DATE('20-JAN-2009', 'DD-MON-YYYY') - TO_DATE('12-JAN-2009', 'DD-MON-YYYY') + 1))


I would like to pass sysdate instead of (TO_DATE('20-JAN-2009', 'DD-MON-YYYY').

When I tried it gives me incorrect results.

E.g.

Code: Select all

 (SELECT TO_DATE('01-FEB-2012', 'DD-MON-YYYY') + LEVEL - 1 AS day
                FROM   dual
                CONNECT BY LEVEL <= (TO_DATE(sysdate, 'DD-MON-YYYY') - TO_DATE('01-FEB-2012', 'DD-MON-YYYY') + 1))

gives me

Code: Select all

 
01/Feb/12

where as
the following fetches me dates till 19-FEB-2012

Code: Select all

 (SELECT TO_DATE('01-FEB-2012', 'DD-MON-YYYY') + LEVEL - 1 AS day
                FROM   dual
                CONNECT BY LEVEL <= (TO_DATE('19-FEB-2012', 'DD-MON-YYYY') - TO_DATE('01-FEB-2012', 'DD-MON-YYYY') + 1))

Thanks

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Sun Feb 19, 2012 7:10 am

Tim,
I resolved it it was rather silly.

Code: Select all

(TO_DATE(sysdate, 'DD/Mon/YY'))


Thanks anyway.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Sun Feb 19, 2012 9:01 am

Hi.

If you just want the day, you should use TRUNC(SYSDATE), not convert it to a string. when you convert it to a string, oracle has to implicitly convert it back to a date, which IPs why you needed to change the format mask. If someone alters the NLS_DATE_FORMAT your code will fail. With TRUNC it is safe.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Sun Feb 19, 2012 9:45 am

Thanks Tim for pointing out. I changed to (TRUNC(sysdate))

Regards

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Sun Feb 19, 2012 9:48 am

BTW for mobile access of your forum you could easily add a tapatalk plugin and your forum will be accessible from mobile devices
More information here
http://www.tapatalk.com/faq.php

If you enable tapatalk it will be a advanatge.

Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Sun Feb 19, 2012 10:33 am

Hi.

I will check it out. No promised though.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 5 guests

cron