computed field syntax

Hello:
I am trying to set the value of a computed field in a stored procedure to
the value returned by another stored procedure but can't seem to find the
proper syntax:
CREATE PROCEDURE PlanListGet // syntax invalid
AS
SELECT
Plans.PlanID,
Plans.[Name],
Plans.[Description],
IsPlanEstablished = EXEC PlanIsEstablished PlanID
FROM Plans
In the above code, IsPlanEstablished is the computed field,
PlanIsEstablished is a stored procedure that returns an integer value, and
PlanID is a parameter for the PlanIsEstablished stored procedure.
Any suggestions?
Thanks!
ChrisYOu cant do that in a select but you can retrieve the Value to store it in
a temptable an retrieve this from that.
alte Procedure Testint
(
@.Valuetopass int
)
AS
SELECT @.Valuetopass*5
CREATE TABLE #TempTable
(
ValueToReturn INT
)
INSERT INTO #TempTable
EXEC Testint 5
SELECT * from #TempTable
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ChrisB" <pleasereplytogroup@.thanks.com> schrieb im Newsbeitrag
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
> the value returned by another stored procedure but can't seem to find the
> proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field,
> PlanIsEstablished is a stored procedure that returns an integer value, and
> PlanID is a parameter for the PlanIsEstablished stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>|||You can't use a stored procedure as an expression for a computed column. Per
haps you can convert the
proc into a user defined scalar function?
CREATE FUNCTION f() RETURNS INT AS BEGIN RETURN 1 END
GO
CREATE TABLE t(c1 int, c2 AS dbo.f())
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
the value returned by
> another stored procedure but can't seem to find the proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field, PlanIsEstablis
hed is a stored
> procedure that returns an integer value, and PlanID is a parameter for the
PlanIsEstablished
> stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>|||Looks like I'll have to take a different approach.
Thanks for the input!
Chris
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
> the value returned by another stored procedure but can't seem to find the
> proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field,
> PlanIsEstablished is a stored procedure that returns an integer value, and
> PlanID is a parameter for the PlanIsEstablished stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>

Complex query problem

Hello!

I have a query that queries two tables (including a self join on one of them) and returns a result set that almost (but not quite) gives me what I want , and was wondering if someone could give me some pointers. Rather than show the whole query (complex), I'll show the result set and describe what i want:

Tab1.efID Tab1.VID Tab2.efID Tab2.VID
$00046342 7 $00046342 8
$00046342 7 $00046342 19
$00046342 18 $00046342 19

I want to amend the query so that it returns a count of the distinct rows of Tab1.efID,Tab1.VID - from the above result set, it should return just a count of the first and third rows, i.e 2

The statement SELECT DISTINCT Tab1.efID,Tab1.VID would return the two rows, but obviously SELECT COUNT(DISTINCT Tab1.efID,Tab1.VID) doesn't work.

SELECT COUNT(DISTINCT Tab1.efID + CAST(Tab1.VID AS VARCHAR(2))) does work, but i thought perhaps there may be a more elegant solution - anyone have any pointers?

Cheers
GregSee your other thread (http://www.dbforums.com/t1008216.html) asking this question.

-PatP

complex query / advice needed

Hello!

I'm seeking advice on a rather complex type of query I need to build
in an Access ADP (SQL-Server 7). There are four tables:

tblPeople
ID(PK)PRENAME
-----
1Thomas
2Frank
3Chris

tblInventoryClasses
ID(PK)INVENTORYCLASS
-------
1Car
2Phone

tblInventoryItems
ID(PK)relInvClass(FK)ITEM
-----------
11Dodge Viper
21Chrysler
32Nokia
42Samsung

tblPeopleInventory
ID(PK)relPeople(FK)relInvItem(FK)
------------
112
213
321
423
534

In this example the last table tells me that
Thomas owns a Chrysler (class Car) and a Nokia (class Phone).

Can someone tell me how to write a query or a stored procedure which
produces a resultset like this:

qryOwners
PeopleCarPhone
----------
ThomasChryslerNokia
FrankDodge ViperNokia
Chris[NULL]Samsung

The main idea is that I need to be able to iterate such a collection.
It is guranteed that one "People" only owns one or zero "Car" and one
or zero "Phone".

I guess that it might be impossible to design a stored procedure with
such a variable amount of columns (in this case, each item from
tblInventoryClasses would mean another column).

Ary there any possibilities in accomplishing this without creating
temporary tables?

Any help would be really appreciated ;-)

Greetings,
Christoph BispingChristoph Bisping <bisping.nospamplease@.unikopie.de> wrote in message news:<85d21051enbtmd5g679kpf5bddcml7f3va@.4ax.com>...
> Hello!
> I'm seeking advice on a rather complex type of query I need to build
> in an Access ADP (SQL-Server 7). There are four tables:
> tblPeople
> ID(PK)PRENAME
> -----
> 1Thomas
> 2Frank
> 3Chris
> tblInventoryClasses
> ID(PK)INVENTORYCLASS
> -------
> 1Car
> 2Phone
> tblInventoryItems
> ID(PK)relInvClass(FK)ITEM
> -----------
> 11Dodge Viper
> 21Chrysler
> 32Nokia
> 42Samsung
> tblPeopleInventory
> ID(PK)relPeople(FK)relInvItem(FK)
> ------------
> 112
> 213
> 321
> 423
> 534
> In this example the last table tells me that
> Thomas owns a Chrysler (class Car) and a Nokia (class Phone).
> Can someone tell me how to write a query or a stored procedure which
> produces a resultset like this:
> qryOwners
> PeopleCarPhone
> ----------
> ThomasChryslerNokia
> FrankDodge ViperNokia
> Chris[NULL]Samsung

This particular query looks like:

CREATE VIEW qryOwners AS
SELECT
person.PRENAME AS People,
car.ITEM AS Car,
phone.ITEM AS Phone
FROM
tblPeople person
LEFT JOIN
(
tblInventoryClasses carClass
INNER JOIN tblInventoryItems car
ON carClass.INVENTORYCLASS = 'Car'
AND carClass.ID = car.relInvClass
INNER JOIN tblPeopleInventory carPerson
ON car.ID = carPerson.relInvItem
) ON person.ID = carPerson.relPeople
LEFT JOIN
(
tblInventoryClasses phoneClass
INNER JOIN tblInventoryItems phone
ON phoneClass.INVENTORYCLASS = 'Phone'
AND phoneClass.ID = phone.relInvClass
INNER JOIN tblPeopleInventory phonePerson
ON phone.ID = phonePerson.relInvItem
) ON person.ID = phonePerson.relPeople

> The main idea is that I need to be able to iterate such a collection.
> It is guranteed that one "People" only owns one or zero "Car" and one
> or zero "Phone".
> I guess that it might be impossible to design a stored procedure with
> such a variable amount of columns (in this case, each item from
> tblInventoryClasses would mean another column).

You cannot get each InventoryClass row to magically add a column to
the resultset unless you use a stored procedure that builds dynamic
SQL statements using the EXECUTE(string) command. That would be
qualified as UGLY code, though.

You can see the pattern though.

It's simpler/more efficient on the database side if you just join all
the tables together and use an ORDER BY clause to make your client
code easier to write.

-Russell|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. There is no such thing as a universal "id" -- to be
something is to be something in particular -- a row number or IDENTITY
is a way of destroying a RDBMS and making into a sequential file
system. Why do you have that silly, redundant "tbl-" prefix on data
element name -- tell me what it is LOGICALLY and not how you are
PHYSICALLY representing it. It makes you look like a FORTRAN or BASIC
programmer.

Let's get some DDL and fix the schema. An inventory class is an
attribute of an inventory item.

CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
name CHAR(15) NOT NULL,
..);

CREATE TABLE Inventory
(inventory_nbr INTEGER NOT NULL PRIMARY KEY,
inv_class CHAR(5) NOT NULL
CHECK (inv_class IN (..)),
item_description CHAR(15) NOT NULL,
..,);

CREATE TABLE Allocations
(person_id INTEGER NOT NULL
REFERENCES People(person_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
inventory_nbr INTEGER NOT NULL
REFERENCES Inventory (inventory_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
..,
PRIMARY KEY (person_id, inventory_nbr));

>> Can someone tell me how to write a query or a stored procedure
which produces a result set like this: <<

This is a report and not a query; this ought to be done in the front
end and not in the database at all. Thanks to the lack of specs and
DDL, we have no idea if people can have more than one car or more than
one phone. Here is one possible guess at an answer:

SELECT P1.name,
MAX (CASE WHEN I1.inv_class = 'car'
THEN I1.item_description
ELSE NULL END) AS auto,
MAX (CASE WHEN I1.inv_class = 'phone'
THEN I1.item_description
ELSE NULL END) AS phone
FROM People AS P1, Allocations AS A1, Inventory as I1
WHERE A1.person_id = P1.person_id
AND A1.inventory_nbr = I1.inventory_nbr
GROUP BY P1.name;

if you allowed only one item per class, then use:

CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
name CHAR(15) NOT NULL,
..);

CREATE TABLE Inventory
(inventory_nbr INTEGER NOT NULL PRIMARY KEY,
item_description CHAR(15) NOT NULL,
..,);

CREATE TABLE Allocations
(person_id INTEGER NOT NULL
REFERENCES People(person_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
inventory_nbr INTEGER NOT NULL
REFERENCES Inventory (inventory_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
inv_class CHAR(5) NOT NULL
CHECK (inv_class IN (..)),
UNIQUE (person_id, inv_class),
..,
PRIMARY KEY (person_id, inventory_nbr));|||rustleb@.hotmail.com (Russell Bevers) wrote:

>You cannot get each InventoryClass row to magically add a column to
>the resultset unless you use a stored procedure that builds dynamic
>SQL statements using the EXECUTE(string) command. That would be
>qualified as UGLY code, though.
>You can see the pattern though.
>It's simpler/more efficient on the database side if you just join all
>the tables together and use an ORDER BY clause to make your client
>code easier to write.
>-Russell

That was exactly what I (didn't) want to read...
After digging through hundreds of url's everything looks to me like
that's the only way to accomplish this. Anyway, thanks for your answer
which showed me that I definitely have to handle these things in the
frontend.

In fact, the overall processing performance seems to be good enough if
I just read all of these tables in order to build lookup-tables in my
application. Being familiar with UGLY code, your suggestion using
EXECUTE(strSQL) sounds like an alternative ;-)

Greetings,

Christoph Bisping|||joe.celko@.northface.edu (--CELKO--) wrote:
>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, datatypes, etc. in
>your schema are. There is no such thing as a universal "id" -- to be
>something is to be something in particular -- a row number or IDENTITY
>is a way of destroying a RDBMS and making into a sequential file
>system. Why do you have that silly, redundant "tbl-" prefix on data
>element name -- tell me what it is LOGICALLY and not how you are
>PHYSICALLY representing it.
>It makes you look like a FORTRAN or BASIC programmer.

Well, this wouldn't be a wrong statement. I do agree that I'm using
way to much of these "universal id"-cols in my tables which are surely
wasted.

As you might guess I'm quite inexperienced here and for now pure DDL
isn't one of my favorite languages but I'll see if I'm able to adapt
what you've written. But it seems to be much easier for me to go the
"sequential file system" way and do the complex People<->InventoryItem
mappings entirely at frontend level.

Thanks for your detailed explanation!

Greetings,

Christoph Bisping

Comparing values between 2 matrices (matrix)

Hello
I have two matrices. One contains sales data for the current year, the other prior year. Both matrices use different data sets

I'd like to compare the two - possibly by creating a third matrix that subtracts prior year from current year.

Any ideas? When I create a third matrix and substitute a formula like =sum(values, "Data source for matrix 1") - sum(values, "Data source for matrix 2"), the resultant matrix subtracts the grand total from the first matrix - not the individual "cell".

Any suggestions are appreciated.
Thanks

I would like to know the official answer to this too...

see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1673719&SiteID=1 for simular question...