Home » SQL & PL/SQL » Client Tools » Date Difference (Oracel PL/SQL 9i Microsoft 2K3)
Date Difference [message #432359] Mon, 23 November 2009 17:23 Go to next message
dnfrantum
Messages: 11
Registered: November 2009
Location: California
Junior Member
I have the following query, but I am getting an error on the sysdate minus the startdate. Hopefully, someone can share some wisdom.

set markup html on;
SET PAGESIZE 375
spool c:\DF_Marys_REPORT_VISUALIZE.html;
SELECT T3.R_Object_ID,T3.Object_Name,T3.Title,T3.R_Object_Type,
DECODE (T3.R_Status,2,'Superseded' ,3,'Retired',4,'Draft',5,'Review Copy',7,'Authorised',8,'Withdrawn') AS R_Status_Doc,
T3.R_version,T0.Object_Name,T0.R_Object_Type,DECODE(T0.R_Status,0,'Draft',1,'Workflow Approval',2,'Workflow Approved',
3,'Workflow Disapproved',4,'Cancelled',5,'Editing',6,'Release Approval',7,'Authorised Pending',8,'Release Disapproved',
9,'Awaiting Pre-Approval',10,'Change Review',11,'Pending Release',12,'Released',13,'Superceded',14,'Edit Complete',
15,'Review',16,'Review Complete',17,'Change Review Complete',18,'Undefined',19,'Workflow Approved',20,'Release Approved',
21,'Authorised') AS R_Status_WF,T0.R_TargetDate,T4.User_Name,T8.User_Name,DECODE(T7.R_Status,
0,'Not Applicable', 1,'Disapproved', 2,'Approved', 3,'Not Required', 4,'Pending', 5,'Withdrawn') AS R_STATUS_Panel,[b][color=red]T9.sysdate - TO_DATE(T7.StartDate,'yyyymmdd')[/color][/b] AgeDays
FROM QM_CHANGEREQUEST_BASE T0 INNER JOIN QM_Changerequest_Base_R T1 ON T0.R_Object_ID = T1.Link_From_ID
                              INNER JOIN QM_CRDocument T2 ON T1.R_Documents = T2.R_Object_ID
                              INNER JOIN QM_Document_BASE T3 ON T2.R_Docid = T3.R_Object_ID
                              INNER JOIN QM_User T4 ON T0.R_Originator = T4.R_Object_ID
                              INNER JOIN QM_Panel T5 ON T0.R_Object_ID = T5.R_OwningObjectID
                              INNER JOIN QM_Panel_R T6 ON T5.R_Object_ID = T6.Link_From_ID
                              INNER JOIN QM_PanelMember T7 ON T6.R_Members = T7.R_Object_ID
                              INNER JOIN QM_USER T8 ON T7.R_UserID = T8.R_Object_ID,DUAL T9
ORDER BY T3.R_Object_ID ASC;
spool off;
set markup html off;


Here is the error that I am getting:

= T7.R_Object_ID
<br>
 16                                INNER JOIN QM_USER T8 ON T7.R_UserID = T8.R_O
bject_ID,DUAL T9
<br>
 17  ORDER BY T3.R_Object_ID ASC;
<br>
<pre>
0,'Not Applicable', 1,'Disapproved', 2,'Approved', 3,'Not Required', 4,'Pending'
, 5,'Withdrawn') AS R_STATUS_Panel,T9.sysdate - TO_DATE(T7.StartDate,'yyyymmdd')
 AgeDays

                                      *
</pre>
ERROR at line 8:
<br>
ORA-01747: invalid user.table.column, table.column, or column specification
<br>


SQL&gt; spool off;
<br>
SQL&gt; set markup html off;
<br>


Thanks in advance,
Donald
Re: Date Difference [message #432361 is a reply to message #432359] Mon, 23 November 2009 17:33 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>Hopefully, someone can share some wisdom.

I acknowledge that you really tried to do the right thing;
but from my perspective it is somewhat lacking.

Simply put, I can't come close to reproducing the error.

You should not TO_DATE(DATE_DATATYPE); which it appears you do but I don't think this causes your error.

I think error involve T9 alias, but could be mistaken.

Please eliminate the HTML formating while debugging.
Re: Date Difference [message #432364 is a reply to message #432359] Mon, 23 November 2009 17:44 Go to previous messageGo to next message
dnfrantum
Messages: 11
Registered: November 2009
Location: California
Junior Member
Thanks...I was using the wrong column name, it should have been T7.R_StartDate, not T7.StartDate

Thanks again,
Donald
Re: Date Difference [message #432395 is a reply to message #432364] Mon, 23 November 2009 23:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If this was not a generated query, try to make it a habit to use useful aliases. Having consistent aliases for all tables and views throughout your application, in all queries helps a lot in fast-reading those queries.
Typically, these aliases consist of three letters and are also used in constraint names.
Re: Date Difference [message #432445 is a reply to message #432359] Tue, 24 November 2009 05:07 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
I would suggest this is wrong as well:
T9.sysdate 

Sysdate isn't a column so you can't alias it.
Re: Date Difference [message #432741 is a reply to message #432359] Thu, 26 November 2009 00:23 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
no need to use this
DUAL T9
Wink
You can directly use sysdate..Wink

sriram
Re: Date Difference [message #432783 is a reply to message #432741] Thu, 26 November 2009 04:47 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
ramoradba wrote on Thu, 26 November 2009 06:23
no need to use this
DUAL T9
Wink
You can directly use sysdate..Wink

sriram


Actually you have to use sysdate directly, you'll get an error otherwise.
SQL> select d.sysdate from dual d;
select d.sysdate from dual d
         *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification
Re: Date Difference [message #432785 is a reply to message #432783] Thu, 26 November 2009 05:02 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
I didn`t get you.. why you posted that as a reply to my reply ...And for your information my message is not for your message...Your reply pointed directly where exactly the error is...And thats is exact answer for the OP.Mine Just an addition to your answer. I pointed that he is using An alias for dual which is not required ...Again you posted the same to me....

Can you explain Why ?

This is what i meant in my post
16:44:56 SQL> select e.ename,d.sysdate
16:45:24   2  from emp e,dual d;
select e.ename,d.sysdate
                 *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification


16:45:35 SQL> select e.ename,sysdate
16:45:43   2  from emp e;

SMITH      26-NOV-09
ALLEN      26-NOV-09
WARD       26-NOV-09
JONES      26-NOV-09
MARTIN     26-NOV-09
BLAKE      26-NOV-09
CLARK      26-NOV-09
SCOTT      26-NOV-09
KING       26-NOV-09
TURNER     26-NOV-09
ADAMS      26-NOV-09
JAMES      26-NOV-09
FORD       26-NOV-09

MILLER     26-NOV-09

14 rows selected.

16:45:50 SQL>


Sriram

[Updated on: Thu, 26 November 2009 05:14]

Report message to a moderator

Re: Date Difference [message #432790 is a reply to message #432359] Thu, 26 November 2009 05:21 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
The way you worded it made it sound like it'd would work either way - I'm sure that wasn't your intention but I just wanted to be completely clear for everyone else reading this thread. Especially since I'm sure that was what the OPs original problem was despite the the fact he didn't mention it when he said he'd fixed the issue.
Re: Date Difference [message #433165 is a reply to message #432785] Mon, 30 November 2009 08:16 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
@Sriram I would have to agree with cookie, the wording of your post implied that it would work either way.
Re: Date Difference [message #433369 is a reply to message #433165] Tue, 01 December 2009 23:49 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@ pablolee,
I understood what cookiemonster said.Again after 4 days, you are saying the same thing ? Why ?
Re: Date Difference [message #433403 is a reply to message #433369] Wed, 02 December 2009 02:47 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
@Sriram no need to spit your dummy out the pram mate.
1. I Didn't notice the date stamp on the reply. Mia Culpa.
2. I didn't say the same thing, I simply agreed with him (there is a difference) because I felt that you were unneccessarliy aggressive in your post, so I felt that someone else agreeing with the original position might get you to relax a bit. Quite obviously you're not in the mood for that, so I will happily leave this converstaion alone.
Cheers
Re: Date Difference [message #433406 is a reply to message #433403] Wed, 02 December 2009 02:55 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
In this BIG " Oracle " World,I am a little child,So i can ask (raise) My doubts always...

Quote:
I felt that you were unneccessarliy aggressive
.

I never post my replies in that mood....
I never take my senior `s suggestions Or valuable feedback in an aggressive mood.

Thank you.

sriram Smile
Re: Date Difference [message #433411 is a reply to message #433406] Wed, 02 December 2009 03:36 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
No problem siriam. It does sometimes come across like you take some of these comments personally though. Might be something to watch out for in your 'tone'.
Have a good day.
Previous Topic: HOW to connect oracle with D2K(developer 2000)
Next Topic: Columns in a delimited file
Goto Forum:
  


Current Time: Tue Dec 06 12:29:24 CST 2016

Total time taken to generate the page: 0.08794 seconds