Must be Sunday and I have a mental block.

I'm trying to display some fields from a table and the result columns from a
SP (which needs the ID from the main table).

Something like this:

Select p.ID, p.Name, t.Status, t.Status
from Products p, spProductStatus(p.ID) t


I have tried many different ways without result ... any ideas?

BTW, I'm using 9.x

Is there an error message you're getting? Or just no data?

--
Paul Horan[TeamSybase]
Cynergy Systems
www.cynergysystems.com

"Jan K. van Dalen" <janATjanvandalen.com> wrote

I get ... Invalid parameter. Here is my code (I forgot to mention that my
sp returns 2 values) ... btw, my SP works fine on its own. ... this is my
last try:

=========================
select c.CaseCoverageID, "XER".spCaseActivityStatus (c.CaseCoverageID, 55)
from "XER".CaseCoverage c
=========================
ALTER PROCEDURE "XER"."spCaseActivityStatus" ( In PassedCaseCoverageID Int,
In PassedActivityID Int )
RESULT( Status Varchar(20) , StatusDate Date)
BEGIN
Select CaseActivity.Status, CaseActivity.StatusDate from CaseActivity where
CaseActivity.CaseCoverageID = PassedCaseCoverageID and
CaseActivity.ActivityID = PassedActivityID
END
=========================

BTW, they are both created by the same user ...

"Paul Horan[TeamSybase]" <paul.horan (AT) NOSPAM_cynergysystems (DOT) com> wrote in
message news:44551129$1 (AT) forums-2-dub (DOT) ..

I cannot resist copying and pasting from Section 3.8 of my book,
LATERAL Procedure Call, because it starts off with a *direct answer*
to your question:

=====
If a column from another table is passed as an argument to a procedure
reference in a FROM clause, that procedure reference must appear as
part of a LATERAL derived table definition. Also, the other table must
appear ahead of the LATERAL derived table definition, and be separated
from it by a comma rather than one of the join operators like INNER
JOIN. This is a situation where the "comma join operator" must be
used, and the ON condition cannot be used.
Here is the general syntax for a LATERAL derived table:

<lateral_derived_table>
::= LATERAL
<subquery>
[ AS ] <correlation_name>
[ <derived_column_name_list> ]
[ AS ] <correlation_name>
[ <derived_column_name_list> ]

Here is the simplified syntax for a join between a table and a
procedure reference where a column from that table is passed as an
argument; this is the only use of the comma join and the LATERAL
keyword that is discussed in this book:

<typical_lateral_procedure_call>
::= <table_name> ","
LATERAL "(" <procedure_name>
"(" <table_name>.<column_name> ")" ")"
AS <correlation_name>
=====

So, your query should look like this (caveat emptor: I have not tested
this)...

Select p.ID, p.Name, t.Status, t.Status
from Products p, LATERAL ( spProductStatus(p.ID) ) t

Breck


On 30 Apr 2006 10:40:05 -0700, "Jan K. van Dalen"
<janATjanvandalen.com> wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

Breck, that did it ... many thanks ... What Book? Link to Amazon?

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter (AT) risingroad (DOT) com> wrote in
message news:j55a52lph4am3nfs25mirr17b2ghqoopv9 (AT) 4ax (DOT) com...

Found it ...
http://www.amazon.com/gp/product/155...g=UTF8&s=books

Thanks, will get it asap.

"Jan K. van Dalen" <janATjanvandalen.com> wrote