alternate table value

I need to read through two tables, first stepping thru the first one,
finding a value to a particular field (model no) along with the value of
another (price), and then read thru the second table, looking for the same
field value (model no), and if found, use that (price) field vale verses the
original value for the (price) field.
TABLE 1
Model No. Price
X45 $30
X60 $50
TABLE 2
Model No. Price
X50 $35
X60 $48
RESULT SET
Model No. Price
X45 $30
X50 $35
X60 $48
Thanks,
DonOne method:
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
Hope this helps.
Dan Guzman
SQL Server MVP
<dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>I need to read through two tables, first stepping thru the first one,
>finding a value to a particular field (model no) along with the value of
>another (price), and then read thru the second table, looking for the same
>field value (model no), and if found, use that (price) field vale verses
>the original value for the (price) field.
> TABLE 1
> Model No. Price
> X45 $30
> X60 $50
> TABLE 2
> Model No. Price
> X50 $35
> X60 $48
> RESULT SET
> Model No. Price
> X45 $30
> X50 $35
> X60 $48
> Thanks,
> Don
>
>|||Hi Dan,
Thanks for the help. It appears as though this is very close. But, I am
still getting duplicate records for the same model number, when the desire
is to only use the record from table 2 when the same model no is found in
both.
Here is the result set I am getting:
Model No. Price
X45 $30
X50 $35
X60 $50
X60 $48
When I am looking for:
Model No. Price
X45 $30
X50 $35
X60 $48 <-- Table 2 is specifically used when same
model no is in both tables.
Below is the actual code being used:
SELECT TOP 100 PERCENT COALESCE (dbo.hb_view_organization_plan.plan_id,
dbo.hb_view_business_unit_plan.plan_id,
dbo.hb_view_subdivision_plan.plan_id,
dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
(dbo.hb_view_organization_plan.plan_no_name,
dbo.hb_view_business_unit_plan.plan_no_name,
dbo.hb_view_subdivision_plan.plan_no_name,
dbo.hb_view_lot_plan.plan_no_name)
AS plan_no_name, COALESCE
(dbo.hb_view_organization_plan.current_retail_price,
dbo.hb_view_business_unit_plan.current_retail_price,
dbo.hb_view_subdivision_plan.current_retail_price,
dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
COALESCE (dbo.hb_view_organization_plan.source,
dbo.hb_view_business_unit_plan.source, dbo.hb_view_subdivision_plan.source,
dbo.hb_view_lot_plan.source) AS source
FROM dbo.hb_view_organization_plan FULL OUTER JOIN
dbo.hb_view_business_unit_plan ON
dbo.hb_view_organization_plan.plan_id =
dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
dbo.hb_view_lot_plan FULL OUTER JOIN
dbo.hb_view_subdivision_plan ON
dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
dbo.hb_view_business_unit_plan.plan_id =
dbo.hb_view_subdivision_plan.plan_id
ORDER BY dbo.hb_view_organization_plan.plan_no_name
The logic here is that is to sum all the records and when a duplicate
plan_no_name is found, is to display only one record from the lowest in the
hierarchy, which organization, business_unit, subdivsion, and lot. Always
use the lot record first (lowest), subdivision second, business unit third,
and finally organization last (highest).
Again, thanks for your help.
Regards,
Don
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O1LO80ISFHA.2788@.TK2MSFTNGP09.phx.gbl...
> One method:
> SELECT
> COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
> COALESCE(t2.Price, t1.Price) AS Price
> FROM Table1 t1
> FULL JOIN Table2 t2 ON
> t1.ModelNo = t2.ModelNo
> ORDER BY 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>|||You design is wrong. Given a fixed hierarchy, each level should be in
the same row since it is an attribute of that entity. This is a basic
principle of data modeling. You might want to read what Chris Date and
Dave McGoveran said about this kind of design error. They called it
orthogonal design, while I prefer attribute splitting.
duplicate plan_no_name is found, is to display only one record [sic]
from the lowest in the hierarchy, which organization, business_unit,
subdivsion, and lot. Always use the lot record [sic] first (lowest),
subdivision second, business unit third, and finally organization last
(highest). <<
Row are not records and until you learn the differences, you will not
be able to write good SQL. You will think in terms of DML for
solutions that shoudl have been done with correct DDL. Since you did
not post DDL, it is impossible to make anything but a guess about the
data.
And using things like "view" or "tbl" in a data element name to tell us
about the storage method is a violationfo ISO-11179 and good data
mdoeling practices, too.|||On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:

>Hi Dan,
>Thanks for the help. It appears as though this is very close. But, I am
>still getting duplicate records for the same model number, when the desire
>is to only use the record from table 2 when the same model no is found in
>both.
>Here is the result set I am getting:
>Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
>When I am looking for:
>Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
>model no is in both tables.
>Below is the actual code being used:
(snip)
Hi Don,
Checking the code you posted, I see that you're not attempting to combine
two tables, but a total of four! Now, whereas a full outer join betwee two
tables is not very tough, a full outer join of three or more tables has
some gotchas in the ON clause.
Here's a simplified version of a four-way full outer join to show you how
you should approach this:
SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
FROM Table1 AS t1
FULL JOIN Table2 AS t2
ON t2.keycol = t1.keycol
FULL JOIN Table3 AS t3
ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
FULL JOIN Table4 AS t4
ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
I'll leave it too you to fill this pattern with your (IMO much too long)
table and column names.
Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
your query, and in the worst case introduces unnecessary overhead at
execution time. Please remove it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||<dbj> wrote in message news:OJ3NcqQSFHA.3144@.tk2msftngp13.phx.gbl...
> Hi Dan,
> Thanks for the help. It appears as though this is very close. But, I am
> still getting duplicate records for the same model number, when the desire
> is to only use the record from table 2 when the same model no is found in
> both.
> Here is the result set I am getting:
> Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
> When I am looking for:
> Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
> model no is in both tables.
I get the expected results using the sample data provided in your original
post. Below is the complete script:
CREATE TABLE Table1
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
price int NOT NULL
)
CREATE TABLE Table2
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table2 PRIMARY KEY,
price int NOT NULL
)
INSERT INTO Table1 VALUES('X45', 30)
INSERT INTO Table1 VALUES('X60', 50)
INSERT INTO Table2 VALUES('X50', 35)
INSERT INTO Table2 VALUES('X60', 48)
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
--results
ModelNo Price
-- --
X45 30
X50 35
X60 48

> Below is the actual code being used:
> SELECT TOP 100 PERCENT COALESCE
> (dbo.hb_view_organization_plan.plan_id,
> dbo.hb_view_business_unit_plan.plan_id,
> dbo.hb_view_subdivision_plan.plan_id,
> dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
> (dbo.hb_view_organization_plan.plan_no_name,
> dbo.hb_view_business_unit_plan.plan_no_name,
> dbo.hb_view_subdivision_plan.plan_no_name,
> dbo.hb_view_lot_plan.plan_no_name)
> AS plan_no_name, COALESCE
> (dbo.hb_view_organization_plan.current_retail_price,
> dbo.hb_view_business_unit_plan.current_retail_price,
> dbo.hb_view_subdivision_plan.current_retail_price,
> dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
> COALESCE (dbo.hb_view_organization_plan.source,
> dbo.hb_view_business_unit_plan.source,
> dbo.hb_view_subdivision_plan.source,
> dbo.hb_view_lot_plan.source) AS source
> FROM dbo.hb_view_organization_plan FULL OUTER JOIN
> dbo.hb_view_business_unit_plan ON
> dbo.hb_view_organization_plan.plan_id =
> dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
> dbo.hb_view_lot_plan FULL OUTER JOIN
> dbo.hb_view_subdivision_plan ON
> dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
> dbo.hb_view_business_unit_plan.plan_id =
> dbo.hb_view_subdivision_plan.plan_id
> ORDER BY dbo.hb_view_organization_plan.plan_no_name
> The logic here is that is to sum all the records and when a duplicate
> plan_no_name is found, is to display only one record from the lowest in
> the hierarchy, which organization, business_unit, subdivsion, and lot.
> Always use the lot record first (lowest), subdivision second, business
> unit third, and finally organization last (highest).
> Again, thanks for your help.
> Regards,
> Don
>
I wouldn't expect duplicate plan_ids as long as that is the is the primary
key of these tables. However, you might very well have duplicate
plan_no_name data. In that case, it is a symptom of a flaw in your data
model.
Hope this helps.
Dan Guzman
SQL Server MVP|||Call me old-fashioned or just plain dumb, but I'd go about it like
this:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM t1
LEFT JOIN t2 ON t1.modelno = t2.modelno
The existence of a row in t2 results in the t2 price; whereas an
absence of a row in t2 results in t1 price.|||> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>
True, but also the absence of a row in t1 should provide the t2 price based
on Don's requirements, hence the FULL JOIN. With his original data:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.modelno = t2.modelno
Results:
modelno price
-- --
X45 30
X60 48
Desired results:
modelno price
-- --
X45 30
X50 35
X60 48
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeme" <jeme.rey@.gmail.com> wrote in message
news:1114383919.282023.11520@.o13g2000cwo.googlegroups.com...
> Call me old-fashioned or just plain dumb, but I'd go about it like
> this:
> SELECT t1.modelno, price = isnull(t2.price,t1.price)
> FROM t1
> LEFT JOIN t2 ON t1.modelno = t2.modelno
> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>|||Hi Hugo,
Your suggestion worked. Thanks VERY MUCH for your help. And I would also
like to thank everyone else who responded as well. I am sure my
inexperience was apparent but really appreciate your patience helping me
through this. Below is your code that worked!
SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
t4.plan_id) AS plan_id, COALESCE (t1.plan_availability_pricing_id,
t2.plan_availability_pricing_id,
t3.plan_availability_pricing_id, t4.plan_availability_pricing_id) AS
plan_availability_pricing_id,
COALESCE (t1.plan_number, t2.plan_number,
t3.plan_number, t4.plan_number) AS plan_number, COALESCE (t1.plan_number,
t2.plan_name,
t3.plan_name, t4.plan_name) AS plan_namer, COALESCE
(t1.plan_no_name, t2.plan_no_name, t3.plan_no_name, t4.plan_no_name) AS
plan_no_name,
COALESCE (t1.current_retail_price,
t2.current_retail_price, t3.current_retail_price, t4.current_retail_price)
AS current_retail_price, COALESCE (t1.source,
t2.source, t3.source, t4.source) AS source
FROM dbo.hb_view_lot_plan t1 FULL OUTER JOIN
dbo.hb_view_subdivision_plan t2 ON t2.plan_id =
t1.plan_id FULL OUTER JOIN
dbo.hb_view_business_unit_plan t3 ON t3.plan_id =
COALESCE (t1.plan_id, t2.plan_id) FULL OUTER JOIN
dbo.hb_view_organization_plan t4 ON t4.plan_id =
COALESCE (t1.plan_id, t2.plan_id, t3.plan_id)
ORDER BY t1.plan_no_name
Regards,
Don
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vo3o61t3hm1doo7ou1f1hoimq6nrdq5b07@.
4ax.com...
> On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:
>
> (snip)
> Hi Don,
> Checking the code you posted, I see that you're not attempting to combine
> two tables, but a total of four! Now, whereas a full outer join betwee two
> tables is not very tough, a full outer join of three or more tables has
> some gotchas in the ON clause.
> Here's a simplified version of a four-way full outer join to show you how
> you should approach this:
> SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
> COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
> FROM Table1 AS t1
> FULL JOIN Table2 AS t2
> ON t2.keycol = t1.keycol
> FULL JOIN Table3 AS t3
> ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
> FULL JOIN Table4 AS t4
> ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
> I'll leave it too you to fill this pattern with your (IMO much too long)
> table and column names.
> Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
> your query, and in the worst case introduces unnecessary overhead at
> execution time. Please remove it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 24 Apr 2005 22:48:27 -0500, <dbj> wrote:

>Hi Hugo,
>Your suggestion worked. Thanks VERY MUCH for your help. And I would also
>like to thank everyone else who responded as well. I am sure my
>inexperience was apparent but really appreciate your patience helping me
>through this. Below is your code that worked!
>SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
(snip)
Hi Don,
Good to hear that it worked. Now all that's left to do is to get rid of
the totally useless TOP 100 PERCENT. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Alter of text type field

Can I change the datatype for a particular field which was previously set to text?If possible then how?Secondly can I change the datatype of a filed of int type to identity after inserting data?Can I remove identity property from a field?USE Northwind
GO

CREATE TABLE myTable98 (Col1 int, Col2 text)
GO

INSERT INTO myTable98 (Col1, Col2)
SELECT 1, REPLICATE('X',8001) UNION ALL
SELECT 2, 'Hi! How the hell are you' UNION ALL
SELECT 3, 'X'
GO

ALTER TABLE myTable98 ALTER Column Col2 varchar(8000)
GO
-- No Good
ALTER TABLE myTable98 ADD Col3 varchar(8000)
GO

UPDATE myTable98 SET Col3 = Col2

SELECT Col1, LEN(Col3), Col3 FROM myTable98

ALTER TABLE myTable98 DROP Column Col2
GO

SELECT * FROM myTable98
GO

DROP TABLE myTable98
GO

Look up ALTER in Books Online for more....

Alter column datatype and default value in a table

i have a table which has 3 columns, one of three is set to default value 0. Now i have to change data type of that particular column and its default value using sql query. am working with sql server 2005. am not getting any prob when i exec ALTER TABLE TABLE_NAME ADD COLUMN [COLUMN_NAME] DATATYPE SIZE CONSTRAINT [CONSTRAINT_NAME] VALUE. but getting probs when exec
ALTER TABLE TABLE_NAME ALTER COLUMN_NAME DATATYPE SIZE [CONSTRAINT NAME] VALUE. Help me, thanx in advance

Quote:

Originally Posted by vijaialphonse

i have a table which has 3 columns, one of three is set to default value 0. Now i have to change data type of that particular column and its default value using sql query. am working with sql server 2005. am not getting any prob when i exec ALTER TABLE TABLE_NAME ADD COLUMN [COLUMN_NAME] DATATYPE SIZE CONSTRAINT [CONSTRAINT_NAME] VALUE. but getting probs when exec
ALTER TABLE TABLE_NAME ALTER COLUMN_NAME DATATYPE SIZE [CONSTRAINT NAME] VALUE. Help me, thanx in advance

Are you sure your columns meet all the criteria described in the ALTER COLUMN notes at http://msdn2.microsoft.com/en-us/library/ms190273.aspx ?
Also not that if you modify the type and/or constraints of a column the data already in the column must meet the new settings.

Allowing users to truncate log file

I would like to allow a particular user to truncate a log file in a
stored procedure that the user runs every day. At this moment the only
personnel that can truncate the log file are personnel with sysadmin
rights. Is there any way to do this in sql server 2005 without
granting this user sysadmin rights (something we REALLY don't want to
do)? Thanks for all your help in advance.

Dave C.hedgracer (d.christman@.sbcglobal.net) writes:

Quote:

Originally Posted by

I would like to allow a particular user to truncate a log file in a
stored procedure that the user runs every day. At this moment the only
personnel that can truncate the log file are personnel with sysadmin
rights. Is there any way to do this in sql server 2005 without
granting this user sysadmin rights (something we REALLY don't want to
do)? Thanks for all your help in advance.

Yes, this can be done with help of certificates. I have an article on my
web site that describes this in detail:
http://www.sommarskog.se/grantperm.html.

However, this not at all sound right to me, at least if the user would
truncate the log file every day. Truncating the log is something you
only do in exceptional cases when there is an emergency. Normally, you
either:
1) Run with full recovery and schedule regular full backups as well as
transaction log backups.
2) Run with simple recovery and schedule only full backups. The log
will be auto-truncated.

When you run with full recovery, you do so, because you want to be able
to recover the database to any given point in time. But if you truncate
the log, you lose that possibility. Which in fact is self-evident in
SQL 2005, where the only way to do this is to set the database into
simple recovery.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Allowing for blank fields in date text boxes

Several textboxes are being on a web form for date entry. and we want to allow the user to leave date fields blank (if particular date is unknown). However, when the field is left blank, the SQL Server 2000 database defaults to 1/1/1900 (datatype = datetime).
My question is as follows: How is it possible to leave a date textbox field blank, and either send 00\00\0000 or spaces to the SQL Server 2000 database datetime field?
Any advice/insight is appreciated! RobEDIT
Both Datetime and SmallDatetime allow NULLs Datetime is 8bytes while Smalldatetime is 4bytes but its resolution is limited. If you want Time Span you have to use DateTime.
when you are using NULL with Datetime you must use COUNT (*) in all you calculations because all SQL Server aggregate functions ignore NULLs except COUNT (*).
Hope this helps.|||Check outthis article

Allowing a user to execute SProcs

Hi,
I have a database that I'm using which contains many stored procedures. The
only way I have been able to allow a particular user to execute the
procedure is to select that user, then open that users permissions, find the
table and go through the list of procedures one by one allowing the user the
execute permission.
Can someone let me know how I can just allow a user permission to execute
all stored procedures that exist, as well as any new ones that may be
created. Is their like a "Can Execute" permission and if so where can i find
it.
Thanks everyone
SimonHere's one idea:
http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:uYiTk9k8DHA.3012@.TK2MSFTNGP09.phx.gbl...
Hi,
I have a database that I'm using which contains many stored procedures. The
only way I have been able to allow a particular user to execute the
procedure is to select that user, then open that users permissions, find the
table and go through the list of procedures one by one allowing the user the
execute permission.
Can someone let me know how I can just allow a user permission to execute
all stored procedures that exist, as well as any new ones that may be
created. Is their like a "Can Execute" permission and if so where can i find
it.
Thanks everyone
Simon

All tables and columns referenced by sql objects

I'm trying to find all the tables and columns referenced by the sql objects (stored procs, UDFs, views, etc.) in a particular MSSQL2005 database.

I can join the sys.objects, sys.sql_dependencies and sys.columns tables using object_id, referenced_major_id, referenced_minor_id, and column_id but I find that the more complex procs contain references to tables (sub selects, for example) that do not seem to appear in sys.sql_dependencies.

I can always do string searches on the definition column in sys.sql_modules, but that won't reliably get me column+table combos.

Anyone have any ideas?

Thanks!

I actually wrote a blog on this exact topic recently. Check it out:

http://blogs.claritycon.com/blogs/the_englishman/default.aspx

HTH

|||

Well... close but... Your solution involves string searches on the proc definition which may or may not (probably not) distinguish between tab1.tab1Key (PK) and tab2.tab1Key (FK).

Incidentally, check out http://msdn2.microsoft.com/en-us/library/ms187997.aspx for the SQL2005 system views that correspond to the SQL 2000 tables.

Thanks!

|||

No, for that you would have to use the following in an exists:

SELECT * FROM sysforeignkeys f join sysobjects obj on obj.id = f.fkeyid

Incidentally, I was told by a SQL server database engine worker that the INFORMATION_SCHEMA schema views are better to use. Check out the following:

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

That should do it, in combination with my stored porcedure. Let me know if you need more information.

|||

This is unfortunately not so easy to do. You should rely on your source code control to maintain the dependencies since it can be easily broken on the database side by recreating objects in wrong order or dropping some objects etc. There are certain dependencies like foreign key constraints, schema bound objects which are easy to obtain and maintained accurately by the engine. But there are other by name dependencies which are harder to track. For an example of how complex this scenario can be you can take a look at my blog post below which covers the direct dependencies on a column:

http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx

All Member Formula AS 2005

Hello,

In AS 2000, we used the All Member Formula property for a dimension to force the dimension to use a particular level when the dimension was at the All level. Is there similar functionality in AS 2005?

Thanks.

In AS2005 the same functionality can be achieved by adding the following line to your MDX Script:

Dimension.Hierarchy.[All Member] = <all member formula>;