Sybase query iteration

Question

I have populated data in a temp table ( the table will always have 10 records max). The next step I am trying is to pick up records one by one and query the database to test some condition and assign a comment based on the outcome of the query. For example:

-- table: #XY (data)

select data from #XY

data 
----
AB1
AB2
AB3

I'm trying to write a sybase query which will pick up the data one by one ( recursively ?), run some designated query and return a pass or fail comment which I will then finally populate in another temp table

eg: Row 1 is AB1 - Select name from table where name = 'AB1' if it doesn't return data , I stop there and populate a comment

if data is returned , I go to step 2 select name from table 2 where name = 'AB1' and so on

I need to do for all records and capture the results and put in a final temp table .

The o/p I perceive will be something like this

Name  Cause Descricption
----- ------------------
AB1   XXXX
AB2   YYYY

I have done such stuff in shell script ( reading lines from a file one by one and actioning them ) but not sure if that's something that can be done in sybase as well

Any alternate approach welcome as well

Thanks in advance .

are you trying this in SQL or you have a client application written in some C/C++/Java/Python/Perl/Ruby/etc...? — Igor, Feb 27 '18 at 15:26
@Newbie, you will need to create some stored procedure on the server and do it there. It is impossible to make 1 SQL query that will do what you want. Is this the question and you just need a syntax of how to do that? — Igor, Feb 27 '18 at 18:46
which Sybase product (ASE? IQ? SQLAnywhere? Advantage?) and version? batch SQL or a stored proc? for looping through the rows in a table you're likely looking at a solution based on a cursor; also, how do you know which table to query for which data value ... will that be hard-coded in your SQL, or is the table name derived (somehow) from the contents of #XY? — markp-fuso, Feb 28 '18 at 01:35
@markp its Sybase ASE 15.7 . I’m looking for a batch sql . The idea is to pick up the values one by one from the temp table #XY and iterate through a defined set of tables ( table name hardcoded yes ) — Newbie, Mar 01 '18 at 21:21

score 0 · Answer 1 · answered Mar 01 '18 at 21:38

As a batch script in Sybase ASE, a rough outline:

declare datacur cursor
as
select data from #XY
for read only
go

declare @data varchar(30)

open datacur

fetch datacur into @data

while @@sqlstatus = 0
begin

    -- add code that you wish to apply against a given @data value

    fetch datacur into @data
end

close datacur
go

deallocate cursor datacur
go

Sybase query iteration

1 Answers1