Syntax cannot be this hard. Basic question

[thendrickson]

Hope I do not sound like an idiot but I do not seem to be finding what I need.

I created a view in a 7.31 Informix database to summararize
certain columns.

I now need to select the data from that view using a where clause.

Select * from vifxView where scode = 'ABC-123'

I need to create a procedure that receives the input and returns in the close vicinity of 359 records (or fewer)

I need to call this from SQL server using a linked server.

Performance is horrible enough so joining to the view from within SQL is not a good option. Nor is selecting from the Informix view and filling a SQL Server temp table


I need to return only the records meeting the where clause.

Very simple in SQL Server (perhaps because I have used that extensively), but I am missing a basic concept with Informix I am afraid

 

[mberni]

hi,

do you ask for a sql-server procedure or for an informix-procedure?

in informix you could do something like that:

create procedure myproc()
returning varchar(255);

DEFINE myval varchar(255);

FOREACH
  select field1
    into myval
    from myview
   where <someconditions>  <<<<<<no semicolon here !!!>

   return myval WITH RESUME;

END FOREACH;

end procedure;

To call this in informix you would do:

execute procedure myproc();

But when you use odbc to call this procedure from external you will probably get "procedure returns too many values".

I am not familiar with sql-server, so i don't know what that would look like there.

Hope this helps.

 

[thendrickson]

I meant Informix. Sorry for any confusion.

I am a SQL server developer with good DBA skills, but Informix is stopping me dead.

I just am not finding the basics to build from.

I need to return multiple records into SQL server so I can join the sums of some fields in Informix to the employee/site/customer tables in SQL.

However, the problem is moot since I just found out the report I needed this procedure for is never used.

I will experiment with your example as soon as I fix my current "crisis" and post again.

Can you reccomend a good basic book for Informix? Something Like "Learn Informix in 4.7 minutes"?

Some place to start so I can ask a reasonable question?

 

[mberni]

maybe you should download a trial version from ibm and have a look into the included docs, they are very good.

I have looked around but couldnt find any docs for 7.31, but you should be fine even with the v10-docs, as long as you don't dive in too deep

if i can be of further help, just drop a thread here.

 

[thendrickson]

Thanks

I have found some information on 7.3 and am working on it.

I think I did find a solution to my (now moot) origional problem.

Yout answer was a big help if I understand correctly that I can declare the return value as a table

 

[mberni]

Yes, informix can return multiple values and rows (WITH RESUME) from a single procedure-call, but this is an extension to the ANSI-Standard and can cause problems when you leave native informix-environment. So possibly you cannot use that, if you connect from 3rd-party apps.

 

[thendrickson]

Thank you for your help.

I am swamped here with additional work, but will try to experiment a bit with this once I finish what must be done today.

Informix is a bit "unusual" in my eyes, but interesting. I was just hired for this position after several years contracting and the Informix is a legacy system that none of the current developers understand but which we cannot change right now without breaking a very important application

I do not quite understand why, but it seems I always get the work no one else knows anything about , so they give it to me.

At least I am not bored, but you will probably see me back with more 'dumb' questions

Again thanks for the help