Home » SQL & PL/SQL » SQL & PL/SQL » Insert/Delete or do nothing (10.2.0.4.0)
Insert/Delete or do nothing [message #582473] Thu, 18 April 2013 11:52 Go to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
I might be making this more complex than I think but I was wondering if this scenario can be done using SQL or should I wright a PL/SQL procedure. I may need to either Delete, Insert or do nothing with data depending on what changes on a user form are done. I plan to pass a string of values in and compare them to what is saved in my "default" table already. Depending on the changes I may need to Delete, Insert, or do nothing to my "master" table.

"master" table
CREATE TABLE event_type_values 
(
event_seq    number not null,
event_id     number not null,
type_id number NOT NULL
);

INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 5);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 5);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 9);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 5);

INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 2, 5);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 2, 5);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 2, 9);

Rows can be inserted/delete from other procedures but I need to check only against the string vs "default" table for differences. That is where the fourth row(in insert statement) for event_id: 1 came from

"default" table
CREATE TABLE type_default_values 
(
type_id number NOT NULL
);

INSERT INTO type_default_values (type_id) VALUES (5);
INSERT INTO type_default_values (type_id) VALUES (5);
INSERT INTO type_default_values (type_id) VALUES (9);


Code to determine "differences" between "form" data and "default" values
WITH new_type_values AS
--This statement will split a row strings into individual pieces
--Ex. '1,1,5,9' will be split into three rows(row "1", row "1", row "5", row "9")
 (SELECT TRIM(substr(txt, instr(txt, ',', 1, LEVEL) + 1, instr(txt, ',', 1, LEVEL + 1) -
                      instr(txt, ',', 1, LEVEL) - 1)) new_type_id
    FROM (SELECT ',' || '1,1,5,9' || ',' txt FROM dual)
  CONNECT BY LEVEL <=
             length('1,1,5,9') - length(REPLACE('1,1,5,9', ',', '')) + 1)
SELECT new_data.type_id, nvl(new_data.cnt - old_data.cnt, new_data.cnt) cnt
  FROM (SELECT 'old' status, type_id, COUNT(*) cnt
          FROM type_default_values
         GROUP BY type_id) old_data,
       (SELECT 'new' status, type_id, COUNT(*) cnt
          FROM (SELECT to_number(new_type_id) type_id FROM new_type_values)
         GROUP BY type_id) new_data
 WHERE new_data.type_id = old_data.type_id(+)


Results
TYPE_ID	CNT
5	-1
9	0
1	2

--Pseudocode

--Delete "delta" between what might be in table based on "negative cnt(delete only 1 possible row)" value for every "event_id"
delete from event_type_values where type_id = 5 and "only delete at most one row"

--Insert into table for every "event_id" where there's a "positive cnt(insert only 2 rows)" value
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, "event_id", 1);

--Do nothing if "cnt" is zero


Can I write this using SQL or should I write a PL/SQL procedure??

I hope my question was asked sufficiently

[Updated on: Thu, 18 April 2013 12:20] by Moderator

Report message to a moderator

Re: Insert/Delete or do nothing [message #582476 is a reply to message #582473] Thu, 18 April 2013 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is unclear there is no "delta" column in your tables.

Regards
Michel

[Updated on: Thu, 18 April 2013 12:20]

Report message to a moderator

Re: Insert/Delete or do nothing [message #582478 is a reply to message #582476] Thu, 18 April 2013 12:32 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
I'll try one other thing. If this doesn't make sense than I apologize for wasting your time.

In lieu of doing the necessary delete/inserts in code. I simulated what the resulting table should look like

"Original" data
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 5);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 5);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 9);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 5);

INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 2, 5);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 2, 5);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 2, 9);



"new" data
truncate table event_type_values;

INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 5);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 9);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 5);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 1);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 1, 1);


INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 2, 5);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 2, 9);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 2, 1);
INSERT INTO event_type_values (event_seq, event_id, type_id) VALUES (event_type_values_SEQ.Nextval, 2, 1);


Where I
"deleted" one row from event_id = 1 where type_id = 5
"inserted" two rows for event_id = 1 where type_id = 1
"did nothing" for event_id = 1 where type_id = 9

"deleted" one row from event_id = 2 where type_id = 5
"inserted" two rows for event_id = 2 where type_id = 1
"did nothing" for event_id = 2 where type_id = 9
Re: Insert/Delete or do nothing [message #582480 is a reply to message #582476] Thu, 18 April 2013 12:34 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
Michel Cadot wrote on Thu, 18 April 2013 10:19
This is unclear there is no "delta" column in your tables.

Regards
Michel


The delta column would be the "count" column.
If it's negative, it's a delete for that many rows
If it's positive, it's an insert for that many rows
If it's zero, do nothing
Re: Insert/Delete or do nothing [message #582481 is a reply to message #582480] Thu, 18 April 2013 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which "count" column?

Regards
Michel
Re: Insert/Delete or do nothing [message #582482 is a reply to message #582478] Thu, 18 April 2013 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Where I...


What are the rules?

Regards
Michel

[Updated on: Thu, 18 April 2013 12:52]

Report message to a moderator

Re: Insert/Delete or do nothing [message #582483 is a reply to message #582482] Thu, 18 April 2013 13:01 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
I changed "count" to "delta" for you
WITH new_type_values AS
--This statement will split a row strings into individual pieces
--Ex. '1,1,5,9' will be split into three rows(row "1", row "1", row "5", row "9")
 (SELECT TRIM(substr(txt, instr(txt, ',', 1, LEVEL) + 1, instr(txt, ',', 1, LEVEL + 1) -
                      instr(txt, ',', 1, LEVEL) - 1)) new_type_id
    FROM (SELECT ',' || '1,1,5,9' || ',' txt FROM dual)
  CONNECT BY LEVEL <=
             length('1,1,5,9') - length(REPLACE('1,1,5,9', ',', '')) + 1)
SELECT new_data.type_id, nvl(new_data.cnt - old_data.cnt, new_data.cnt) delta
  FROM (SELECT 'old' status, type_id, COUNT(*) cnt
          FROM type_default_values
         GROUP BY type_id) old_data,
       (SELECT 'new' status, type_id, COUNT(*) cnt
          FROM (SELECT to_number(new_type_id) type_id FROM new_type_values)
         GROUP BY type_id) new_data
 WHERE new_data.type_id = old_data.type_id(+)


Results:
TYPE_ID	DELTA
5	-1
9	0
1	2


as far as rules, it's the Pseudocode I gave using the results above

The delta column would be the "count" column.
If it's negative, it's a delete for that many rows
If it's positive, it's an insert for that many rows
If it's zero, do nothing

Where I
"deleted" one row(delta column = -1) from event_id = 1 where type_id = 5(type_id column)
"inserted" two rows(delta column = 2) for event_id = 1 where type_id = 1(type_id column)
"did nothing" (delta column = 0) for event_id = 1 where type_id = 9(type_id column)

"deleted" one row(delta column = -1) from event_id = 2 where type_id = 5(type_id column)
"inserted" two rows(delta column = 2) for event_id = 2 where type_id = 1(type_id column)
"did nothing" (delta column = 0) for event_id = 2 where type_id = 9(type_id column)
Re: Insert/Delete or do nothing [message #582485 is a reply to message #582483] Thu, 18 April 2013 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The delta column would be the "count" column.


What is the rule to compute this "delta" or "count"?

Regards
Michel
Re: Insert/Delete or do nothing [message #582486 is a reply to message #582473] Thu, 18 April 2013 13:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
Registered: January 2010
Senior Member
Well, it is a bit complex but can be resolved with MERGE statement.

Before state:

SQL> select  *
  2    from  event_type_values
  3    order by event_id,
  4             event_seq
  5  /

 EVENT_SEQ   EVENT_ID    TYPE_ID
---------- ---------- ----------
         1          1          5
         2          1          5
         3          1          9
         4          1          5
         5          2          5
         6          2          5
         7          2          9

7 rows selected.


Merge:

merge
  into event_type_values x
  using (
         with data as (
                       select  '1,1,5,9' txt
                         from  dual
                      ),
             split as (
                       select  to_number(
                                         substr(
                                                txt,
                                                instr(
                                                      ',' || txt,
                                                      ',',
                                                      1,
                                                      level
                                                     ),
                                                instr(
                                                      txt || ',',
                                                      ',',
                                                      1,
                                                      level
                                                     ) - instr(
                                                               ',' || txt,
                                                               ',',
                                                               1,
                                                               level
                                                              )
                                               )
                                        ) type_id
                         from  data
                         connect by level <= length(txt) - length(replace(txt,',')) + 1
                      ),
              need as (
                       select  event_id,
                               type_id,
                               row_number() over(partition by event_id,type_id order by 1) rn
                         from  split,
                               (
                                select  distinct event_id
                                  from  event_type_values
                               )
                      ),
              have as (
                       select  rowid rid,
                               event_id,
                               type_id,
                               row_number() over(partition by event_id,type_id order by 1) rn
                         from  event_type_values
                      )
         select  a.rid,
                 nvl(a.event_id,b.event_id) event_id,
                 nvl(a.type_id,b.type_id) type_id,
                 case
                   when a.type_id is not null then 'delete'
                   else 'insert'
                 end flag
           from      have a
                 full join
                     need b
                   on (
                           b.event_id = a.event_id
                       and
                           b.type_id = a.type_id
                       and
                           b.rn = a.rn
                      )
           where a.type_id + b.type_id is null
        ) y
  on (
          y.rid = x.rowid
      and
          y.flag != 'insert'
     )
  when not matched
    then
      insert
        values(
               event_type_values_seq.nextval,
               y.event_id,
               y.type_id
              )
  when matched
    then
      update
         set event_seq = 0
         delete where event_seq = 0
/ 

7 rows merged.

SQL> select  *
  2    from  event_type_values
  3    order by event_id,
  4             event_seq
  5  /

 EVENT_SEQ   EVENT_ID    TYPE_ID
---------- ---------- ----------
         1          1          5
         3          1          9
        13          1          1
        14          1          1
         5          2          5
         7          2          9
        11          2          1
        12          2          1

8 rows selected.

SQL> 


SY.

[Updated on: Thu, 18 April 2013 14:07]

Report message to a moderator

Re: Insert/Delete or do nothing [message #582493 is a reply to message #582485] Thu, 18 April 2013 15:13 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
Michel Cadot wrote on Thu, 18 April 2013 11:31
Quote:
The delta column would be the "count" column.


What is the rule to compute this "delta" or "count"?

Regards
Michel


The rule for "delta" / "count" is you need to compare what is in the table
INSERT INTO type_default_values (type_id) VALUES (5);
INSERT INTO type_default_values (type_id) VALUES (5);
INSERT INTO type_default_values (type_id) VALUES (9);


Result:
TYPE_ID
5
5
9


with a new set of "type_default_values" that a user has adjusted on a form. represented from an input string
'1,1,5,9'


Result
TYPE_ID
1
1
5
9


What is the difference between the before and after image of "type" values. I need to make the table "event_type_values" get updated with the "delta's". I'll then update the table "type_default_values" with the after image.


Re: Insert/Delete or do nothing [message #582494 is a reply to message #582486] Thu, 18 April 2013 15:22 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
Solomon Yakobson wrote on Thu, 18 April 2013 11:51
Well, it is a bit complex but can be resolved with MERGE statement.

Before state:

SQL> select  *
  2    from  event_type_values
  3    order by event_id,
  4             event_seq
  5  /

 EVENT_SEQ   EVENT_ID    TYPE_ID
---------- ---------- ----------
         1          1          5
         2          1          5
         3          1          9
         4          1          5
         5          2          5
         6          2          5
         7          2          9

7 rows selected.




Solomon, thanks for the sql. I haven't tried it yet BUT shouldn't the result be more like

 EVENT_SEQ   EVENT_ID    TYPE_ID
---------- ---------- ----------
         1          1          5
         3          1          9
        13          1          1
        14          1          1
         5          2          5
         7          2          9
        11          2          1
        12          2          1
--another row
        15          1          5


because, we should only delete one row for each event that has a type_id of 5?? based upon the "delta" of before and after image??
Re: Insert/Delete or do nothing [message #582496 is a reply to message #582494] Thu, 18 April 2013 15:37 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
Maybe if the sql is as complex as Solomon suggests maybe I should just perhaps write a less efficient pl/sql routine with individual deletes/inserts within for-loops...
Re: Insert/Delete or do nothing [message #582547 is a reply to message #582494] Fri, 19 April 2013 06:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
Registered: January 2010
Senior Member
lott42 wrote on Thu, 18 April 2013 16:22
Solomon, thanks for the sql. I haven't tried it yet BUT shouldn't the result be more like


What means "like". You tell us what are the rules and what result should be. And you can't clearly define the rules. My assumptions. For each EVENT_ID compare TYPE_ID with ones listed in string. If it is present in both (same number of times) do nothing. If it is not present in EVENT_TYPE_VALUES table or present less times insert it as amy times as needed to match number of occurrences in string. If it is not present in string or present less times delete it from EVENT_TYPE_VALUES table as amy times as needed to match number of occurrences in string. Bottom line - we need to modify EVENT_TYPE_VALUES table so each EVENT_ID has nothing but TYPE_ID's listed in string (including number of occurrences). Based on the above:

1. String has the following TYPE_ID's 1,1,5,9
2. EVENT_ID 1 has the following TYPE_ID's 5,5,5,9
3. EVENT_ID 2 has the following TYPE_ID's 5,5,9

Therefore, for EVENT_ID 1 we have to insert TYPE_ID 1 twice and delete TYPE_ID 5 twice and leave TYPE_ID 9 unchanged.
for EVENT_ID 2 we have to insert TYPE_ID 1 twice and delete TYPE_ID 5 once and leave TYPE_ID 9 unchanged. And that's what code I posted does. If you have different rules in mind, please post detail explanation with examples. Now about SQL vs PL/SQL. I'd stay with SQL. Merge I posted is, as I already posted, is a bit complex but each view in WITH clause is simple enough and self-descriptive. And, in any case, PL/SQL will have pretty much same logic - just will execute same pieces used in MERGE separately.

SY.
Re: Insert/Delete or do nothing [message #582584 is a reply to message #582547] Fri, 19 April 2013 10:32 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
Solomon and Michel) thanks so much for looking at my question but please don't spend any more of your time on this question. I appreciate you taking the time to look into it initially though.

Solomon, I'll try specify my rules again


I have a string of values
TYPE_ID's 1,1,5,9


Compare those against a table(default_values) that also contain TYPE_ID
type_id	
5
5
9


Determine what is different between the string type_id values and the table(default_values) type_id values

TYPE_ID	DELTA
5	-1
9	0
1	2


Where type_id from STRING
5 is missing from the "default_values" table ONE time
9 matches exactly with the "default_values" table
1 occurs twice but is NOT in "default_values" table

Take these results and perform DML statements on table "event_type_values"
Need to take type_id: 5 and DELETE from table(event_type_values) for each event ONE time only
Do Nothing with type_id: 9 
Need to take type_id: 1 and INSERT into table(event_type_values) for each event TWO times only


Orig data "event_type_values"
event_seq	event_id	type_id
102		1		5
105		1		5
106		1		9
109		1		5

122		2		5
125		2		5
136		2		9




New Data "event_type_values"
event_seq	event_id	type_id
144		1		5
146		1		9
147		1		5
148		1		1
149		1		1

155		2		5
157		2		9
158		2		1
159		2		1
Re: Insert/Delete or do nothing [message #582591 is a reply to message #582584] Fri, 19 April 2013 14:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
Registered: January 2010
Senior Member
lott42 wrote on Fri, 19 April 2013 11:32
Solomon, I'll try specify my rules again


Still can be done with a single MERGE. Before state:

SQL> select  *
  2    from  event_type_values
  3    order by event_id,
  4             event_seq
  5  /

 EVENT_SEQ   EVENT_ID    TYPE_ID
---------- ---------- ----------
         1          1          5
         2          1          5
         3          1          9
         4          1          5
         5          2          5
         6          2          5
         7          2          9

7 rows selected.

SQL> select  *
  2    from  type_default_values
  3  /

   TYPE_ID
----------
         5
         5
         9

SQL>


Now:

merge
  into event_type_values x
  using (
         with data as (
                       select  '1,1,5,9' txt
                         from  dual
                      ),
             split as (
                       select  to_number(
                                         substr(
                                                txt,
                                                instr(
                                                      ',' || txt,
                                                      ',',
                                                      1,
                                                      level
                                                     ),
                                                instr(
                                                      txt || ',',
                                                      ',',
                                                      1,
                                                      level
                                                     ) - instr(
                                                               ',' || txt,
                                                               ',',
                                                               1,
                                                               level
                                                              )
                                               )
                                        ) type_id
                         from  data
                         connect by level <= length(txt) - length(replace(txt,',')) + 1
                      ),
       event_types as (
                       select  distinct event_id
                         from  event_type_values
                      ),
          split_rn as (
                       select  event_id,
                               type_id,
                               row_number() over(partition by event_id,type_id order by 1) rn
                         from  split,
                               event_types
                      ),
        default_rn as (
                       select  event_id,
                               type_id,
                               row_number() over(partition by event_id,type_id order by 1) rn
                         from  type_default_values,
                               event_types
                      ),
              need as (
                       select  nvl(a.event_id,b.event_id) event_id,
                               nvl(a.type_id,b.type_id) type_id,
                               case
                                 when a.type_id is not null then 'delete'
                                 else 'insert'
                               end flag,
                               row_number() over(partition by nvl(a.type_id,b.type_id) order by 1) rn
                         from      default_rn a
                               full join
                                   split_rn b
                                 on (
                                         b.event_id = a.event_id
                                     and
                                         b.type_id = a.type_id
                                     and
                                         b.rn = a.rn
                                    )
                         where a.type_id + b.type_id is null
                      ),
              have as (
                       select  rowid rid,
                               event_id,
                               type_id,
                               'delete' flag,
                               row_number() over(partition by event_id,type_id order by 1) rn
                         from  event_type_values
                      )
         select  a.rid,
                 nvl(a.event_id,b.event_id) event_id,
                 nvl(a.type_id,b.type_id) type_id,
                 case
                   when a.type_id is not null then 'delete'
                   else 'insert'
                 end flag
           from      have a
                 full join
                     need b
                   on (
                           b.event_id = a.event_id
                       and
                           b.type_id = a.type_id
                       and
                           b.rn = a.rn
                       and
                           b.flag = a.flag
                      )
           where b.flag = 'insert'
              or a.type_id + b.type_id is not null
        ) y
  on (
          y.rid = x.rowid
      and
          y.flag != 'insert'
     )
  when not matched
    then
      insert
        values(
               event_type_values_seq.nextval,
               y.event_id,
               y.type_id
              )
  when matched
    then
      update
         set event_seq = 0
         delete where event_seq = 0
/

6 rows merged.

SQL> select  *
  2    from  event_type_values
  3    order by event_id,
  4             event_seq
  5  /

 EVENT_SEQ   EVENT_ID    TYPE_ID
---------- ---------- ----------
         1          1          5
         3          1          9
         4          1          5
        10          1          1
        11          1          1
         6          2          5
         7          2          9
        12          2          1
        13          2          1

9 rows selected.

SQL> 


SY.
Re: Insert/Delete or do nothing [message #583756 is a reply to message #582473] Fri, 03 May 2013 10:57 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
Sorry to bother but this is just a quick question on splitting of "input" values that is used here.

Part of the original question...
WITH new_type_values AS
--This statement will split a row strings into individual pieces
--Ex. '1,1,5,9' will be split into three rows(row "1", row "1", row "5", row "9")
 (SELECT TRIM(substr(txt, instr(txt, ',', 1, LEVEL) + 1, instr(txt, ',', 1, LEVEL + 1) -
                      instr(txt, ',', 1, LEVEL) - 1)) new_type_id
    FROM (SELECT ',' || '1,1,5,9' || ',' txt FROM dual)
  CONNECT BY LEVEL <=
             length('1,1,5,9') - length(REPLACE('1,1,5,9', ',', '')) + 1)


What if a user passes nothing in the "input" string
SELECT *
  FROM (SELECT TRIM(substr(txt, instr(txt, ',', 1, LEVEL) + 1, instr(txt, ',', 1, LEVEL + 1) -
                            instr(txt, ',', 1, LEVEL) - 1)) new_type_id
          FROM (SELECT ',' || '' || ',' txt FROM dual)
        CONNECT BY LEVEL <= length('') - length(REPLACE('', ',', '')) + 1);


The result is one row of "null" value.
NEW_TYPE_ID
"null"	


I WANT to pass back "no data" as my result
SELECT *
  FROM (SELECT TRIM(substr(txt, instr(txt, ',', 1, LEVEL) + 1, instr(txt, ',', 1, LEVEL + 1) -
                            instr(txt, ',', 1, LEVEL) - 1)) new_type_id
          FROM (SELECT nvl2('', ',' || '' || ',', '') txt FROM dual)
        CONNECT BY LEVEL <= length('') - length(REPLACE('', ',', '')) + 1)
 WHERE new_type_id IS NOT NULL;


The result is null.
NEW_TYPE_ID
"no data found"	


Obviously, this does want I want but is there another way such as
SELECT *
  FROM (SELECT TRIM(substr(txt, instr(txt, ',', 1, LEVEL) + 1, instr(txt, ',', 1, LEVEL + 1) -
                            instr(txt, ',', 1, LEVEL) - 1)) new_type_id
          FROM (SELECT nvl2('', ',' || '' || ',', '') txt FROM dual)
        CONNECT BY LEVEL <= length('') - length(REPLACE('', ',', '')) + 1)
;

Re: Insert/Delete or do nothing [message #583760 is a reply to message #583756] Fri, 03 May 2013 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand you have a query and you want another one?
But what another one? What difference do you want?
What is the problem with the first query?

Regards
Michel
Re: Insert/Delete or do nothing [message #583764 is a reply to message #583760] Fri, 03 May 2013 12:39 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
I thought I stated what I wanted. Just reading the text part of my question:
1. Part of the original question...
2. What if a user passes nothing in the "input" string
3. The result is one row of "null" value.
4. I WANT to pass back "no data" as my result
5. Obviously, this does want I want but is there another way such as

I want this code

SELECT *
  FROM (SELECT TRIM(substr(txt, instr(txt, ',', 1, LEVEL) + 1, instr(txt, ',', 1, LEVEL + 1) -
                            instr(txt, ',', 1, LEVEL) - 1)) new_type_id
          FROM (SELECT ',' || '' || ',' txt FROM dual)
        CONNECT BY LEVEL <= length('') - length(REPLACE('', ',', '')) + 1);


to return what this code does
SELECT *
  FROM (SELECT TRIM(substr(txt, instr(txt, ',', 1, LEVEL) + 1, instr(txt, ',', 1, LEVEL + 1) -
                            instr(txt, ',', 1, LEVEL) - 1)) new_type_id
          FROM (SELECT nvl2('', ',' || '' || ',', '') txt FROM dual)
        CONNECT BY LEVEL <= length('') - length(REPLACE('', ',', '')) + 1)
 WHERE new_type_id IS NOT NULL;


BUT you CAN'T use
WHERE new_type_id IS NOT NULL


How would you do it??


Re: Insert/Delete or do nothing [message #583766 is a reply to message #583764] Fri, 03 May 2013 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
BUT you CAN'T use


Why?

Regards
Michel
Re: Insert/Delete or do nothing [message #583769 is a reply to message #583766] Fri, 03 May 2013 12:51 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
Just curious.

I was wondering if modifying the "inner" select would give me the same result

Ex. (doesn't do what I want)
SELECT *
  FROM (SELECT TRIM(substr(txt, instr(txt, ',', 1, LEVEL) + 1, instr(txt, ',', 1, LEVEL + 1) -
                            instr(txt, ',', 1, LEVEL) - 1)) new_type_id
          FROM (SELECT nvl2('', ',' || '' || ',', '') txt FROM dual)
        CONNECT BY LEVEL <= length('') - length(REPLACE('', ',', '')) + 1)


Re: Insert/Delete or do nothing [message #583770 is a reply to message #583769] Fri, 03 May 2013 13:08 Go to previous message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Just curious.


OK, so search and give us the result.

Regards
Michel
Previous Topic: SP2-0027: Input is too long (> 2499 characters)
Next Topic: merge two tables in to one and knock out the duplicate records.
Goto Forum:
  


Current Time: Sun Dec 21 06:19:42 CST 2014

Total time taken to generate the page: 0.09715 seconds