Home » SQL & PL/SQL » SQL & PL/SQL » Create table as select (Oracle 11.2, AIX)
Create table as select [message #677166] Wed, 28 August 2019 04:55 Go to next message
corina
Messages: 2
Registered: August 2019
Junior Member
Hi guys,
I need help/suggestions with the following problem.

- I have a table with aroud 13 thousand rows;
- from the description column of type varchar2 (1000Char) I need to extract only the strings that start with characther '6' and can exist multiple series. The characters are separated by ',' and after every series is ending with CRLF.This part is done with the bellow query:

select NR, ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,1),'6'),','), ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,2),'6'),','), ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,3),'6'),','),
ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,4),'6'),','), ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,5),'6'),','), ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,6),'6'),','),
ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,7),'6'),','), ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,Cool,'6'),','), ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,9),'6'),','),
ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,10),'6'),',')
from msg_rule_log
where OBJECT_ID like 'CPA%'
and regexp_substr(description,'6,[^,]*') is not null
and regexp_substr(description,'6,[^,]*') not like '%-%';
output:
e.g:

NR camp1 camp2 camp3 camp4 camp5 camp6 capm7 camp8 camp9
19110814253903F00 78312 61026 61269 61285 2/4/2010 13:51

- with this output I want to create a table which will be used in another query; most of the output is numeric but can exist also values of type DATA which are not relevant for me and can be converted in NULL.

Any suggestion is welcome.

Re: Create table as select [message #677167 is a reply to message #677166] Wed, 28 August 2019 07:01 Go to previous messageGo to next message
EdStevens
Messages: 1125
Registered: September 2013
Senior Member
Quote:
most of the output is numeric but can exist also values of type DATA
In oracle there is no data type 'DATA'.
And the fact that you are needing to extract multiple data elements from a single column is strongly indicative of a seriously flawed data design.

That said, do you really need to put the results into another table, just to be queried again? Why not something like this?

with a as (select NR, 
                  ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,1),'6'),','), 
                  ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,2),'6'),','), 
                  ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,3),'6'),','),
                  ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,4),'6'),','), 
                  ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,5),'6'),','), 
                  ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,6),'6'),','),
                  ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,7),'6'),','), 
                  ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,Cool,'6'),','), 
                  ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,9),'6'),','),
                  ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,10),'6'),',')
           from msg_rule_log
           where OBJECT_ID like 'CPA%'
             and regexp_substr(description,'6,[^,]*') is not null
             and regexp_substr(description,'6,[^,]*') not like '%-%'           
           )
select <whatever> 
from a
;

[Updated on: Wed, 28 August 2019 07:02]

Report message to a moderator

Re: Create table as select [message #677168 is a reply to message #677167] Wed, 28 August 2019 09:19 Go to previous messageGo to next message
corina
Messages: 2
Registered: August 2019
Junior Member
Sorry for the typo, I was refering of datatype DATE.
It is a big table and a complex query, the time of execution is to big.For this reason I want to extract data I need in a new table.
Re: Create table as select [message #677171 is a reply to message #677168] Wed, 28 August 2019 13:40 Go to previous messageGo to next message
Bill B
Messages: 1899
Registered: December 2004
Senior Member
Im curious, is their an application that can't be modified to create a better database structure? A parent and daughter table would cause your application to be much more reactive.
Re: Create table as select [message #677172 is a reply to message #677171] Wed, 28 August 2019 15:30 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Create table as select [message #677185 is a reply to message #677168] Thu, 29 August 2019 08:24 Go to previous messageGo to next message
EdStevens
Messages: 1125
Registered: September 2013
Senior Member
corina wrote on Wed, 28 August 2019 09:19
Sorry for the typo, I was refering of datatype DATE.
It is a big table and a complex query, the time of execution is to big.For this reason I want to extract data I need in a new table.
Perhaps you should consider a materialized view. This kind of preprocessing of complex, cpu-intensive queries is what materialized views were designed for.

And I would reiterate, the fact that you are needing to extract multiple data elements from a single column is strongly indicative of a seriously flawed data design. Perhaps you should read up on 'data normalization' and 'third normal form'.
Re: Create table as select [message #677190 is a reply to message #677185] Thu, 29 August 2019 10:38 Go to previous message
Michel Cadot
Messages: 66650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Perhaps you should read up on 'data normalization' and 'third normal form'.
Starting with our Normalization wiki page.

Previous Topic: help with sql grouping
Next Topic: Additional Column Indicator for Failed or Passed
Goto Forum:
  


Current Time: Mon Oct 21 19:58:53 CDT 2019