Home > Default > SQL query problem

SQL query problem

November 30Hits:0
Advertisement
Hello,
I am trying to split the result of a field in "sub" results:
select otname from ordertrailer: this statement results in
OTNAME
163.24.27.167.U.BASE.F.I.4.3.10.837.1.34
I would like to have a query which gives me as results, the following:
OTNAME AA BB CC DD
163.24.27.167.U.BASE.F.I.4.3.10.837.1.34 163 24 27 167
and so on...
I have used the following query:
select id, otname, decode(id,1,substr(otname,1,instr(otname,'.',1,1)-1),decode(instr(otname,'.',1,id),0,
substr(otname,instr(otname,'.',1,id-1)+1,length(otname)-instr(otname,'.',1,id-1)+1),
substr(otname,instr(otname,'.',1,id-1)+1,instr(otname,'.',1,id)-instr(otname,'.',1,id-1)-1))) value
from (
select rownum id from dual
connect by level <= (select length(otname)-length(replace(otname,'.',''))+1
from ordertrailer
where otxact = 36852284
and otseq = 0)
) temp,
ordertrailer
where otxact = 36852284
and otseq = 6;
but I get the results in different rows:
ID     OTNAME     value
1     163.24.27.167.U.BASE.F.I.4.3.10.837.1.34     163
2     163.24.27.167.U.BASE.F.I.4.3.10.837.1.34     24
3     163.24.27.167.U.BASE.F.I.4.3.10.837.1.34     27
4     163.24.27.167.U.BASE.F.I.4.3.10.837.1.34     167
5     163.24.27.167.U.BASE.F.I.4.3.10.837.1.34     U
6     163.24.27.167.U.BASE.F.I.4.3.10.837.1.34     BASE
7     163.24.27.167.U.BASE.F.I.4.3.10.837.1.34     F
8     163.24.27.167.U.BASE.F.I.4.3.10.837.1.34     I
I need instead to have all data on the same row.
Thanks in advance
Alessandro

Answers

Try changing the +'s into *'s:
with my_tab as (select '163.24.27.167.U.BASE.F.I.4.3.10.837.1.34' otname from dual union all
                select 'fred.24.27.167.U.BASE.F.I.4.3.10.837.1.34' otname from dual union all
                select '163.24.27.167.33.BASE.F.I.4.3.10.837.1.34' otname from dual union all
                select '163.24.27.167' otname from dual)
select otname,
       regexp_substr(otname, '[[:alnum:]]*[^.]', 1, 1) a,
       regexp_substr(otname, '[[:alnum:]]*[^.]', 1, 2) b,
       regexp_substr(otname, '[[:alnum:]]*[^.]', 1, 3) c,
       regexp_substr(otname, '[[:alnum:]]*[^.]', 1, 4) d,
       regexp_substr(otname, '[[:alnum:]]*[^.]', 1, 5) e,
       regexp_substr(otname, '[[:alnum:]]*[^.]', 1, 6) f,
       regexp_substr(otname, '[[:alnum:]]*[^.]', 1, 7) g,
       regexp_substr(otname, '[[:alnum:]]*[^.]', 1, 8) h
from   my_tab;
OTNAME                                    A    B    C    D    E    F    G    H  
163.24.27.167.U.BASE.F.I.4.3.10.837.1.34  163  24   27   167  U    BASE F    I  
fred.24.27.167.U.BASE.F.I.4.3.10.837.1.34 fred 24   27   167  U    BASE F    I  
163.24.27.167.33.BASE.F.I.4.3.10.837.1.34 163  24   27   167  33   BASE F    I  
163.24.27.167                             163  24   27   167                    

Read other 13 answers

Tags:

Related Articles

  • SQL query problem - select max (case... aggregate function)October 11

    Hi, I have a problem with below sql query, it gives me problem/error message 'ORA-00937: not a single-group group function', why? select sag.afdeling, sag.sagsnr, to_char(sag.start_dato, 'yyyy-mm-dd'), sag.stat, BOGF_TRANS.TRANSTYPE, max (case when B

  • A challenging dynamic SQL query problemNovember 30

    hi All, I have a very interesting problem at work: We have this particular table defined as follows : CREATE TABLE sales_data ( sales_id NUMBER, sales_m01 NUMBER, sales_m02 NUMBER, sales_m03 NUMBER, sales_m04 NUMBER, sales_m05 NUMBER, sales_m06 NUMBE

  • PL/SQL function body returning SQL Query ProblemOctober 11

    I have wandered around the forums and found quite a bit of helpful information that has gotten me to the point I am now at. Unfortuntely, PL/SQL is not my strongest point and I am getting an error when I attempt to run my report. This is what I have

  • Date in sql query problemNovember 30

    Hi, I have a search form with 1 field (date picker) - the user chooses a date and a report based on a sql query is shown. I want the report to be based on only the month and year, and not the particular day. If there is a date picker (or something si

  • SQL query problem with sortingNovember 30

    Hi, I have question regarding sql query . Right now I am getting the results like this if i use this sql query select ID,Name,Desc,Priority from emp order by Priority ; Priority is varchar field. I don't want to change the Priority field and cannot a

  • SQL query problem using analytical/report functionOctober 11

    Hello there, I am having some problem writing my query. I have this table below:               create table t (priority number,                               plannedamount number,                               availablepieces number,                 

  • Report SQL query problemOctober 11

    Hi! I want to create a report with the script i copied at the bottom of this message. For some reason i get the following error: Error: Unable to describe SQL statement. Please correct it (WWV-13010) (WWV-) ORA-01001: invalid cursor (WWV-11230) ORA-0

  • Access SQL Query Problem - Pls helpOctober 11

    Hi, I'm connecting to an Access database and have a query which looks somehting like this: SELECT Accept.ACCEPT_NO,      Accept.CURRENCY,      Accept.INT_RATE,      Accept.F_AMOUNT,      Accept.DEALER,      Accept.MATURITY,      Accept.START_DATE,   

  • SQL Query Problem - Please HelpNovember 30

    Post Author: nmellick CA Forum: Data Connectivity and SQL Hello, I am trying to use a query in CR9 to pull the latest date of a DREXAM for each patient.  The patient can have one item or several items.  When I run this query, it only pulls back one r

  • Urgent Sql Query Problem - -Very UrgentNovember 30

    Hi Guys, I need a urgent solution for a problem.I am using the following query select ename from emp where deptno =10 Now I will declare a bind variable and if user passes 'A' then the query will run as it is and if he passes B then it should run the

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.565 s.