Create table based on select

  1. Corina

    Corina Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Romania
    How can a create a table as select if the output of the query is mostly numeric but can exist also values of type DATE which can exist in any of the columns (values of type DATE are not relevant and can be converted in NULL when are find)?
    e.g:
    NR camp1 camp2 camp3 camp4 camp5 camp6 capm7 camp8 camp9 camp10
    19110814253903F00 78312 61026 61269 61285 2/4/2010 13:51

    Any sugestion is welcome.
     
    Last edited: Aug 28, 2019
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,688
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The first suggestion is to make your question understandable; what EXACTLY is 'DATA' as it's not a valid data type in Oracle? Your example is anything but clear -- WHY do you have a table with 'DATA' in it that is completely useless? This makes no sense.
     
  3. Corina

    Corina Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Romania
    Hi David,
    It was a typo and I corrected, I was refering to datatype DATE as it is in the example.
    I will try to be more understadable. I have a complex query with multiple join which is taking to much,it is a report. My question it is just a part of the query..... I want to simplify and to extract in a diffrent table only the informations I need.
    -the table is quite big , it has around 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. For this part I used bellow query:

    select Tran, 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,[​IMG],'6'),','), ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,9),'6'),','),
    ltrim(ltrim(regexp_substr(description,'6,[^,]*',1,10),'6'),',')
    from msg_rule_log
    where
    regexp_substr(description,'6,[^,]*') is not null
    and regexp_substr(description,'6,[^,]*') not like '%-%';

    output: output.jpg
    In the output it is extracted also values of type DATE, which can be found in any posistion, except first and second. Only informations of type numeric are relevant, values of datatype DATE can be convertend in NULL.
     

    Attached Files:

  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,688
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The best option you have is to simply create the table using the query as written, then set to NULL any date values in that table. You could try to write a 'wrapper' SQL to eliminate the DATE values but I'm not sure how successful that would be since, by your own admission, these dates can appear in any of the columns being selected. You can also attempt to refine your regular expressions to eliminate these dates.