OraFAQ Forum: SQL & PL/SQL » Replace Date column to Varchar Value

Home » SQL & PL/SQL » SQL & PL/SQL » Replace Date column to Varchar Value (Windows)

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

Replace Date column to Varchar Value [message #610260]

Tue, 18 March 2014 21:19

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

hi,

I have a question wherein 1 table is getting data about the jobs ran like start time and end time .
Both start and end time columns datatype are date ,now what i need is suppose the Job is still running so i do not get an entry in start time and end time columns , so in-spite of NULL i want to replace it with 'Running' value.

But as the columns are Date Type i was unable to replace it with 'Running'

Can it be done Please help.

Thanks in Advance.

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610261 is a reply to message #610260]

Tue, 18 March 2014 21:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can it be done Please help.
No, DATE column can't hold any string like "Running"

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610262 is a reply to message #610260]

Tue, 18 March 2014 21:33

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

amit_jha05 wrote on Tue, 18 March 2014 21:19

hi,

I have a question wherein 1 table is getting data about the jobs ran like start time and end time .
Both start and end time columns datatype are date ,now what i need is suppose the Job is still running so i do not get an entry in start time and end time columns , so in-spite of NULL i want to replace it with 'Running' value.

But as the columns are Date Type i was unable to replace it with 'Running'

Can it be done Please help.

Thanks in Advance.

Example :-

(SELECT DISTINCT (TO_DATE
(TO_CHAR (MAX (x.job_start_ts),
'MM/DD/YYYY HH12:MI:SS AM'
),
'MM/DD/YYYY HH12:MI:SS AM'
)
)job_start_ts
FROM abc x
Here x.job_start_ts is Date type so if the process is running it wont have data hence i want to replace the same with 'Running'.

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610263 is a reply to message #610262]

Tue, 18 March 2014 21:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

use NVL()
BTW, posted SQL is invalid syntax

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610264 is a reply to message #610262]

Tue, 18 March 2014 21:53

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

Hi Black Swan ,
Thanks for the reply.

mistake is SQL is i added 1 extra braces before Select .

As you suggested i have change the datatype to Varchar2(30) as Date cannot hold String.

SELECT DISTINCT (TO_DATE
(TO_CHAR (MAX (job_end_ts),
'MM/DD/YYYY HH12:MI:SS AM'
),
'MM/DD/YYYY HH12:MI:SS AM'
)
)
FROM abc x

After Changing the datatype to Varchar i tried
SELECT DISTINCT (NVL(TO_DATE
(TO_CHAR (MAX (job_end_ts),
'MM/DD/YYYY HH12:MI:SS AM'
),
'MM/DD/YYYY HH12:MI:SS AM'
)
),'Running')
FROM abc x
It gives error as Invalid Number of arguments

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610265 is a reply to message #610264]

Tue, 18 March 2014 22:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

why TO_CHAR() any VARCHAR2 column?

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610272 is a reply to message #610265]

Wed, 19 March 2014 00:45

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

Sorry for the confusion ....
Column job_end_ts is Date type ,I am extracting the data of this column into another column (Availibility) that was also Date datatype now i have changed it to Var char as per advise ,here i want if Job_end_ts is NULL as the Job will be running so in the Availibility column it should be 'Running'.

Above the select query that i have posted is insert into select from

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610273 is a reply to message #610272]

Wed, 19 March 2014 01:03

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

Senior Member
Account Moderator

Quote:

another column (Availibility) that was also Date datatype now i have changed it to Var char as per advise

If this column contains a date this is a very bad advice.
If this is a new flag 'running/completed) you can set it using something like:

update mytable set Availibility = nvl2(end_date,'completed','running');

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610276 is a reply to message #610273]

Wed, 19 March 2014 01:08

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

Thanks man but can't it be done in select itself as
Insert into mytable
SELECT DISTINCT (NVL2(TO_DATE
(TO_CHAR (MAX (job_end_ts),
'MM/DD/YYYY HH12:MI:SS AM'
),
'MM/DD/YYYY HH12:MI:SS AM'
)
),'Running')
FROM abc x ;

Its giving Invalid number of argument to me

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610278 is a reply to message #610276]

Wed, 19 March 2014 01:27

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

Senior Member
Account Moderator

This is not I said.
Actually I don't even understand what you mean. Why this insert? Insert into what? From what?
Post the current table or tables with sample of data and what you want to achieve from them (it could be just an example of the tables and data that shows what you want to do not the actual ones).

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610281 is a reply to message #610278]

Wed, 19 March 2014 01:36

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

INSERT xyz
VALUES ('EE', 'aa', 'DAILY', '5:30 AM CEST', '11:30 PM EST',
'200000', TRUNC (SYSDATE),
(SELECT DISTINCT (TO_DATE
(TO_CHAR (MAX (x.job_start_ts),
'MM/DD/YYYY HH12:MI:SS AM'
),
'MM/DD/YYYY HH12:MI:SS AM'
)
)job_start_ts
FROM abc,
(SELECT DISTINCT (NVL2(TO_DATE
(TO_CHAR (MAX (y.job_end_ts),
MM/DD/YYYY HH12:MI:SS AM'
),
'MM/DD/YYYY HH12:MI:SS AM'
)
),'Running')job_end_time
FROM abc);
In the 1st select we have the job start time whereas 2nd select it is end time so if the job is running select will not get any data for end_time here i wanted to do NVL so that it gives Running as tried above but query failed with Invalid number of Argument error.
hope this makes thing a bit clearer to you.

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610282 is a reply to message #610281]

Wed, 19 March 2014 01:39

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

Senior Member
Account Moderator

Still don't understand.
As you refuse to post what I requested, I give up.

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610287 is a reply to message #610282]

Wed, 19 March 2014 02:36

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

Let me try to explain

in the above Insert script xyz table has some data regarding various jobs start and end time

Now we have made another table abc wherein we extract data from xyz table based on some condition.

Requirement is data from table abc should go to business user in mail so that they know about the day's loading process.

Now if a job is running the select query bring NULL for end_time second select in the insert script where we want Running to be extracted in abc table instead of NULL so that the users know that the job is in running state.

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610288 is a reply to message #610287]

Wed, 19 March 2014 02:45

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

Senior Member
Account Moderator

This is still not what I asked.

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610290 is a reply to message #610287]

Wed, 19 March 2014 02:49

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

orclz>
orclz> create table t1(c1 date);

Table created.

orclz> insert into t1 values(sysdate);

1 row created.

orclz> insert into t1 values(null);

1 row created.

orclz> select nvl(to_char(c1),'running') from t1;

NVL(TO_CHAR(C1),'RU
-------------------
2014-03-19 07:48:26
running

orclz>

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610291 is a reply to message #610290]

Wed, 19 March 2014 02:50

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

Thanks John I will try this now and will let u know the result

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610323 is a reply to message #610287]

Wed, 19 March 2014 07:28

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

No, you don't need to INSERT the character string 'running' into the table itself. Leave dates as DATE. By definition, in this app any date that is null is defined as 'running'. So just report so when you query it, as others have tried to show with the NVL function.

Whoever advised you to try to store a date in a varchar simply doesn't know what they are talking about. I don't care how 'senior' you (or they) think they are. Then the idea of trying to insert a very transient string literal into that 'varchar-date' column simply compounds they insanity.

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610375 is a reply to message #610323]

Wed, 19 March 2014 11:06

amit_jha05
Messages: 35
Registered: January 2014
Location: India

Member

Hi Ed,
Thanks for the suggestion but the queries above is the ones that i tried but its not working for me either.
I too want to just report the Date as Running instead of changing anything.

If possible for you can you suggest the mistake in my query of some query of yours that might work.

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610385 is a reply to message #610375]

Wed, 19 March 2014 12:28

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

amit_jha05 wrote on Wed, 19 March 2014 11:06

Hi Ed,
Thanks for the suggestion but the queries above is the ones that i tried but its not working for me either.
I too want to just report the Date as Running instead of changing anything.

If possible for you can you suggest the mistake in my query of some query of yours that might work.

Can you debug code you don't see?
Neither can I.
I've not seen your attempt to simply SELECT the date and use NVL to return a string if null. But John Watson has already shown you exactly how to do it.

Report message to a moderator

Re: Replace Date column to Varchar Value [message #610387 is a reply to message #610385]

Wed, 19 March 2014 12:37

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

EdStevens wrote on Wed, 19 March 2014 13:28

Can you debug code you don't see?
Neither can I.

And OP, I don't see any table definitions either. A blind insert that you show is poor and dangerous programming.

Report message to a moderator

Previous Topic:	Rename Tablespace
Next Topic:	Doubt with Associative Arrays

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:23:47 CDT 2024