Home » SQL & PL/SQL » SQL & PL/SQL » Query without outer join (10g)
Query without outer join [message #569894] Fri, 02 November 2012 17:36 Go to next message
msaleem
Messages: 37
Registered: November 2006
Location: Pakistan
Member

Hi gurus,

I have an attendance table in which we have empno, time_in, time_out..etc.
I want to create a matrix report that should shows all dates of any specific required month in columns and present empno in rows and time_in in cells.

I did it with using a temporary table by filling all the required dates and outer join with attendance table, it works fine, but it takes so long, is there any other better fast way to do it?

Thanks in advance

Re: Query without outer join [message #569895 is a reply to message #569894] Fri, 02 November 2012 17:42 Go to previous messageGo to next message
BlackSwan
Messages: 21942
Registered: January 2009
Senior Member
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Query without outer join [message #569897 is a reply to message #569895] Fri, 02 November 2012 18:04 Go to previous messageGo to next message
msaleem
Messages: 37
Registered: November 2006
Location: Pakistan
Member

Sorry Dear

Here is test Data

Thanks
Re: Query without outer join [message #569899 is a reply to message #569897] Fri, 02 November 2012 18:24 Go to previous messageGo to next message
BlackSwan
Messages: 21942
Registered: January 2009
Senior Member
we need actual CREATE TABLE statements along with actual INSERT statements posted here as plain text;
not some useless picture of results from SELECT statement!

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
Re: Query without outer join [message #569900 is a reply to message #569899] Fri, 02 November 2012 18:38 Go to previous messageGo to next message
msaleem
Messages: 37
Registered: November 2006
Location: Pakistan
Member

Here is script for create table and insert statements....

Result jpg is already posted


  • Attachment: test.sql
    (Size: 3.35KB, Downloaded 41 times)
Re: Query without outer join [message #569901 is a reply to message #569900] Fri, 02 November 2012 19:11 Go to previous messageGo to next message
BlackSwan
Messages: 21942
Registered: January 2009
Senior Member
>Result jpg is already posted
yes, but did not include the actual SELECT that produced the results

>is there any other better fast way to do it?
How can we determine if any different SQL is better or worse than yours; when you decided to NOT share your SQL with us?
Re: Query without outer join [message #569911 is a reply to message #569894] Sat, 03 November 2012 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See date generator

Regards
Michel
Re: Query without outer join [message #569912 is a reply to message #569900] Sat, 03 November 2012 01:07 Go to previous message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that '02-jan-12 19:27' is a STRING not a DATE:
SQL> select to_date('02-jan-12 19:27') from dual;
select to_date('02-jan-12 19:27') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel
Previous Topic: sql developer seprate date and time
Next Topic: How to restrict the user(Schema) from deleting the data from a table
Goto Forum:
  


Current Time: Fri Apr 18 16:08:54 CDT 2014

Total time taken to generate the page: 0.17791 seconds