Home » SQL & PL/SQL » SQL & PL/SQL » Need To Be Displayed NULL COLUMNS (merged 3) (Oracle9i,Forms&reports6i,xpsp2)
Need To Be Displayed NULL COLUMNS (merged 3) [message #432995] Sat, 28 November 2009 10:08 Go to next message
venkatesh.M.P
Messages: 37
Registered: February 2009
Location: india
Member
I am unable to get the data of the employees who are not present for a day in a Month.If there is In or Out Swip its showing for the calender days "I NEED TO GET COMPLETE 30 DAYS WITH OR WITHOUT DATA"

Thanks in ADVANCE
Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #432998 is a reply to message #432995] Sat, 28 November 2009 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to outer join with a calendar (search for this word).

Regards
Michel

[Updated on: Sat, 28 November 2009 10:17]

Report message to a moderator

Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433000 is a reply to message #432998] Sat, 28 November 2009 10:30 Go to previous messageGo to next message
venkatesh.M.P
Messages: 37
Registered: February 2009
Location: india
Member
sorry i didnot understand can u pls explain
Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433001 is a reply to message #433000] Sat, 28 November 2009 10:35 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

use the SEARCH function on this forum & search using keyword "calendar"
Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433035 is a reply to message #432995] Sun, 29 November 2009 04:43 Go to previous messageGo to next message
venkatesh.M.P
Messages: 37
Registered: February 2009
Location: india
Member

Attendance Data From : 01/09/2009 To : 30/09/2009

--------------------------------------------------------------------------------

Shift date In Time Out Time Details

--------------------------------------------------------------------------------

G 01.09 0933 1426

HERE IN BETWEEN THIS HE HAS NOT ATTENDED WITHOUT ANY LEAVE LETTERS.HERE I WANT THE DATE
TO BE DISPLAYED CONTINUE...............


G 05.09 CL-F

MISSING 06.09 HERE LIKE THAT I NEED TO GET UPTO THE TODATE (30/09/2009)


G 07.09 0920 1807



Here my problem is I want the dates to be printed upto the todate which we pass
in the run time parameters for a particular date there maybe no data but I need
to get that date to be displayed with null columns.


  • Attachment: checkout.txt
    (Size: 0.91KB, Downloaded 132 times)
Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433037 is a reply to message #433035] Sun, 29 November 2009 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Answer already gave.
What did you try for what we gave you?
What did you find searching for "calendar"?
How does this not fit your requirements?

And DO NOT post in UPPER case.
Follow BlackSwan's advice.

Regards
Michel
Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433071 is a reply to message #432995] Sun, 29 November 2009 13:38 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
We do not do IM speak here. u? That person is not here right now, call back later. Do you have Chef Boyardee in a can? You better let him out before he suffocates.

Now to your problem.

Do you know what outer join is?

Do you know what a calendar table is? There are many variations on this theme from simple, all the way up to data warehouse TIME dimension. Here is a very simple example.

SQL> create table calendar (thedate date,year number,month number,day number);

Table created.

SQL> insert into calendar values (to_date('01-jan-2009','dd-mon-rrrr'),2009,1,1);

1 row created.

SQL> insert into calendar values (to_date('02-jan-2009','dd-mon-rrrr'),2009,1,2);

1 row created.

SQL> -- ...
SQL> 
SQL> insert into calendar values (to_date('31-jan-2009','dd-mon-rrrr'),2009,1,31);

1 row created.


So now join your data to the calendar table using outer join.

Good luck, Kevin
Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433114 is a reply to message #432995] Mon, 30 November 2009 03:43 Go to previous messageGo to next message
venkatesh.M.P
Messages: 37
Registered: February 2009
Location: india
Member
Thanks for your responce and other thing is its should not be manullay.When i run the report it should pic up the date from the system calendar and should print all the month dates with or without data
Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433115 is a reply to message #432995] Mon, 30 November 2009 03:45 Go to previous messageGo to next message
venkatesh.M.P
Messages: 37
Registered: February 2009
Location: india
Member
its a employee attendance report
Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433117 is a reply to message #433114] Mon, 30 November 2009 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
venkatesh.M.P wrote on Mon, 30 November 2009 10:43
Thanks for your responce and other thing is its should not be manullay.When i run the report it should pic up the date from the system calendar and should print all the month dates with or without data

Michel Cadot wrote on Sun, 29 November 2009 11:57
Answer already gave.
What did you try for what we gave you?
What did you find searching for "calendar"?
How does this not fit your requirements?

Or are you just waiting we provide you the final solution?

Regards
Michel

[Updated on: Mon, 30 November 2009 03:56]

Report message to a moderator

Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433146 is a reply to message #432995] Mon, 30 November 2009 06:20 Go to previous messageGo to next message
venkatesh.M.P
Messages: 37
Registered: February 2009
Location: india
Member
sir its a report sir i need the dates for the month to be diplayed with or without data (i.e., if i run the report from 01.09.09 to 30.09.09 it should show the dates in between them with or without data)
Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433149 is a reply to message #433146] Mon, 30 November 2009 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All I can tell you is to read again the posts and do what we said.

Regards
Michel
Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433157 is a reply to message #433146] Mon, 30 November 2009 06:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To state it again, in smaller words.

1) You need a data source that will provide you with a record for each day of the month. @Kevin's Calendar table will do, but there are other, more flexible approaches

2) You take your query, and outer join it ,on a date column, to a query selecting every row from the Calendar, so that you still get a row from the calendar even if your query doesn't return a row for a specific date you still get a row back from the calendar.
Previous Topic: how to exclude SATURDAY AND SUNDAY in a string of DATES
Next Topic: procedure/function to search the field
Goto Forum:
  


Current Time: Mon Sep 26 02:32:09 CDT 2016

Total time taken to generate the page: 0.12284 seconds