Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: Sybase ASE query formating

    Hello Sybase Gurus!
    Please find the attachment of my issue. I'm new to SYBASE ASE 15. I've been using Oracle & SQL server. My recent project requires expertise in Sybase and I'm trying to do some basic formatting but having a hard time. If you read the attachment, "before" is my current data sets and "after" is what I'd like to make it. I can do this in Oracle but having a hard time in Sybase. the current workaround is creating proc and validate id, location_id fields, if it finds matching then display one id and one location_id and rest of the data would be as is. but this is getting really complex. I tried to do it in excel too but it removes the the entire row and that's not what I want. all I want is display blank for id and location_id if there is another identical data.
    Let me know if there is anything confusing.

    Thanks,
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2010
    Posts
    51
    Assuming your original table is "tt".

    Below is the code:

    Code:
    select * from tt
    go
    set nocount on
    go
    
    create table #temp2
    (
    id varchar(10) null,
    location_cd varchar(10) null,
    name char(20) null,
    value char(20) null
    )
    go
    declare @maxid int, @cur_id int
    
    select distinct loc_cod,id_col=identity(5) into #temp
    from tt
    
    select @maxid = max(id_col) from #temp
    select @cur_id = 1
    
    while (@cur_id <= @maxid)
    begin
    
            declare @minid int,@loc_cod int
    
            select @loc_cod = loc_cod from #temp where id_col=@cur_id
            
            select @minid = min(id_col) from tt
            where loc_cod = @loc_cod
            
            insert into #temp2
            select convert(varchar(10),@cur_id), convert(varchar(10),loc_cod), name, phone
            from tt where id_col = @minid
            
            insert into #temp2
            select "", "", name, phone
            from tt
            where id_col <> @minid
            and   loc_cod = @loc_cod
            
            select @cur_id = @cur_id + 1
    
    end
    
    
    select * from #temp2
    Run the batch directly. Do not run any query independently.

    Let us know how it goes.

    Thanks,
    Meet

  3. #3
    Join Date
    Jun 2010
    Posts
    51
    I have kept the original tables as following:

    Code:
    create table tt
    (
    id_col numeric(5) identity
    ,loc_cod int
    ,Name char(20)
    ,phone char(20)
    )
    Change it as you want. Remember to change that in the sql batch also.

  4. #4
    Join Date
    Apr 2009
    Posts
    3
    Meet,
    Thanks for your suggestion and sorry for the late response. As a matter of fact I've created a proc which basically does what you've suggested. I've 3 different sql (one create table, second insert, and third is proc), the proc basically change the display of the data. What I was thinking was I was writing too many codes to display something simple (in oracle I used 'Break On' location_id).
    thanks a lot for your suggestion

  5. #5
    Join Date
    Jun 2010
    Posts
    51
    There is no simple way of doing this in sybase (no inbulit function). You will have to rely on pure logic here . That's what great about sybase.

  6. #6
    Join Date
    Apr 2009
    Posts
    3
    Meet,
    I reviewed your code again and again and found that it is more simpler than mine. But I was also thinking something little different. See my original attachment one more time. Here is what I'm thinking:
    is there any easy update statement that I can use which will basically do the following:
    update location
    set location_id=''
    where location_id is greater than 1 (I mean to say greater than one occurance) and the second one is
    update location
    set id='' (knowing it's identity field)
    where location_id=''
    I think update will work quicker than the whole script. I tried your scritp and second part of the select statement produced more values
    Your thoughts will be appreciated.

    Thanks,

  7. #7
    Join Date
    Jun 2010
    Posts
    51
    Ok...I don't know how to write this in one update statements. Even if it's possible, it will be too complicated to understand and might end up giving you performance issues. Updating whole table at a time is never a good idea. As they always say...do it in small batches.

    As for your 2nd statement:
    I think update will work quicker than the whole script. I tried your scritp and second part of the select statement produced more values
    I am not sure what you mean here? Is the script not working as expected? Let us know with an example.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •