Solved

Create columns in query based on string values in other column

Posted on 2013-12-03
13
444 Views
Last Modified: 2013-12-05
Hi

I'm trying to create a view that adds two additional columns based on a string value in another column.  I need to extract from the string a file name and a date/time value.

Representative samples of the string is below:

505_247703b0bab8_2012-11-14-16_32_12_068.00506
505_247703b0bab8_2012-11-14-16_32_12_068.00506
505_247703b0bab8_2012-11-14-16_32_12_068.00506
505_247703b0bab8_2012-11-14-16_32_12_068.00506

Note:  The separator in the string is the underscore "_"


I want to create two new columns in the view, one called filename and one called DateLastUploaded.

Using the "_" (underscore) as the separator, I want to extract the filename to one column and the date and time to another.  Using the above as a quick example, we would end up with two columns looking like this:

FILENAME                           DATELASTUPLOADED
--------------                           ------------------------------
505_247703b0bab8          14/11/2012 16:32:12
505_247703b0bab8          14/11/2012 16:32:12
505_247703b0bab8          14/11/2012 16:32:12
505_247703b0bab8          14/11/2012 16:32:12

Attached to this question is some sample data.  If you create a table from the sample data called HEALTHCHECK, the column containing the existing string is called LAST_DATA_FILENAME.

Having created the two new columns, I need to use their values in the HAVING clause e.g.

Select ..... (logic to select all existing columns and create two new columns called FILENAME AND LASTUPLOADDATE)
From HEALTHCHECK
GROUP BY  ..... To remove duplicates ....
HAVING MAX (HEALTHCHECK.DATELASTUPLOADED) < SYSDATE  - 0.5

Any help would be much appreciated.

Kind regards
0
Comment
Question by:pm620wh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
13 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39692302
As long as the size doesn't vary, you could use something like this:
select substr(<your_col>,1,16) filename,
       to_date(substr(<your_col>,18,19), 'yyyy-mm-dd-hh24_mi_ss') datelastuploaded
from ....

Open in new window

0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39692322
can't see attachment

you can extract the filename and date with regexp_substr and the subexpression with oracle 11

select 
regexp_substr(yourcolumn, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 1) filename, 
regexp_substr(yourcolumn, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 2) DATELASTUPLOADED
from table;

Open in new window

0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39692325
if you want it formatted as a date

select
regexp_substr(yourcolumn, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 1) filename,
to_date(regexp_substr(yourcolumn, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 2), 'yyyy-mm-dd-hh24_mi_ss') DATELASTUPLOADED
from table;
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:pm620wh
ID: 39692567
Sorry, I did attach it once but a careless click of the back key lost my entry and I forgot to re-attach it.

Here it is.
Sample-Data.xls
0
 
LVL 35

Accepted Solution

by:
johnsone earned 250 total points
ID: 39692814
There is also the substr method of doing it

SELECT Substr(last_data_filename, 1, Instr(last_data_filename, '_', 1, 2) - 1) 
       FILENAME 
       , 
       To_date(Substr(last_data_filename, Instr(last_data_filename, '_', 
                                          1, 2) + 1, 
               Instr( 
                       last_data_filename, '_', 1, 5) - 
               Instr(last_data_filename, '_', 1, 2) 
                       - 1), 'yyyy-mm-dd-hh24:mi:ss') 
       DATELASTUPLOADED 
FROM   healthcheck; 

Open in new window

0
 

Author Comment

by:pm620wh
ID: 39692946
Hi johnsone

This is perfect thank you as it converts the column to a date.

May I ask one more question?  

How may I used the new column DATELASTUPLOADED in the same query to filter the resulting dates (I need to find only those dates that are up to 12 hours old).

With my limited knowledge, I was going to create a view and then use another view to query it.  Would that be the best approach please?

Thank you - almost there.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 39692977
create view new_columns_vw as
select <desiredcolumns>,
substr(last_data_filename,1,instr(last_data_filename,'_',1,2) - 1) filename,
to_date(substr(last_data_filename,instr(last_data_filename,'_',1,2) + 1,19),'yyyy-mm-dd_hh24_mi_ss') datelastuploaded
from healthcheck
where to_date(substr(last_data_filename,instr(last_data_filename,'_',1,2) + 1,19),'yyyy-mm-dd_hh24_mi_ss') > sysdate - 0.5;

select * from new_columns_vw;
0
 
LVL 35

Expert Comment

by:johnsone
ID: 39693005
To use the date in the where clause, you need to repeat the to_date part of the query in the where like this:

SELECT Substr(last_data_filename, 1, Instr(last_data_filename, '_', 1, 2) - 1) 
       FILENAME 
       , 
       To_date(Substr(last_data_filename, Instr(last_data_filename, '_', 
                                          1, 2) + 1, 
               Instr( 
                       last_data_filename, '_', 1, 5) - 
               Instr(last_data_filename, '_', 1, 2) 
                       - 1), 'yyyy-mm-dd-hh24:mi:ss') 
       DATELASTUPLOADED 
FROM   healthcheck 
WHERE  To_date(Substr(last_data_filename, Instr(last_data_filename, '_', 1, 2) + 
                                          1, 
                      Instr( 
                              last_data_filename, '_', 1, 5) - 
                              Instr(last_data_filename, '_', 1, 2) - 1), 
              'yyyy-mm-dd-hh24:mi:ss') >= SYSDATE - 0.5; 

Open in new window


If this is a query that is going to run often, I would recommend putting a function based index on that to_date part of the query.  Without it, you would be doing a full table scan every time you run the query.

Here is a link to some of the documentation on function based indexes -> http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN11731
0
 

Author Comment

by:pm620wh
ID: 39693052
Perfect!

I feel that the last two solutions jointly solved this issue for me as johnsone showed me how to convert he values to a date and awking00 took me over the line.

Thank you to everyone however for your guidance an input.  I've learnt loads today.
0
 

Author Comment

by:pm620wh
ID: 39693657
I've requested that this question be closed as follows:

Accepted answer: 300 points for johnsone's comment #a39692814
Assisted answer: 200 points for awking00's comment #a39692977
Assisted answer: 0 points for pm620wh's comment #a39693052

for the following reason:

Very quick replies and really useful.  Helped me loads

Thank you to everyone
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39693658
Please review the distribution of the points ;-)
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39695699
did you actually try my approach ?
perfectly valid for separating 1 column into multiple column in oracle 11 !

btw, it will work for any format of filename before the date

ah ... i see you added the attachment

let me modify it so it works with your column name and table name
select
regexp_substr(last_data_filename, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 1) filename,
to_date(regexp_substr(last_data_filename, '(.*)_(\d{4}-\d{1,2}-\d{1,2}-\d{1,2}_\d{1,2}_\d{1,2})_(.*)', 1, 1, 'i', 2), 'yyyy-mm-dd-hh24_mi_ss') DATELASTUPLOADED
from healthcheck
 

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question