The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.
CREATE TABLE #tmp_tbl
          ( --line_num              integer DEFAULT AUTOINCREMENT,
          qualifier_state_id    integer
          , event_id              integer
          , group_id              integer
          , state_number          integer
          , qualifier_state_name  varchar(255)
          ) 
INSERT INTO #tmp_tbl (qualifier_state_id,event_id,group_id,state_number,qualifier_state_name)
VALUES ('62,58',1680,3,2,'BLUE,MEDIUM')

Now when someone try to insert into this table values such as below then IT should give error

INSERT INTO #tmp_tbl (qualifier_state_id,event_id,group_id,state_number,qualifier_state_name)
VALUES ('58,62',1680,3,2,'MEDIUM,BLUE')

so basically I need to check '62,58' is same as '58,62' what function i need to use for that? I tried LIST function in where clause but that did not work.

asked 16 Feb '13, 11:34

roadtrain64's gravatar image

roadtrain64
1366710
accept rate: 0%

edited 19 Feb '13, 10:52

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262


CREATE TABLE t
          ( qualifier_state_id    varchar(255) unique
          , event_id              integer
          , group_id              integer
          , state_number          integer
          , qualifier_state_name  varchar(255)
          );

CREATE PROCEDURE f 
          ( @qualifier_state_id    varchar(255)
          , @event_id              integer
          , @group_id              integer
          , @state_number          integer
          , @qualifier_state_name  varchar(255) )
BEGIN
INSERT t 
SELECT @qualifier_state_id, 
       @event_id,  
       @group_id,  
       @state_number,  
       @qualifier_state_name
 WHERE ( SELECT LIST ( row_value,
                       ','
                       ORDER BY row_value ) 
           FROM sa_split_list ( @qualifier_state_id, ',' ) ) 
NOT IN ( SELECT ( SELECT LIST ( row_value,
                                ','
                                ORDER BY row_value ) 
                    FROM sa_split_list ( t.qualifier_state_id, ',' ) )
           FROM t );
COMMIT;
END;

CALL f ('62,58',1680,3,2,'BLUE,MEDIUM');
CALL f ('58,62',1680,3,2,'MEDIUM,BLUE');
CALL f ('11,22',1234,5,6,'LARGE,RED');
CALL f ('22,11',1234,5,6,'LARGE,RED');

SELECT * FROM t ORDER BY qualifier_state_id;

qualifier_state_id,event_id,group_id,state_number,qualifier_state_name
'11,22',1234,5,6,'LARGE,RED'
'62,58',1680,3,2,'BLUE,MEDIUM'
permanent link

answered 17 Feb '13, 14:23

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

Thanks but i think thats too much complicated. I can do this, Here is my user table,

CREATE TABLE dba.t_retail_qualifier_state_list (qualifier_seq_id integer, event_id integer, qualifier_state_ids varchar(25), qualifier_state_names  varchar(255))
INSERT INTO  dba.t_retail_qualifier_state_list VALUES(1,1680, '55,62','MEDIUM,BLUE') 
SELECT * FROM dba.t_retail_qualifier_state_list

AND from SP input parameters I am creating a temp table where i have below data,

CREATE TABLE #tmp_tbl
          ( --line_num              integer DEFAULT AUTOINCREMENT,
          qualifier_state_id    varchar(30)
          , event_id              integer
          , group_id              integer
          , state_number          integer
          , qualifier_state_name  varchar(255)
          ) 
INSERT INTO #tmp_tbl (qualifier_state_id,event_id,group_id,state_number,qualifier_state_name)
VALUES ('62,55',1680,3,2,'BLUE,MEDIUM')
SELECT * FROM #tmp_tbl

Now I need to get qualifier_seq_id from dba.t_retail_qualifier_state_list table when the value already exists. So right now in dba.t_retail_qualifier_state_list , we have '55,62'. And my temp table has '62,55' which is same then get qualifier_seq_id from dba.t_retail_qualifier_state_list. So now only need to join 2 tables and get the value if exists.

SELECT a.qualifier_seq_id
         --INTO @lQualifierSeqId
         FROM dba.t_retail_qualifier_state_list  a
            , #tmp_tbl                           b
        WHERE a.event_id              = b.event_id
          AND LIST(a.qualifier_state_ids)   = LIST(b..qualifier_state_id) --But List doesnt work in where clause
          --AND a.qualifier_state_names = 'BLUE,MEDIUM'

permanent link

answered 18 Feb '13, 11:17

roadtrain64's gravatar image

roadtrain64
1366710
accept rate: 0%

edited 18 Feb '13, 11:55

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824

2

Much simpler... the "doesn't work" part is just a minor obstacle :)

LIST is an aggregate function, not a scalar function like sa_split_list.

LIST takes multiple values and combines them into a comma-separated list. The qualifier_state_id is ALREADY a comma-separated list so your REALLY need to break it apart (like sa_split_list) before combining it in a different order.

Perhaps, once you understand how LIST works, you will come up with a simpler solution that actually works... we all look forward to seeing it!

(18 Feb '13, 12:00) Breck Carter

Thanks a lot for the hints. I made the change in original sp where it was not sorting values to insert into temp table. So now no need to do anything but thanks a lot for all the help.

permanent link

answered 19 Feb '13, 12:09

roadtrain64's gravatar image

roadtrain64
1366710
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×12
×2

question asked: 16 Feb '13, 11:34

question was seen: 840 times

last updated: 19 Feb '13, 12:09