Solved

Return values from between commas

Posted on 2009-04-06
25
266 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
  • 14
  • 11
25 Comments
 
LVL 73

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 73

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
 

Author Comment

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

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 73

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 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 73

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 73

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 73

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 73

Accepted Solution

by:
sdstuber earned 500 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 73

Expert Comment

by:sdstuber
ID: 24096462
glad I could help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now