Reply to: SQL Statement Help

[Spacecadet]

Yeah yeah. I wonder why this sort of work has gone to India......

cause you're too dim to do it?

[doodab]

I wonder why this sort of work has gone to India......

Because you charge too much?

[DimPrawn]

even though it's been a few years since I did any serious Oracle, that SQL it makes perfect sense to me

Of course, you shouldn't let inexperienced coders near Big Co. data mangling process

Yeah yeah. I wonder why this sort of work has gone to India......

[doodab]

It is a bit fugly isn't it

[Spacecadet]

Jeez that looks horrific. The sort of thing you find with no comments in a stored proc named usp_do_work which is critical to some Big Co. data mangling process which worked for 6 months but now fails completely.

Please, don't use SQL for this sort of thing.

even though it's been a few years since I did any serious Oracle, that SQL it makes perfect sense to me

Of course, you shouldn't let inexperienced coders near Big Co. data mangling process

[DimPrawn]

select substr(dataset, 1, instr(dataset, chr(10)))
      ,substr(dataset, 1, instr(dataset, '|') - 1) tmpVal2
      ,substr(dataset, instr(dataset, '|') + 1
             , instr(dataset, '|', 1, 2)
             - instr(dataset,'|', 1, 1) - 1) tmpVal3
      ,substr(dataset, instr(dataset, '|', 1, 2) + 1
             ,instr(dataset, '|', 1, 3)
             -instr(dataset, '|', 1, 2) - 1) tmpVal4 
      ,nvl(substr(dataset, instr(DataSet, '|', 1, 3) + 1
             ,instr(dataset, chr(10), 1, 1)
             -instr(dataset, '|', 1, 3) - 1), substr(dataset, instr(DataSet, '|', 1, 3) + 1)) tmpVal5
      , substr(dataset, instr(dataset, chr(10)))  TmpVal6          
from (select 'first|12345|version745|99985
second|23456|ver666|68452
third|3456_00|v9864|7777' as dataset from dual)

.

Jeez that looks horrific. The sort of thing you find with no comments in a stored proc named usp_do_work which is critical to some Big Co. data mangling process which worked for 6 months but now fails completely.

Please, don't use SQL for this sort of thing.

[Spacecadet]

in SQL Server you can use XML to solve this:

declare @dataset varchar(1000)
declare @dataxml xml
 
set @dataset ='first|12345|version745|99985
second|23456|ver666|68452
third|3456_00|v9864|7777'
 
select @dataxml =cast('<dataset><row><data>'+replace(replace(replace(@dataset,char(13),''),'|','</data><data>'),CHAR(10),'</data></row><row><data>')+'</data></row></dataset>'as xml)
 
select dataset.a.value('data[1]','varchar(100)')as data1,
         dataset.a.value('data[2]','varchar(100)')as data2,
         dataset.a.value('data[3]','varchar(100)')as data3,
         dataset.a.value('data[4]','varchar(100)')as data4
from @dataxml.nodes('dataset/row')as dataset(a)

as long as the number of columns is static, you're fine. It'll handle any number of rows.

There should be similar functionality in Oracle

[doodab]

select substr(dataset, 1, instr(dataset, chr(10)))
      ,substr(dataset, 1, instr(dataset, '|') - 1) tmpVal2
      ,substr(dataset, instr(dataset, '|') + 1
             , instr(dataset, '|', 1, 2)
             - instr(dataset,'|', 1, 1) - 1) tmpVal3
      ,substr(dataset, instr(dataset, '|', 1, 2) + 1
             ,instr(dataset, '|', 1, 3)
             -instr(dataset, '|', 1, 2) - 1) tmpVal4 
      ,nvl(substr(dataset, instr(DataSet, '|', 1, 3) + 1
             ,instr(dataset, chr(10), 1, 1)
             -instr(dataset, '|', 1, 3) - 1), substr(dataset, instr(DataSet, '|', 1, 3) + 1)) tmpVal5
      , substr(dataset, instr(dataset, chr(10)))  TmpVal6          
from (select 'first|12345|version745|99985
second|23456|ver666|68452
third|3456_00|v9864|7777' as dataset from dual)

You might need to do a bit of munging to ensure that chr(10) is the only line seperator if you have some 13s in there as well.

The nvl in the tmpval5 part is to cope with the case that there is no newline on the last line BTW.

[ettubrute]

Try the following:

SELECT REGEXP_SUBSTR(DataSet, '[^:cntrl:]+', 1, 1) tmpVal1
     , REGEXP_SUBSTR(DataSet, '[^|]+', 1, 1) tmpVal2
     , REGEXP_SUBSTR(DataSet, '[^|]+', 1, 2) tmpVal3
     , REGEXP_SUBSTR(DataSet, '[^|]+', 1, 3) tmpVal4
     , REGEXP_SUBSTR(DataSet, '[^|]+', 1, 4) tmpVal5
     , REGEXP_SUBSTR(DataSet, '[^:cntrl:]+', 1, 2) tmpVal6
 FROM (SELECT DataSet aString FROM DUAL)

I'm not sure whether the [:cntrl:] will work, since I can't test it, but the splitting of fields works! If you can post the surrounding code, it might be easier to write something that will work better. Problem being that I do not know how DataSet actually looks like... which you could find out by doing a "select dump(DataSet) ...", which will show you what the control characters are that split the lines.

[chef]

ok, i'm going to start burying myself in REGEXP_INSTR research..

thanks once again to all that have contributed, i very much appreciate your help

i'll check back tomorrow morning on here at 7am european time or 6 uk time to see if there's any other pointers but Im now confident i'll get it sorted with some more research..

Chef

[chef]

Ok, clear for now. Next question: Is this to be done in a PL/SQL block or function, or is it an embedded SQL statement somewhere? Thus, what loop mechanism do you (intend to) use?

If you can call a PL/SQL function, the solution might be easy: call the function with the dataset as a parameter, and use the function to create a temporary table with the separate rows, then us a standard SQL loop to read the table and push to the webservice. Sounds like a possibility?

yes, yes and yes, that's exactly what i thought would be the solution, a function that outputs parameters using the dataset as the main input parameter. i can use either an embedded SQL statement or a function or a combination of both as far as I know, my issue is getting the SQL to take an input of the dataset and output 6 tmp variables, if the loop is done within the sql then i can use the fact that field variables change value as the trigger to extract each line or if looping is too difficult via the SQL statement/function then i can force a loop via the external system and simply use the output of the statement/function to extract the variables that way. The extracting the varibles once they've been set is not a major problem is getting them to the point where they contain the correct parsed values that im struggling with.

EDIT: oh and welcome to CUK ettubrute if no-one else has said so so far..

[ettubrute]

Ok, clear for now. Next question: Is this to be done in a PL/SQL block or function, or is it an embedded SQL statement somewhere? Thus, what loop mechanism do you (intend to) use?

If you can call a PL/SQL function, the solution might be easy: call the function with the dataset as a parameter, and use the function to create a temporary table with the separate rows, then us a standard SQL loop to read the table and push to the webservice. Sounds like a possibility?

[chef]

no not a stupid question in the slightest.

essentially the form that contains this data is a delta form, its the result of a webservice push of data from an external source.

essentially:

-external source sends data to Delta form
-delta form stores 1 group info with a Dataset field listing all products
-** SQL magic happens to parse Dataset into individual lines and 6 tmp variable fields one line per loop***
-external helpdesk system runs the SQL query/function above in a loop until -dataset is null, extracting on each loop a single line of product and product version info (stored in the tmp variable fields)
-external helpdesk system then pushes the info extracted on each loop to a seperate table/form to record information on a group/product level resulting in x amount of records where x equals the number of lines in the DataSet


My problem is i need to seperate out the products aka the DataSet field into individual lines/elements so that I can push them to a seperate form on a group/product basis

it was agreed before hand not to send x messgaes, one for each group/product combination where x equals the number of line sin the dataset as this may result in a very large number of webservice calls and also there is the issue of tracking to ensure all messages are received. so changing the way i receive data is essentially out of hte question, it comes in the relationship of 1 message per group, each message contains x number of product rows in a field called dataset which need to be extracted to form the Group/Product relationships.

[ettubrute]

Hi Chef, maybe I'm stupid, but... How does your query get the data? At a complete block of text, or are you reading it from a file, or...

[chef]

be good if you could pop the complete code up chef, certainly help me along

umm that is the complete code as far as I have it, the looping through and pushing the variables in other forms is done via an external app called BMC Remedy Administrator tool and essentially makes SQL calls to extract the information required

EDIT: essentially im looking for either a SQL query or a function that will result in 6 tmp variables that i can then re-run to slowly extract whatever is in the DataSet field, it's so frustrating knowing what i want but not having the skills to do it or the time to learn it.. if I can answer any q's i'll try my best, again, i appreciate your patience