?
Solved

Return values from between commas

Posted on 2009-04-06
25
Medium Priority
?
285 Views
Last Modified: 2013-12-18
Guys

I am after some help. I need to search a string from within oracle 10.2
the string is made up of info seperated between "," but the order of the info can change
ie

swo12,sht12,dose4,can34
sht23,swo34,can55,dose44
dose3,can55,sht3,swo3

I need something to search for the first 3 letters ie  "swo"  or "can" or "dos" then return the info that is between the ",".
so search for "can"

sht23,swo34,can55,dose44

returns = can55

I dont think a standard sql script will be able to do this more like a package or something

Can anyone help

I will attach a script that I use to return info from "," but what I am after this wont work.

I dont think


CREATE OR REPLACE PACKAGE BODY IFSAPP.hert_column_sort IS
 
FUNCTION col(text_in IN VARCHAR2, number_in IN NUMBER)
RETURN NUMBER
IS
  tempval NUMBER;
  number_in_ NUMBER;
  text_in_  VARCHAR2(400);
  BEGIN
 
  number_in_ := number_in;
  text_in_ := text_in;
 
  IF number_in_ <=0 THEN
   tempval := 0;
  ELSIF number_in_ = 1 THEN
   tempval :=  to_number(substr(text_in_,1,instr(text_in_,';')-1));
  ELSE
   tempval := to_number(substr(text_in_,instr(text_in_,';',1,number_in_-1)+1,instr(text_in_,';',1,number_in_)-1-instr(text_in_,';',1,number_in_-1) ));
  END IF;
 RETURN tempval;
END;
 
END hert_column_sort;

Open in new window

0
Comment
Question by:DarrenJackson
[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
  • 14
  • 11
25 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24078789
try something like this...

you can change the 100 to be at least as big as the most number of substrings you might have
SELECT x
  FROM (SELECT SUBSTR(str, from_pos, to_pos - from_pos) AS x
          FROM (SELECT str,
                       n,
                       CASE WHEN n = 0 THEN 
                              1 ELSE 1 + INSTR(str, ',', 1, n) 
                       END AS from_pos,
                       CASE
                           WHEN n = LENGTH(str) - LENGTH(REPLACE(str, ',', NULL))
                             OR LENGTH(str) - LENGTH(REPLACE(str, ',', NULL)) = 0
                           THEN
                               1 + LENGTH(str)
                           ELSE
                               INSTR(str, ',', 1, n + 1)
                       END
                           AS to_pos
                  FROM (SELECT 'sht23,swo34,can55,dose44' str FROM DUAL), 
                       (SELECT LEVEL - 1 AS n FROM DUAL CONNECT BY LEVEL <= 100)))
 WHERE x LIKE 'can%'

Open in new window

0
 

Author Comment

by:DarrenJackson
ID: 24078834
Wow that was fast

Thanks for replyng

Let me look at this

Cheers
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24078848
slightly more efficient version...
SELECT x
  FROM (SELECT SUBSTR(str, from_pos, to_pos - from_pos) AS x
          FROM (SELECT str,
                       n,
                       CASE WHEN n = 0 THEN 1 ELSE 1 + INSTR(str, ',', 1, n) END AS from_pos,
                       CASE
                           WHEN n = LENGTH(str) - LENGTH(REPLACE(str, ',', NULL))
                             OR LENGTH(str) - LENGTH(REPLACE(str, ',', NULL)) = 0
                           THEN
                               1 + LENGTH(str)
                           ELSE
                               INSTR(str, ',', 1, n + 1)
                       END
                           AS to_pos
                  FROM (    SELECT str, LEVEL n
                              FROM (    SELECT 'sht23,swo34,can55,dose44' str FROM DUAL) z
                        CONNECT BY LEVEL <= LENGTH(z.str) - LENGTH(REPLACE(z.str, ',', NULL)))))
 WHERE x LIKE 'can%'

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:DarrenJackson
ID: 24078924
didnt realise you could do this I was fully expecting a package
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24078966
yeah, pretty neat huh!

and here's a slightly more compact version (and eliminates the need to hardcode a limit on the number of substrings too)
SELECT x
  FROM (SELECT SUBSTR(str, from_pos, to_pos - from_pos) AS x
          FROM (SELECT str,
                       n,
                       CASE WHEN n = 0 THEN 
                              1 ELSE 1 + INSTR(str, ',', 1, n) 
                       END AS from_pos,
                       CASE
                           WHEN n = numcommas OR numcommas = 0 THEN 1 + LENGTH(str)
                           ELSE INSTR(str, ',', 1, n + 1)
                       END
                           AS to_pos
                  FROM (SELECT str, LEVEL n, numcommas
                         FROM (SELECT str,
                                     LENGTH(z.str) - LENGTH(REPLACE(z.str, ',', NULL)) numcommas
                               FROM (SELECT 'sht23,swo34,can55,dose44' str FROM DUAL) z)
                        CONNECT BY LEVEL <= numcommas)))
 WHERE x LIKE 'can%'

Open in new window

0
 

Author Comment

by:DarrenJackson
ID: 24079012
it is cool

If I wanted to expand on this and include other views / tables to build this up

have you got any tips or examples that I could use to keep the perfomance from slipping??
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24079054
I think I'd have to see what you were doing to give a recommendation.
0
 

Author Comment

by:DarrenJackson
ID: 24079165
ok here is the code i used originally

you can see the package hert_column_sort_varchar.col this was before you posted your code
Iam trying to include your code in this

somehow
select 
        dt.note_text,
        to_date(wo.cre_date,'dd/mm/yyyy') as "DATE",
        hert_column_sort_varchar.col(replace(dt.note_text||',',',',';'),2) as "SHT",
        hert_column_sort_varchar.col(replace(dt.note_text,',',';'),3) as "SWO",
        wo.role_code,
        company_emp_api.get_name(wo.company,wo.emp_no) as "NAME",
        wo.list_price as HOURLY_RATE,
        wo.qty as hours,
        case when lpad(hert_column_sort_varchar.col(replace(dt.note_text,',',';'),2),3) like 'NFR'
        THEN TO_NUMBER('')
        when lpad(hert_column_sort_varchar.col(replace(dt.note_text,',',';'),2),3) like 'CVI' 
        then TO_NUMBER('') 
        else  wo.amount end as "VALUE",
        case when lpad(hert_column_sort_varchar.col(replace(dt.note_text,',',';'),2),3)='NFR' then 
        hert_column_sort_varchar.col(replace(dt.note_text,',',';'),2) ELSE '' end as "NFR",
        case when lpad(hert_column_sort_varchar.col(replace(dt.note_text,',',';'),2),3)='NFR' then 
        wo.amount ELSE TO_NUMBER('') end as "VALUE",
        case when lpad(hert_column_sort_varchar.col(replace(dt.note_text,',',';'),2),3)='CVI' then 
        hert_column_sort_varchar.col(replace(dt.note_text,',',';'),2) ELSE '' end as "CVI",
        case when lpad(hert_column_sort_varchar.col(replace(dt.note_text,',',';'),2),3)='CVI' then 
        wo.amount ELSE TO_NUMBER('') end as "VALUE",
        (hert_column_sort_varchar.col(replace(dt.note_text,',',';'),1)) as "DOSAGE"
                        
  from  ifsapp.active_separate acs inner join
        ifsapp.work_order_coding wo on
        acs.wo_no = wo.wo_no
        left join ifsapp.document_text dt on wo.note_id = dt.note_id
        inner join ifsapp.employee ee on wo.emp_no = ee.emp_no
  
  
 where  
   acs.contract = '01'
   and (wo.cre_date >=
       to_date('&From', 'DD-MM-YYYY HH24:MI:SS') and
       wo.cre_date <
       to_date('&To', 'DD-MM-YYYY HH24:MI:SS'))
   and wo.work_order_cost_type = 'Personnel'
 
 order by wo.cre_date,wo.emp_no
 

Open in new window

0
 

Author Comment

by:DarrenJackson
ID: 24080341
I take it that it isnt as easy to integrate the code you supplied?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24080413
no, not a few seconds effort.  It was more than I could work in between my own tasks.

I can probably still do it but it'll be awhile before I'll be able to put in the time needed.

what will help though is being able to work with some raw data.
what is the result of this... and what do you want the output to be.
If this would be too much, then reduce to a usable sample.

select *
  from  ifsapp.active_separate acs inner join
        ifsapp.work_order_coding wo on
        acs.wo_no = wo.wo_no
        left join ifsapp.document_text dt on wo.note_id = dt.note_id
        inner join ifsapp.employee ee on wo.emp_no = ee.emp_no    
 where  
   acs.contract = '01'
   and (wo.cre_date >=
       to_date('&From', 'DD-MM-YYYY HH24:MI:SS') and
       wo.cre_date <
       to_date('&To', 'DD-MM-YYYY HH24:MI:SS'))
   and wo.work_order_cost_type = 'Personnel'
  order by wo.cre_date,wo.emp_no
 
0
 

Author Comment

by:DarrenJackson
ID: 24080469
I understand

The code you supplied is the key to this working. What I had in my code is have a package that would count how many "," then return the value that was between.

All I need is the ability to add a new table / view to your code and I will replicate this to add in the remainder.

All I need is for you to start me of.

If this is OK that is :)

Dont want you to do too much here you have helped enough.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24080622
you can do the expansion of the substrings with a join like in my first suggestion,
probably the easiest way....

from  ifsapp.active_separate acs inner join
        ifsapp.work_order_coding wo on
        acs.wo_no = wo.wo_no
        left join ifsapp.document_text dt on wo.note_id = dt.note_id
        inner join ifsapp.employee ee on wo.emp_no = ee.emp_no
        inner join (select level n from dual connect by level <= 100) on n <= LENGTH(dt.note_text) - LENGTH(REPLACE(dt.note_text, ',', NULL))




       
0
 

Author Comment

by:DarrenJackson
ID: 24081027
im struggling with this.

If it is OK I will send you some sample data if you can look at this when you can that would be great

you will notice that in the column named note_text this is the source of what your code is looking at you will see that where my code fails is when there isnt the correct sequence of "," as my code looks at the number of "," and returns that so some times like row 4 the CVI is under the column named sht

Cheers
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24081060
yes, sample data and expected output for that sample would be great
0
 

Author Comment

by:DarrenJackson
ID: 24081064
File Didnt attach
0
 

Author Comment

by:DarrenJackson
ID: 24081171
cant seem to attach a file so I have copied the sample into the code pane it is comma seperated
NOTE_TEXT,DATE,SHT,SWO,ROLE_CODE,NAME,HOURLY_RATE,HOURS,VALUE,NFR,VALUE,CVI,VALUE,DOSAGE
0,SHT 001,01/04/0009,SHT 001,,CH-SELNT,Brian Anderson,24.92,4,91.8,,,,,0
0,SHT 001,01/04/0009,SHT 001,,CH-SELNT,Brian Anderson,0,4.25,97.5375,,,,,0
0.111,CVI 98758,SHT 001,01/04/0009,CVI 98758,,CH-SELNT,George Agnew,24.92,8.25,,,,CVI 98758,189.3375,0.111
0,SHT 002,01/04/0009,SHT 002,,ALL3M,Jason Adams,0.5,6,2.46,,,,,0
0.100,SHT 002,01/04/0009,SHT 002,,SEMINT,Jason Adams,19.95,8.25,149.16,,,,,0.100
0.100,SHT 002,01/04/0009,SHT 002,,CRF-SELNT,Paul Agnew,23.36,8.25,176.715,,,,,0.100
0,SHT 002,01/04/0009,SHT 002,,ALL3M,Paul Agnew,0.5,6,2.46,,,,,0
,01/04/0009,,,CH-SELNT,Ernest Blows,0,8.25,189.3375,,,,,
0,CVI 73525,SHT 003,01/04/0009,CVI 73525,,ADV-CRTNT,Derek Boyd,24.14,8.25,,,,CVI 73525,183.0675,0
0,SHT 001,01/04/0009,SHT 001,,PNT-SELNT,William Burns,0,8.25,155.0175,,,,,0
0,SWO 316,SHT 003,01/04/0009,SWO 316,,PNT-SELNT,Keith Bragg,20.47,8.25,155.0175,,,,,0
0.044,SWO 732/08/09,SHT 001,01/04/0009,SWO 732/08/09,,SEMINT,David Bragg,19.95,8.25,149.16,,,,,0.044
0,SWO 567/08/09,SHT 003,01/04/0009,SWO 567/08/09,,CRF-SELNT,Kyle Blows,22.78,8.25,176.715,,,,,0
,01/04/0009,,,SSUPNT,Mark Bennett,0,2,61.44,,,,,

Open in new window

0
 

Author Comment

by:DarrenJackson
ID: 24081280
Sorry I exported it wrongly
the note_text field  is the expected result that your code looks at then translates into sepertate columns
I have exported it out as a semicolon delimited and the note_text field is comma delimited just to give you an idea
NOTE_TEXT;DATE;SHT;SWO;ROLE_CODE;NAME;HOURLY_RATE;HOURS;VALUE;NFR;VALUE;CVI;VALUE;DOSAGE
0,SHT 001;01/04/0009;SHT 001;;CH-SELNT;Brian Anderson;24.92;4;91.8;;;;;0
0,SHT 001;01/04/0009;SHT 001;;CH-SELNT;Brian Anderson;0;4.25;97.5375;;;;;0
0.111,CVI 98758,SHT 001;01/04/0009;CVI 98758;;CH-SELNT;George Agnew;24.92;8.25;;;;CVI 98758;189.3375;0.111
0,SHT 002;01/04/0009;SHT 002;;ALL3M;Jason Adams;0.5;6;2.46;;;;;0
0.100,SHT 002;01/04/0009;SHT 002;;SEMINT;Jason Adams;19.95;8.25;149.16;;;;;0.100
0.100,SHT 002;01/04/0009;SHT 002;;CRF-SELNT;Paul Agnew;23.36;8.25;176.715;;;;;0.100
0,SHT 002;01/04/0009;SHT 002;;ALL3M;Paul Agnew;0.5;6;2.46;;;;;0
;01/04/0009;;;CH-SELNT;Ernest Blows;0;8.25;189.3375;;;;;
0,CVI 73525,SHT 003;01/04/0009;CVI 73525;;ADV-CRTNT;Derek Boyd;24.14;8.25;;;;CVI 73525;183.0675;0
0,SHT 001;01/04/0009;SHT 001;;PNT-SELNT;William Burns;0;8.25;155.0175;;;;;0
0,SWO 316,SHT 003;01/04/0009;SWO 316;;PNT-SELNT;Keith Bragg;20.47;8.25;155.0175;;;;;0
0.044,SWO 732/08/09,SHT 001;01/04/0009;SWO 732/08/09;;SEMINT;David Bragg;19.95;8.25;149.16;;;;;0.044
0,SWO 567/08/09,SHT 003;01/04/0009;SWO 567/08/09;;CRF-SELNT;Kyle Blows;22.78;8.25;176.715;;;;;0
;01/04/0009;;;SSUPNT;Mark Bennett;0;2;61.44;;;;;

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24092616
the above export is what?

is that the result of this...

select *
  from  ifsapp.active_separate acs inner join
        ifsapp.work_order_coding wo on
        acs.wo_no = wo.wo_no
        left join ifsapp.document_text dt on wo.note_id = dt.note_id
        inner join ifsapp.employee ee on wo.emp_no = ee.emp_no    
 where  
   acs.contract = '01'
   and (wo.cre_date >=
       to_date('&From', 'DD-MM-YYYY HH24:MI:SS') and
       wo.cre_date <
       to_date('&To', 'DD-MM-YYYY HH24:MI:SS'))
   and wo.work_order_cost_type = 'Personnel'
  order by wo.cre_date,wo.emp_no

what are the three "value" columns?

what do you want the output to be for this input?

0
 

Author Comment

by:DarrenJackson
ID: 24092658
Yes

The 3 value columns you can ignore the fields of interest is the note_text which contains all the info that I have asked you to splt out this is represented by the columns named SHT,SWO,NFR located in the ifsapp.document_text dt  the rest is located in the work_order_coding  and the hourly rate is located in the active seperate

If you can get it so that there is 3 seperate column showing the SHT,SWO,NFR values the remaining just beside it that would be great
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24092824
I'm not sure but I "think" this is what you're looking for...

if not, please post what you want the output to be, like you showed the input, give me rows and columns of what you expect this to return given the input in post 24081280
  SELECT   dt.note_text,
           TO_DATE(wo.cre_date, 'dd/mm/yyyy') AS "DATE",
           (SELECT   x
              FROM   (SELECT   SUBSTR(str, from_pos, to_pos - from_pos) AS x
                        FROM   (SELECT   str,
                                         n,
                                         CASE WHEN n = 0 THEN 1 ELSE 1 + INSTR(str, ',', 1, n) END
                                             AS from_pos,
                                         CASE
                                             WHEN n = numcommas OR numcommas = 0 THEN 1 + LENGTH(str)
                                             ELSE INSTR(str, ',', 1, n + 1)
                                         END
                                             AS to_pos
                                  FROM   (    SELECT   str, LEVEL n, numcommas
                                                FROM   (SELECT   str,
                                                                 LENGTH(z.str)
                                                                 - LENGTH(REPLACE(z.str, ',', NULL))
                                                                     numcommas
                                                          FROM   (SELECT   note_text str FROM DUAL) z)
                                          CONNECT BY   LEVEL <= numcommas)))
             WHERE   x LIKE 'SHT%')
               AS "SHT",
           (SELECT   x
              FROM   (SELECT   SUBSTR(str, from_pos, to_pos - from_pos) AS x
                        FROM   (SELECT   str,
                                         n,
                                         CASE WHEN n = 0 THEN 1 ELSE 1 + INSTR(str, ',', 1, n) END
                                             AS from_pos,
                                         CASE
                                             WHEN n = numcommas OR numcommas = 0 THEN 1 + LENGTH(str)
                                             ELSE INSTR(str, ',', 1, n + 1)
                                         END
                                             AS to_pos
                                  FROM   (    SELECT   str, LEVEL n, numcommas
                                                FROM   (SELECT   str,
                                                                 LENGTH(z.str)
                                                                 - LENGTH(REPLACE(z.str, ',', NULL))
                                                                     numcommas
                                                          FROM   (SELECT   note_text str FROM DUAL) z)
                                          CONNECT BY   LEVEL <= numcommas)))
             WHERE   x LIKE 'SWO%')
               AS "SWO",
           wo.role_code,
           company_emp_api.get_name(wo.company, wo.emp_no) AS "NAME",
           wo.list_price AS hourly_rate,
           wo.qty AS hours,
           CASE
               WHEN LPAD(hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2), 3) LIKE
                        'NFR' THEN
                   TO_NUMBER('')
               WHEN LPAD(hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2), 3) LIKE
                        'CVI' THEN
                   TO_NUMBER('')
               ELSE
                   wo.amount
           END
               AS "VALUE",
           (SELECT   x
              FROM   (SELECT   SUBSTR(str, from_pos, to_pos - from_pos) AS x
                        FROM   (SELECT   str,
                                         n,
                                         CASE WHEN n = 0 THEN 1 ELSE 1 + INSTR(str, ',', 1, n) END
                                             AS from_pos,
                                         CASE
                                             WHEN n = numcommas OR numcommas = 0 THEN 1 + LENGTH(str)
                                             ELSE INSTR(str, ',', 1, n + 1)
                                         END
                                             AS to_pos
                                  FROM   (    SELECT   str, LEVEL n, numcommas
                                                FROM   (SELECT   str,
                                                                 LENGTH(z.str)
                                                                 - LENGTH(REPLACE(z.str, ',', NULL))
                                                                     numcommas
                                                          FROM   (SELECT   note_text str FROM DUAL) z)
                                          CONNECT BY   LEVEL <= numcommas)))
             WHERE   x LIKE 'NFR%')
               AS "NFR",
           CASE
               WHEN LPAD(hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2), 3) = 'NFR' THEN
                   wo.amount
               ELSE
                   TO_NUMBER('')
           END
               AS "VALUE",
           CASE
               WHEN LPAD(hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2), 3) = 'CVI' THEN
                   hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2)
               ELSE
                   ''
           END
               AS "CVI",
           CASE
               WHEN LPAD(hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2), 3) = 'CVI' THEN
                   wo.amount
               ELSE
                   TO_NUMBER('')
           END
               AS "VALUE",
           (hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 1)) AS "DOSAGE"
    FROM   ifsapp.active_separate acs INNER JOIN ifsapp.work_order_coding wo ON acs.wo_no = wo.wo_no
           LEFT JOIN ifsapp.document_text dt
               ON wo.note_id = dt.note_id
           INNER JOIN ifsapp.employee ee
               ON wo.emp_no = ee.emp_no
   WHERE   acs.contract = '01'
       AND (wo.cre_date >= TO_DATE('&From', 'DD-MM-YYYY HH24:MI:SS')
        AND wo.cre_date < TO_DATE('&To', 'DD-MM-YYYY HH24:MI:SS'))
       AND wo.work_order_cost_type = 'Personnel'
ORDER BY   wo.cre_date, wo.emp_no

Open in new window

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 24092852
or maybe you're looking for something very simple like regular expression searches
SELECT   dt.note_text,
           TO_DATE(wo.cre_date, 'dd/mm/yyyy') AS "DATE",
           REGEXP_SUBSTR(note_text, 'SHT [0-9]+') AS "SHT",
           REGEXP_SUBSTR(note_text, 'SWO [0-9]+') AS "SWO",
           wo.role_code,
           company_emp_api.get_name(wo.company, wo.emp_no) AS "NAME",
           wo.list_price AS hourly_rate,
           wo.qty AS hours,
           CASE
               WHEN LPAD(hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2), 3) LIKE
                        'NFR' THEN
                   TO_NUMBER('')
               WHEN LPAD(hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2), 3) LIKE
                        'CVI' THEN
                   TO_NUMBER('')
               ELSE
                   wo.amount
           END
               AS "VALUE",
           REGEXP_SUBSTR(note_text, 'NFR [0-9]+') AS "NFR",
           CASE
               WHEN LPAD(hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2), 3) = 'NFR' THEN
                   wo.amount
               ELSE
                   TO_NUMBER('')
           END
               AS "VALUE",
           CASE
               WHEN LPAD(hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2), 3) = 'CVI' THEN
                   hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2)
               ELSE
                   ''
           END
               AS "CVI",
           CASE
               WHEN LPAD(hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 2), 3) = 'CVI' THEN
                   wo.amount
               ELSE
                   TO_NUMBER('')
           END
               AS "VALUE",
           (hert_column_sort_varchar.col(REPLACE(dt.note_text, ',', ';'), 1)) AS "DOSAGE"
    FROM   ifsapp.active_separate acs INNER JOIN ifsapp.work_order_coding wo ON acs.wo_no = wo.wo_no
           LEFT JOIN ifsapp.document_text dt
               ON wo.note_id = dt.note_id
           INNER JOIN ifsapp.employee ee
               ON wo.emp_no = ee.emp_no
   WHERE   acs.contract = '01'
       AND (wo.cre_date >= TO_DATE('&From', 'DD-MM-YYYY HH24:MI:SS')
        AND wo.cre_date < TO_DATE('&To', 'DD-MM-YYYY HH24:MI:SS'))
       AND wo.work_order_cost_type = 'Personnel'
ORDER BY   wo.cre_date, wo.emp_no

Open in new window

0
 

Author Comment

by:DarrenJackson
ID: 24094604
sdstuber thanks for this I lost my network connection last nigth so i am just checking them out now

Will come back to you

Cheers
0
 

Author Comment

by:DarrenJackson
ID: 24096329
sdstuber

The regular expressions is much easier also accomplishes what I need

Many Thanks for your time & effort
0
 

Author Closing Comment

by:DarrenJackson
ID: 31567100
Excellent sage classification does no justice :)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24096462
glad I could help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

764 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