Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2015
    Posts
    1

    Unanswered: sybase For loop for Querying , every result got from the select statem

    i am trying to run this Query , this will produce a output of numerous account numbers .

    SELECT distinct(a.acct_num)
    FROM customer_acct a,
    customer_acct_history b LIKE "%000%"
    WHERE a.acct_num *= b.acct_num AND acct_type='C'

    For every account number returned , by the above query . i am trying to run an another query consisitng of various joins , group by and also order by and selecting the Top 1 out put of that Query . i need to put this in a Loop so that for every account number the 2nd query is Ran and top 1 value is selected and stored in a Temp table . any help to proceed would be much appreciated

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    Hi,

    Consider the use of a cursor.

    An example can be found here.

    http://infocenter.sybase.com/help/in...ses/X61512.htm

    --------------------------------------------------

    declare c_account cursor for
    SELECT distinct(a.acct_num)
    FROM customer_acct a,
    customer_acct_history b LIKE "%000%"
    WHERE a.acct_num *= b.acct_num AND acct_type='C'

    @acc_num integer -- or whatever the column type is

    begin

    create table #foo ( col1 int, col2 varchar(255), col3 datetime)

    open c_account

    fetch c_account into @acc_num

    while @@sqlstatus = 0
    begin

    insert into #foo (col1, col2, col3)
    select top 1
    col1, col2, col3
    from
    other_table ot
    where
    ot.col = @acc_num

    fetch c_account into @acc_num
    end

    close c_account

    deallocate c_account

    end
    go

    Hope that helps, but I suspect this could be faster if you do this as a series of joins.

Tags for this Thread

Posting Permissions

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