OraFAQ Forum: SQL & PL/SQL » Query without outer join

Home » SQL & PL/SQL » SQL & PL/SQL » Query without outer join (10g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Query without outer join [message #569894]

Fri, 02 November 2012 17:36

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

Report message to a moderator

Re: Query without outer join [message #569895 is a reply to message #569894]

Fri, 02 November 2012 17:42

BlackSwan
Messages: 20108
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.

Report message to a moderator

Re: Query without outer join [message #569897 is a reply to message #569895]

Fri, 02 November 2012 18:04

msaleem
Messages: 37
Registered: November 2006
Location: Pakistan

Member

Sorry Dear

Here is test Data

Thanks

Attachment: test_result.JPG
(Size: 25.97KB, Downloaded 30 times)

Report message to a moderator

Re: Query without outer join [message #569899 is a reply to message #569897]

Fri, 02 November 2012 18:24

BlackSwan
Messages: 20108
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

Report message to a moderator

Re: Query without outer join [message #569900 is a reply to message #569899]

Fri, 02 November 2012 18:38

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 23 times)

Report message to a moderator

Re: Query without outer join [message #569901 is a reply to message #569900]

Fri, 02 November 2012 19:11

BlackSwan
Messages: 20108
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?

Report message to a moderator

Re: Query without outer join [message #569911 is a reply to message #569894]

Sat, 03 November 2012 01:06

Michel Cadot
Messages: 54224
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

See date generator

Regards
Michel

Report message to a moderator

Re: Query without outer join [message #569912 is a reply to message #569900]

Sat, 03 November 2012 01:07

Michel Cadot
Messages: 54224
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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 24 00:36:05 CDT 2013

Total time taken to generate the page: 0.19617 seconds