Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

can some one help with this logic

i have 2 tables state and data like this

state
state_id    final
1
2
3
4

data
state_id       data
1               ca
1               la
2                ga
4                chi
4                il
4                lv

I want state table updated like this

state_id    final
1            ca/la
2             ga
3             null
4          chi/il/lv

I want the query to be be compatible with sql server 2000
thanks
0
vijay11
Asked:
vijay11
  • 5
  • 3
  • 2
  • +1
1 Solution
 
dj_alikCommented:
you can declare user defined function with  state_id    input parameter that will return
concatenateed values
and use this function in select statements
0
 
Kevin CrossChief Technology OfficerCommented:
Hi.

You can try this XML trick that should work in SQL 2000 as well if my memory serves me correctly.

Test that values are coming back correctly using this:
SELECT [state_id]
     , STUFF((SELECT '/' + [data] 
        FROM [data] 
        WHERE [data].[state_id] = [state].[state_id]
        FOR XML PATH('')), 1, 1, '') AS [final]
FROM [state]
;

Open in new window


Then change to an update like this:
UPDATE [state]
SET [final] = STUFF((SELECT '/' + [data] 
        FROM [data] 
        WHERE [data].[state_id] = [state].[state_id]
        FOR XML PATH('')), 1, 1, '')
;

Open in new window


Hope that helps!
0
 
dj_alikCommented:
STUFF function is not for SQL 2000
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
vijay11Author Commented:
I cannot use function , Can this be done using joins .

Thanks
0
 
Kevin CrossChief Technology OfficerCommented:
Good catch.  Substitute in SUBSTRING() for STUFF().

UPDATE [state]
SET [final] = SUBSTRING((SELECT '/' + [data] 
        FROM [data] 
        WHERE [data].[state_id] = [state].[state_id]
        FOR XML PATH('')), 2, 8000)
;

Open in new window

0
 
vijay11Author Commented:
Guys Thanks for the posts,but XML is not working In Sybase ,It was my fault I didnt mention that it should be compatible ith Sybase also

Sorry

Can this be done using joins or sub queries
0
 
vijay11Author Commented:
I was using a function in sql server 2005 for this logic , but for sybase i used cursor which decreased the performance of query. So I want to re write this query
0
 
Kevin CrossChief Technology OfficerCommented:
The methods showed can work across SQL 2000 and newer versions of MS SQL.  Don't know about Sybase, sorry.
You should probably add the Sybase zone to your question to help answer that bit.
0
 
Alpesh PatelAssistant ConsultantCommented:
CREATE FUNCTION ConcatenateString(@Product VARCHAR(10))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Output VARCHAR(100)

SELECT @Output = ISNULL(@Output, '') + Colors + '|'
FROM YourTable
WHERE Product = @Product

RETURN @Output
END
GO

SELECT Product, dbo.ConcatenateString(Product) AS Colors
FROM YourTable
go
0
 
Kevin CrossChief Technology OfficerCommented:
@vijay11: I asked a friendly Zone Advisor to add 'Sybase' to question's topic area listings and I see you added it as a tag.  Hopefully that will help attract some of those Experts, but if not you may consider using Request Attention and ask Moderator to send out alerts on your behalf.  It may be helpful to post the version of Sybase.

@PatelAlpesh, please note that the user defined function approach was mentioned as the first comment and already discussed by the Asker as not viable in this instance.  The Asker really needs some assistance with finding the most generic SQL syntax possible as it appears the code must be portable to MS SQL of various versions and Sybase.
0
 
Kevin CrossChief Technology OfficerCommented:
In your other question, you posted this as the SQL you are using in MS SQL and Sybase.
declare @state_id int , @num varchar(100), @all varchar(2000)

                  declare cur_state cursor for
                  select state_id
                  from state

                    open cur_state
                  while (1=1)
                  begin
                        fetch cur_inst into @inst_id
                        if @@fetch_status<>0 break


                        declare cur_acct cursor for
                        select ltrim(rtrim(data))
                        from view_values v
                        where v.type='P'
                        and v.status='A'
                        and v.name='Acc'
                        and v.state_id=@state_id

                        select @all =''
                        open cur_acct
                        while (1=1)
                        begin
                              fetch cur_acct into @num
                              if @@fetch_status <> 0 break
                
                              select @all = @all + ' / '  + @num
                        end
                        close cur_acct
                        deallocate cur_acct

                        if @all <> '' 
                        begin
                              
                              select @all = substring(@all, 4,len(@all) - 3) 

                              update country 
                              set acct_no = @all
                              where state_id=@state_id
                        end
                    
                  end
                  close cur_state
                  deallocate cur_state
            end

Open in new window


If that is true, then it is safe to say that the concatenation operation is the same and that multi-line SQL is an option.  I think when I first started posting, especially after your indication of no functions, that you needed a single SQL statement.  If that is not the case, you can consider doing this iteratively using a similar approach to what you would use in the user defined function method.

See if this works in Sybase:
declare @state_id int, @all varchar(2000);

-- grab the smallest state id value first.
select @state_id = min(state_id)
from state;

-- while you have a valid state id, keep iterating.
while(@state_id is not null)
begin
   -- reset @all for each run.
   set @all = null;

   -- build delimited string per current state id.
   -- coalesce and concatenation should work in Sybase.
   select @all = coalesce(@all+'/', '') + ltrim(rtrim(data))
   from view_values v
   where v.type='P'
   and v.status='A'
   and v.name='Acc'
   and v.state_id=@state_id;

   -- update country table using delimited string and state id.
   update country
   set acct_no = @all
   where state_id=@state_id;

   -- grab next state id.
   select @state_id = min(state_id)
   from state
   where state_id > @state_id;
end

Open in new window


I don't have Sybase to verify, but am willing to try to help until a Sybase Expert comes about depending on you to test out SQL syntax to see what works and doesn't work.  ISNULL() is an option to try, but given COALESCE() is ANSI standard SQL -- there is a greater likelihood it will work than ISNULL().

Hope that helps!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now