Can I insert into the same table a new row with the "old" row field value?

I'm trying to insert values into the same database from itself.
Essentially, I want to add another row to the table for each row with
reg_cat_id = 3. But in this row, I want the original registration_id
to show up in the new row.
Here is my syntax below - this generates an error:
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
'05/05/2007')
SELECT REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
FROM Registration_Category t1
WHERE (REG_CAT_ID = 3)
ORDER BY REGISTRATION_ID
Any suggestions?On 30 Mar 2006 14:40:12 -0800, Dee wrote:
(snip)
>Here is my syntax below - this generates an error:
>INSERT INTO Registration_Category
> (REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
>VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
>'05/05/2007')
> SELECT REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
> FROM Registration_Category t1
> WHERE (REG_CAT_ID = 3)
> ORDER BY REGISTRATION_ID
>Any suggestions?
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
SELECT 90, t1.REGISTRATION_ID, 'test',
'20070505', 'Y', '20070505')
FROM Registration_Category AS t1
WHERE REG_CAT_ID = 3
Hugo Kornelis, SQL Server MVP

Can I insert data from a report with RS2005?

Hi All,
Here's my problem:
I need to pull some items into a table report. I have the items grouped
by a key field (AlertID) and a detail the the user can drilldown to for
each item. I'm having trouble with the linking, I try to link this
report to another report that takes the parameters and runs a stored
proc to insert the data for the item (Alert History). Each report works
fine on by itself, but I can't get them to link in anyway, I've tried
Jump to Report and Jump to URL.
Any suggestions would be greatly appreciated.
Thanks,
Damien Johnston
P.S. I also need know if there is a writable textbox and a checkbox
control available in RS?In general this is a bad idea (trying to write data). Some issues you would
have to deal with are: multi-user, cleaning up data when done, etc.
Given what you say I don't see why you need to do this. If your stored
procedure can figure out the data to insert then why can't you just have a
stored procedure returning the appropriate data to the report. Instead of
drill down you should be using drill through. Drill through is much more
efficient. Have a field that you highlight and color blue. Users understand
that means to click on it. Then use jump to report to call up the report
with the detail information.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dij0674" <dij0674@.hotmail.com> wrote in message
news:1161026120.846569.113660@.e3g2000cwe.googlegroups.com...
> Hi All,
> Here's my problem:
> I need to pull some items into a table report. I have the items grouped
> by a key field (AlertID) and a detail the the user can drilldown to for
> each item. I'm having trouble with the linking, I try to link this
> report to another report that takes the parameters and runs a stored
> proc to insert the data for the item (Alert History). Each report works
> fine on by itself, but I can't get them to link in anyway, I've tried
> Jump to Report and Jump to URL.
> Any suggestions would be greatly appreciated.
> Thanks,
> Damien Johnston
> P.S. I also need know if there is a writable textbox and a checkbox
> control available in RS?
>|||Bruce--Thanks for the reply, I'll look at alternative methods to solve
the problem.
Here are the details of what's required and what I have in place:
I have a database monitoring app the alerts when an event occurs (like
profiler)
I have specific events that need to be trapped and a historical record
needs to be kept of the original event along with any updates to an
event history field. These events are stored in a SQL DB
I need to be able to allow certain users to access this via some sort
of form/webpage, and update the events they are responsible for with
documentation, such as an email or an uploaded file.
I need to be able have a single parent to many children relationship
for the events. For example, event 1 can be chosen as the parent for
events 2,3,4,5,6 and event 1's event history will become the history
of the child events.
I sure I can get the SQL side of things, stored procs and table design.
But I'm struggling with a front end for my users; I was hoping that
RS had the ability to function in this way.
Any suggestions would be greatly appreciated.
Thanks Again,
Damien Johnston
Bruce L-C [MVP] wrote:
> In general this is a bad idea (trying to write data). Some issues you would
> have to deal with are: multi-user, cleaning up data when done, etc.
> Given what you say I don't see why you need to do this. If your stored
> procedure can figure out the data to insert then why can't you just have a
> stored procedure returning the appropriate data to the report. Instead of
> drill down you should be using drill through. Drill through is much more
> efficient. Have a field that you highlight and color blue. Users understand
> that means to click on it. Then use jump to report to call up the report
> with the detail information.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "dij0674" <dij0674@.hotmail.com> wrote in message
> news:1161026120.846569.113660@.e3g2000cwe.googlegroups.com...
> > Hi All,
> >
> > Here's my problem:
> > I need to pull some items into a table report. I have the items grouped
> > by a key field (AlertID) and a detail the the user can drilldown to for
> > each item. I'm having trouble with the linking, I try to link this
> > report to another report that takes the parameters and runs a stored
> > proc to insert the data for the item (Alert History). Each report works
> > fine on by itself, but I can't get them to link in anyway, I've tried
> > Jump to Report and Jump to URL.
> >
> > Any suggestions would be greatly appreciated.
> >
> > Thanks,
> >
> > Damien Johnston
> >
> > P.S. I also need know if there is a writable textbox and a checkbox
> > control available in RS?
> >

Can I index a table in View ?

I create a view based on table. The table (say tblA) has an index on column
A.
When I do a sql query like "select .. from vMyView as MyView,tblB
where tblB.colA = MyView.A", will it use the index on tblA column A , or do
I
need to (and can I) create an index on the view ?
CREATE view vMyView()
select a,b,c...from tblA --> tblA.a is indexed
Thank you very much.Look up "Indexed views" in Books online.
Note - This feature is only supported in Enterprise/Developer editions.
Immy
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:16F4C4B5-1006-4A73-9BDF-BF7C2CC36B80@.microsoft.com...
>I create a view based on table. The table (say tblA) has an index on column
>A.
> When I do a sql query like "select .. from vMyView as MyView,tblB
> where tblB.colA = MyView.A", will it use the index on tblA column A , or
> do I
> need to (and can I) create an index on the view ?
> CREATE view vMyView()
> select a,b,c...from tblA --> tblA.a is indexed
> Thank you very much.|||The base table index will be used, unless you create an indexed view. See
Books Online for details on how to create indexed views.
Indexed views basically allow you to index only a subset of data.
What exactly are you trying to achieve?
ML
http://milambda.blogspot.com/|||Not entirely true. From Books Online:
"
Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
"
ML
http://milambda.blogspot.com/|||It will use the base table index if it is useful to do so (based on many
factors, like the selectivity of the index and your search argument)
You can index the view, provided it meets the strict requirements for doing
so. You can do it in any version, but as noted only in Enterprise Edition
will it use the index transparently, other versions require you to use the
NOEXPAND keyword so he text of the view isn't brought into the plan creation
(so you manually force it to treat the view as a table, more or less)
So the main reason I piped up and said basically the same things that were
said before is this. Do you know how to tell if an index is being used
using Management Studio / Query Analyzer? When it comes to predicting what
index may or may not be used, it generally behooves you to try it out.
There are menu items on both for displaying estimated plans, and then actual
plans that were used to execute the query. So execute the query and look at
the plan, and fiddle about with it for a while and you will get the hang of
when an index will be used.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:16F4C4B5-1006-4A73-9BDF-BF7C2CC36B80@.microsoft.com...
>I create a view based on table. The table (say tblA) has an index on column
>A.
> When I do a sql query like "select .. from vMyView as MyView,tblB
> where tblB.colA = MyView.A", will it use the index on tblA column A , or
> do I
> need to (and can I) create an index on the view ?
> CREATE view vMyView()
> select a,b,c...from tblA --> tblA.a is indexed
> Thank you very much.|||Thanks.
I guess I am worry that in a sql query like the following
"select .. from vMyView as MyView,tblB where tblB.colA = MyView.A", the
query will be slow if it does not use the base table index.
CREATE view vMyView()
select a,b,c...from tblA --> tblA.a is indexed
"ML" wrote:

> The base table index will be used, unless you create an indexed view. See
> Books Online for details on how to create indexed views.
> Indexed views basically allow you to index only a subset of data.
> What exactly are you trying to achieve?
>
> ML
> --
> http://milambda.blogspot.com/|||If you post more DDL (see here for details:
http://www.aspfaq.com/etiquette.asp?id=5006) , we can provide better help.
select .. from vMyView as MyView,tblB where tblB.colA = MyView.A
This may not be optimal. For one the old (deprecated) join syntax is used.
Compare the execution plan of your current query to the execution plan of
something like this:
select <columns>
from tblA
inner join tblB
on tblB.colA = tblA.colA
ML
http://milambda.blogspot.com/sql

Can I index a table in UDF ?

I create a UDF based on a table. The table (say tblA) has an index on column
A.
When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
where tblB.colA = MyProc.A", will it use the index on tblA column A , or do
I
need to (and can I) create an index on the UDF ?
CREATE function dbo.udfMyProc()
returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
varchar(50), ...)
AS BEGIN
INSERT INTO @.myTable(a,b,c,d,e...)
select a,b,c...from tblA --> tblA.a is indexed
return
end
Thank you very much.That query will not use the index on "tblA" - you need to create one on the
temp table returned by the UDF. You cannot create a regular index on table
variables though (a CREATE INDEX statement), but you can use PRIMARY KEY and
UNIQUE constraints:
...
RETURNS @.tmp TABLE
(
somestring varchar(50) NOT NULL PRIMARY KEY
)
If the index can have non-unique records you can fake an index by adding an
identity column so that each record will be unique:
...
RETURNS @.tmp TABLE
(
somestring varchar(50) NOT NULL
, meaningless_column int NOT NULL IDENTITY(1,1)
, PRIMARY KEY (somestring, meaningless_column)
)
Make sure to make the IDENTITY column last in the PK/UNIQUE definition so
the index is still useful.
KH
"Paul fpvt2" wrote:

> I create a UDF based on a table. The table (say tblA) has an index on colu
mn A.
> When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
> where tblB.colA = MyProc.A", will it use the index on tblA column A , or d
o I
> need to (and can I) create an index on the UDF ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> Thank you very much.|||What do you mean by "use the index"? It will not copy the index onto
@.table, if that's what you mean. The only indexes you can create on a table
variable are via PRIMARY KEY or UNIQUE constraints... if you're talking
about ordering, that's a different conversation altogether...
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
>I create a UDF based on a table. The table (say tblA) has an index on
>column A.
> When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
> where tblB.colA = MyProc.A", will it use the index on tblA column A , or
> do I
> need to (and can I) create an index on the UDF ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> Thank you very much.|||"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
>I create a UDF based on a table. The table (say tblA) has an index on
>column A.
> When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
> where tblB.colA = MyProc.A", will it use the index on tblA column A , or
> do I
> need to (and can I) create an index on the UDF ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> Thank you very much.
If your function truly consists of a single INSERT... SELECT statement then
you should turn it into an inline function rather than a multi-statement
one. That way your query against the function will be more likely to benefit
from an index on the base table. An example version of an inline function is
given below. This looks subtly different from what you posted but in terms
of the way the function works the difference is very significant.
CREATE function dbo.udfMyProc()
RETURNS TABLE
AS
RETURN (SELECT a,b,c...FROM tblA)
GO
BTW, if the function doesn't have any parameters then why use a function at
all? You could use a view for that.
Hope this helps.
David Portas
SQL Server MVP
--|||Thank you everybody for your replies.
I tried the view and it works a lot faster, I will use it instead.
But, I have a question about inline function. The example that you posted
looks the same with the UDF that I posted (CREATE function dbo.udfMyProc() )
What is the difference between UDF and inline function ?
Thanks a lot.
"David Portas" wrote:

> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
> If your function truly consists of a single INSERT... SELECT statement the
n
> you should turn it into an inline function rather than a multi-statement
> one. That way your query against the function will be more likely to benef
it
> from an index on the base table. An example version of an inline function
is
> given below. This looks subtly different from what you posted but in terms
> of the way the function works the difference is very significant.
> CREATE function dbo.udfMyProc()
> RETURNS TABLE
> AS
> RETURN (SELECT a,b,c...FROM tblA)
> GO
> BTW, if the function doesn't have any parameters then why use a function a
t
> all? You could use a view for that.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||One more question about inline function and views.
I understand that the ORDER BY clause is invalid in views and inline
functions.
If I need to use ORDER BY clause in my query inside the views or inline
function, Is there a way around it ?
Thanks.
"David Portas" wrote:

> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
> If your function truly consists of a single INSERT... SELECT statement the
n
> you should turn it into an inline function rather than a multi-statement
> one. That way your query against the function will be more likely to benef
it
> from an index on the base table. An example version of an inline function
is
> given below. This looks subtly different from what you posted but in terms
> of the way the function works the difference is very significant.
> CREATE function dbo.udfMyProc()
> RETURNS TABLE
> AS
> RETURN (SELECT a,b,c...FROM tblA)
> GO
> BTW, if the function doesn't have any parameters then why use a function a
t
> all? You could use a view for that.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||> What is the difference between UDF and inline function ?
there are two types of UDFs:
Inline. Consists of only one query. Think of it as a view or "macro".
Multi-statement. Here you define a table variable and populate that table va
riable, and when
function code exist at run-time the data is selected from the variable, All
that work is overhead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:B7BABDF8-B1A1-4735-BBFA-5A250B92B1F6@.microsoft.com...
> Thank you everybody for your replies.
> I tried the view and it works a lot faster, I will use it instead.
> But, I have a question about inline function. The example that you posted
> looks the same with the UDF that I posted (CREATE function dbo.udfMyProc()
)
> What is the difference between UDF and inline function ?
> Thanks a lot.
>
> "David Portas" wrote:
>|||Thanks.
Is the following UDF considered inline (because it only has 1 query: select
a,b,c...) ?
CREATE function dbo.udfMyProc()
returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
varchar(50), ...)
AS BEGIN
INSERT INTO @.myTable(a,b,c,d,e...)
select a,b,c...from tblA --> tblA.a is indexed
return
end
"Tibor Karaszi" wrote:

> there are two types of UDFs:
> Inline. Consists of only one query. Think of it as a view or "macro".
> Multi-statement. Here you define a table variable and populate that table
variable, and when
> function code exist at run-time the data is selected from the variable, Al
l that work is overhead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:B7BABDF8-B1A1-4735-BBFA-5A250B92B1F6@.microsoft.com...
>|||No, that is a multi-statement UDF. An Inline is:
CREATE FUNCTION f(...)
RETURNS TABLE
AS
RETURN (SELECT ...)
For above, SQL Server doesn't have to populate a table variable and then ret
urn the result. SQL
Server can, and will "inline" above query in the outer query, just like it d
oes with a view. Or
think of it as a macro, if you wish.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:D30F1F9A-E3D1-412A-B9D5-30A0F265B8C8@.microsoft.com...
> Thanks.
> Is the following UDF considered inline (because it only has 1 query: selec
t
> a,b,c...) ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> "Tibor Karaszi" wrote:
>|||Thanks.
In the sample query that you posted, does it mean that you return a table
that is populated with the select statement ?
"Tibor Karaszi" wrote:

> No, that is a multi-statement UDF. An Inline is:
> CREATE FUNCTION f(...)
> RETURNS TABLE
> AS
> RETURN (SELECT ...)
> For above, SQL Server doesn't have to populate a table variable and then r
eturn the result. SQL
> Server can, and will "inline" above query in the outer query, just like it
does with a view. Or
> think of it as a macro, if you wish.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:D30F1F9A-E3D1-412A-B9D5-30A0F265B8C8@.microsoft.com...
>

can i have data of two datasets into a single table

In Report, I need to display PO Details such as pono,item names and
their item qty,item rate, po amount, dcno., dcdate.
PO Details TenderName,pono,item name,item qty, item rate available in a
table tblPurchaseOrder.
PO amount=sum(item qty*item rate) + sum(charge)+ sum(item tax amount).
charge amount, tax amount may/may not exist for PO.
Each PO can have more than one DC.
using single query if i am trying to retreive pono,item name,po amount,
dc date,dcno then i am getting po amount no. of times dc
occurs*original po amount.so i splitted po details and dc details into
two. now the data i am getting is correct. but using two tables the
data is not corresponding to each other. dc details getting ups and
downs.
Kindly give me solution ...
My query to retreive PO Details is as follows :
SELECT viewPurchaseOrderComponents.TenderName,
viewPurchaseOrderComponents.PONo, viewPurchaseOrderComponents.PODate,
viewPurchaseOrderComponents.ComponentName,
viewPurchaseOrderComponents.ComponentQty,
viewPurchaseOrderComponents.Rate,
viewPurchaseOrderComponents.ComponentQty *
viewPurchaseOrderComponents.Rate AS CompAmount,
viewPurchaseOrderComponents.UnitName,
B.TotalTaxAmount, A.Charge,
viewPurchaseOrderComponents.ProjectComponentId,
viewPurchaseOrderComponents.ComponentId,
viewPurchaseOrderComponents.IndentNo, tblPurchaseOrder.PaymentTerms,
tblPurchaseOrder.IndentDate,
tblPurchaseOrder.DeliveryPeriod, tblPurchaseOrder.Status,
tblPurchaseOrder.HasAnyAmendment,
tblPurchaseOrder.IsRegularize,
viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier
FROM viewPurchaseOrderComponents INNER JOIN
tblPurchaseOrder ON
viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
JOIN
(SELECT PONo, SUM(Amount) AS Charge
FROM tblPOCharges
GROUP BY PONo) AS A ON A.PONo = viewPurchaseOrderComponents.PONo LEFT OUTER JOIN
(SELECT POComponentId,
SUM(TaxValue) AS TotalTaxAmount
FROM tblPOComponentTaxes
GROUP BY POComponentId) AS B ON
B.POComponentId = viewPurchaseOrderComponents.ComponentId
WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
(viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
For DC Details :
SELECT viewPurchaseOrderComponents.PONo,
viewPurchaseOrderComponents.TenderName,
viewPurchaseOrderComponents.PODate,
viewPurchaseOrderComponents.ComponentName,
tblPurchaseOrder.Status, tblPurchaseOrder.HasAnyAmendment,
tblPurchaseOrder.IsRegularize,
viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier,
tblComponentsOfDc.Qty AS DCQty,
tblDeliveryChallan.DCId, CONVERT(varchar(11),
tblDeliveryChallan.ChallanDate, 106) AS ChallanDate
FROM viewPurchaseOrderComponents LEFT OUTER JOIN
tblDeliveryChallan ON tblDeliveryChallan.PONo
= viewPurchaseOrderComponents.PONo INNER JOIN
tblPurchaseOrder ON
viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
JOIN
tblComponentsOfDc ON tblDeliveryChallan.DCId = tblComponentsOfDc.DCId
WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
(viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
In a single table i need TenderName,PONO,item details name,qty,rate
POAmount, DCDate...
Waiting for solution ...Try looking at subreports. You can embed a subreport into a detail line of a
table. Add an extra detail line and then drag and drop your subreport onto
the cell. Then right mouse click, properties and set the parameters to the
field values that link the two together.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sarada" <sarada_devi_i@.yahoo.com> wrote in message
news:1155904374.494550.59260@.i3g2000cwc.googlegroups.com...
> In Report, I need to display PO Details such as pono,item names and
> their item qty,item rate, po amount, dcno., dcdate.
> PO Details TenderName,pono,item name,item qty, item rate available in a
> table tblPurchaseOrder.
> PO amount=sum(item qty*item rate) + sum(charge)+ sum(item tax amount).
> charge amount, tax amount may/may not exist for PO.
> Each PO can have more than one DC.
> using single query if i am trying to retreive pono,item name,po amount,
> dc date,dcno then i am getting po amount no. of times dc
> occurs*original po amount.so i splitted po details and dc details into
> two. now the data i am getting is correct. but using two tables the
> data is not corresponding to each other. dc details getting ups and
> downs.
> Kindly give me solution ...
> My query to retreive PO Details is as follows :
> SELECT viewPurchaseOrderComponents.TenderName,
> viewPurchaseOrderComponents.PONo, viewPurchaseOrderComponents.PODate,
> viewPurchaseOrderComponents.ComponentName,
> viewPurchaseOrderComponents.ComponentQty,
> viewPurchaseOrderComponents.Rate,
> viewPurchaseOrderComponents.ComponentQty *
> viewPurchaseOrderComponents.Rate AS CompAmount,
> viewPurchaseOrderComponents.UnitName,
> B.TotalTaxAmount, A.Charge,
> viewPurchaseOrderComponents.ProjectComponentId,
> viewPurchaseOrderComponents.ComponentId,
> viewPurchaseOrderComponents.IndentNo, tblPurchaseOrder.PaymentTerms,
> tblPurchaseOrder.IndentDate,
> tblPurchaseOrder.DeliveryPeriod, tblPurchaseOrder.Status,
> tblPurchaseOrder.HasAnyAmendment,
> tblPurchaseOrder.IsRegularize,
> viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier
> FROM viewPurchaseOrderComponents INNER JOIN
> tblPurchaseOrder ON
> viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
> JOIN
> (SELECT PONo, SUM(Amount) AS Charge
> FROM tblPOCharges
> GROUP BY PONo) AS A ON A.PONo => viewPurchaseOrderComponents.PONo LEFT OUTER JOIN
> (SELECT POComponentId,
> SUM(TaxValue) AS TotalTaxAmount
> FROM tblPOComponentTaxes
> GROUP BY POComponentId) AS B ON
> B.POComponentId = viewPurchaseOrderComponents.ComponentId
> WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
> (viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
> For DC Details :
> SELECT viewPurchaseOrderComponents.PONo,
> viewPurchaseOrderComponents.TenderName,
> viewPurchaseOrderComponents.PODate,
> viewPurchaseOrderComponents.ComponentName,
> tblPurchaseOrder.Status, tblPurchaseOrder.HasAnyAmendment,
> tblPurchaseOrder.IsRegularize,
> viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier,
> tblComponentsOfDc.Qty AS DCQty,
> tblDeliveryChallan.DCId, CONVERT(varchar(11),
> tblDeliveryChallan.ChallanDate, 106) AS ChallanDate
> FROM viewPurchaseOrderComponents LEFT OUTER JOIN
> tblDeliveryChallan ON tblDeliveryChallan.PONo
> = viewPurchaseOrderComponents.PONo INNER JOIN
> tblPurchaseOrder ON
> viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
> JOIN
> tblComponentsOfDc ON tblDeliveryChallan.DCId => tblComponentsOfDc.DCId
> WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
> (viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
> In a single table i need TenderName,PONO,item details name,qty,rate
> POAmount, DCDate...
> Waiting for solution ...
>

can i grant all privilege to user?

it is possible to grant all privilege (CRUD) to specified table to user. But, now, i want to grant all privilege (CRUD) of all tables, views, sp, ... of database to the user. is it possible?

regards,

Yes you can try the link below for SQL Server object permissions. It can get complex but the link below can get you started. You have the option of doing it with T-SQL or with GUI with Management Studio. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms188371.aspx

|||Now, i assign a role(db_owner) to the user. Because i don't know the different permissions of the different role, i just assign as db_owner. In my case, the only needed is DDL, CRUD DML, stored procedure. That's all. So, is there any other more suitable role. As i can't find the way that assign all these privilege only one command, i just assign a role. I don't know whether is it good or not.

Any idea?|||

You have two options dbcreator and db_ddladmin roles both are less than dbo and may do what you need because a of service pack3 in SQL Server 2000 dbcreator cannot change ownership of a database. The links below will help with more details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175892.aspx

http://msdn2.microsoft.com/en-us/library/ms189121.aspx

|||thx for ur kindness.|||I am glad I could help.sql

can i get the output of 'sp_columns' system SP into a table through T-SQL

Hi,
I want to collect the output of system stored procedure 'sp_columns' into a temporary table. Please suggest how can i achieve this.

Ashu Sharma

Create a temp table with columns as described at sp_columns in BOL and then do
INSERT INTO your_temp_table EXEC sp_columns
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de|||Thanks Franksql

Can I generate Table Script in a Stored Procedure?

I have to load around 40 tables and I want to write one Stored Procedure that I pass a table name into. Data is coming from 3 different sources and to fill each of the 40 tables. I want to create a temp table that I can load first to make sure it succeeds first before I truncate and insert into the production table.

Is there a system procedure or something that will allow me to create a temp table from a table that alresdy exist in my database?

You can use SELECT INTO to create a new table based on a query like:

SELECT t.col1, t.col2, t.col3

INTO #t

FROM your_table AS t

Above query will create table with same structure as your_table without the defaults, constraints, indexes etc. Identity property on columns will be transferred though. You may also want to look at SSIS to automate your bulk loading process. You can also take a look at the link below for more pointers on how to improve the bulk load process:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

Note that with above query, you can create permanent table too. If you create a temporary table then you can only use BULK INSERT statement since the temporary table is connection scoped. And I am not sure if SSIS can perform SELECT...INTO and bulk load from the client side within context of single connection (You will have to ask in the SSIS forum).

|||

Good to know you can create Temps like this.

I might be able to work with this let me know what you think.

Problem:

I’m getting my data from an AS400 using

Insert Into @.TableName

Select *

from Opendatasource(“Connection Info”).AS400ServerName.AS400Library.@.TableName

The table name and column structure in the AS400 are the same as in my database but the column type is different i.e. A lot of the AS400 columns are Char when I need them to be Decimal. I need to create the temp table with the same structure as my table because the majority of the time my process fails is because the AS400 has Char data when it should be Decimal

My Solution:

Select Top 1 *

Into #t

From @.TableName

Truncate Table #t

--Insert temp data from sites

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.California.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Nevada.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Arizona.@.TableName

Truncate Table @.TableName

--Insert data to Production

Insert Into @.TableName

Select *

From #t

Can I generate a SQL CREATE script for a table that exists in the db automatical

I'm trying to figure out what the easiest/best way to transfer just one table from one sql server to another. Any help would be appreciated. Thanks!you can use the query analyzer, select database --> user tables --> select correct table --> right click --> script object to new window as -->> create. The query analyzer will generate a script that will generate your table.|||Thanks for the quick reply!
However, I can't seem to find the "user tables-->select correct table" option in the query analyzer?
Thanks again!|||At the left side is the object browser. If you cannot see, push the F8 button. You see at the top your server and below the databases in that server. From that point on it's like the Windows Explorer. When you see the user tables you can select one and right click on it for the scripting.|||Is this a SQL 2000 thing? I'm on SQL 7.0|||don't know, never used sql 7. Something else you can try: in the enterprise manager, select the table you want to script. Right click on it, select all tasks and then generate script. A popup will appear and if you want you can set some preferences, but just pushing OK is sufficient. Hopefully this is in sql 7|||You can access this through enterprise manager by right-clicking on the table and selecting All Tasks, Generate SQL Script.

Note that this does not copy the actual data. To do that you will need to use DTS.

blindman

Can I Force SQL to Accept INSERT List of Values Less Than Number of Columns?

I have a canned application that does INSERTs with lists of values without
column lists. The table has one additional (uniqueidentifier) column for merge
replication, so the application is inserting 7 values, but there are 8
columns. Is there a way to tell SQL to accept it anyway and just fill the
columns from left to right until it runs out of data? I hope so, because I
have no access to the source code.
--EricHello Eric. you could try with renaming that table (the one you insert in)
and creating a view with old name of the table you just renamed. In the view
definition specify all fields from the renamed table except the one that you
added for replication(uniqueidentifier) .
Hope this works,
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hr
"Eric Robinson" <eric@._nospam_nvipa.com> wrote in message
news:CFN379450577312037@.news.microsoft.com...
> I have a canned application that does INSERTs with lists of values without
> column lists. The table has one additional (uniqueidentifier) column for
merge
> replication, so the application is inserting 7 values, but there are 8
> columns. Is there a way to tell SQL to accept it anyway and just fill the
> columns from left to right until it runs out of data? I hope so, because I
> have no access to the source code.
> --Eric
>|||If there isn't a column list specified in the INSERT then the number of
columns in the table must match the number of columns in the INSERT
statement (less the IDENTITY column, if any).
You could set a default for the uniqueidentifier column, rename the table
and then create a view under the original name containing all except the
extra column:
CREATE TABLE newname (a INTEGER PRIMARY KEY, b INTEGER NOT NULL, c
UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() ...)
CREATE VIEW oldname
AS
SELECT a,b
FROM newname
Then find the programmer and make him fix his code.
--
David Portas
--
Please reply only to the newsgroup
--|||"Tomislav Kralj" <tomislav.kralj1@.zg.tel.hr> wrote in message
news:bpi4j6$vq0$1@.sunce.iskon.hr...
> Hello Eric. you could try with renaming that table (the one you insert in)
> and creating a view with old name of the table you just renamed. In the
view
> definition specify all fields from the renamed table except the one that
you
> added for replication(uniqueidentifier) .
Oh, and i forgot. create view with VIEW_METADATA option !!!
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hrsql

Can I force SQL Server to use the CONTAINS operator first?

If I do the query below, SQL Server does a table scan (thousands of rows) for fn_TestCol(), then evaluates the CONTAINS clause:

SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
AND fn_TestCol(col1) = 0

How can I force it to evaluate CONTAINS clause, which returns only a few rows, first? The best I've come up with is this:

SELECT sub.col1, sub.col2
FROM (
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
) sub
WHERE fn_TestCol(sub.col1) = 0

It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.

Actually CONTAINS is a predicate that can be used with the WHERE clause there are two versions of it defined by ANSI SQL and implemented by Microsoft CONTAINS and CONTAINSTABLE, the other two FULLTEXT predicates are FREETEXT and FREETEXTTBALE, try the link below for details. Hope this helps.

http://technet.microsoft.com/en-us/library/ms187787.aspx

|||

Hi Caddre,

Thanks for the reply -- but that didn't seem to have anything to do with my question!

|||

(It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.)

It does because since CONTAINS is a predicate and not a clause what you can force it to do is determined by the restrictions and limitations listed in the link I provided.

|||no, you will have no control over the freetext functionality as in SQL Server 2005 the query in FT searches still is not connected to the query engine and therefore cannot be optimized and tweaked in your requested way.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

OK, I found a way to do it:

DECLARE @.ftQuery nvarchar(32)

SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), @.ftQuery)
AND fn_TestCol(col1) = 0

OPTION (OPTIMIZE FOR(@.ftQuery = 'foo'))

This structure allows the optimizer to make assumptions about what might be in @.ftQuery -- so it correctly assumes that the CONTAINS predicate will be a faster starting point than the udf, and runs with the right plan.

|||

_jesse,
Interesting.. this as long been a "by design" problem with SQL FTS since SQL Server 7.0. You never said which version of SQL Server you are using, but I'm assuming it is SQL 2000 and not SQL Server 2005. Correct? Can you provide more details on your UDF fn_TestCol? How many rows do you have in your real table or your myTable example?

Thanks,
John

John T. Kane
Search Evangelist, Intellisearch
email: john.kane@.intellisearch.no

|||

Hi John,

It is SQL 2005.

The problem, as I understand it, is this: when you pass a parameter to CONTAINS, as in

CONTAINS(col1, @.searchText)

the optimizer isn't able to recognize that the CONTAINS operator is a good place to start. The execution plan changes if you replace @.searchText with a literal string, as in:

CONTAINS(col1, 'foo')

In my case, the table being scanned was relatively small (20,000 rows), but the UDF is slow -- it's doing a bunch of string manipulation & comparison. Since the CONTAINS clause is likely to be very selective, the query is very fast if you start with CONTAINS, then run the UDF on the few rows matching the fts, but very slow if you do it the other way around.

Declaring the @.searchText parameter as nvarchar, then adding the OPTIMIZE FOR hint gets it to consistently run in the right order.

can I export view result to excel ?

Good morning all,

I created a view that selects the rows of the records from a table that matches where condition.
I need to export the records to excel so I could send it out...
what is the best way to do?
Please tell me detail, I don't know much about SQL 2000, still learning.

Thank you,
YanorooUse the DTS wizard (import and export data in start menu->Microsoft SQL server).

The wizard will ask you the information needed in order to complete the operation. Select the MS-Excel driver as a destination (as well as the output filename), choose the first options (export tables an views) and select your view in particular.

Shouldn't be too hard.|||yes, I was able to do that!
Thank you!

Can I encrypt a field of a table using stored procedure?

Can I encrypt a field of a table using stored procedure in SQL Server 2005 Express Edition? And also, how to decrypt it through stored procedure?

Using the encryption functions.

In Books Online, look up the topic: Encryption, sub-topic: Functions.

You can download a version of Books Online for SQLServer Express from:

SQL Server 2005 Express Books Online Express Edition
http://msdn2.microsoft.com/en-us/library/ms165706.aspx

Can I dynamically change the width of objects?

I have a problem using the table object. I have columns that I sometimes want to show, and I want the page to run off the edge and be two pages. However, usually I'm hiding many of the columns and the table "shrinks" enough to fit on one page. When this happens, the table looks right on the first page, but I get blank pages after it, because the report itself will not shrink.
Is there a way to dynamically change the size of the table and report so that it doesn't print blank pages if it doesn't need to?
Thanks,
John
Unfortunately, there is no way to shrink the body size of the report. This is under consideration for a future release.|||

We had same situation where user would select few columns out of some possible 50 data columns.

We ended with doing some logic programmatically.

1) GetReportDefinition() of report containing 50 columns

2) As rdl from #1 is XML, we manipluate the xml by deleting the unselected columns and reordering them as per selected display order

3) publish the new report with unique name to a temp folder in reporing server

4) Now generate the report

5) then delete the report from temp folder

|||

We have the same issue with a client where a dense grid in 12.5 in body with borders it is 13in. The client uses IE6.

I read that IE7 supports Shrink-to-fit. Would that work with SSRS reports pending the Shrink-to-fit functionality printing in SSRS?

Thanks

Can I dynamically change the width of objects?

I have a problem using the table object. I have columns that I sometimes want to show, and I want the page to run off the edge and be two pages. However, usually I'm hiding many of the columns and the table "shrinks" enough to fit on one page. When this happens, the table looks right on the first page, but I get blank pages after it, because the report itself will not shrink.
Is there a way to dynamically change the size of the table and report so that it doesn't print blank pages if it doesn't need to?
Thanks,
John
Unfortunately, there is no way to shrink the body size of the report. This is under consideration for a future release.|||

We had same situation where user would select few columns out of some possible 50 data columns.

We ended with doing some logic programmatically.

1) GetReportDefinition() of report containing 50 columns

2) As rdl from #1 is XML, we manipluate the xml by deleting the unselected columns and reordering them as per selected display order

3) publish the new report with unique name to a temp folder in reporing server

4) Now generate the report

5) then delete the report from temp folder

|||

We have the same issue with a client where a dense grid in 12.5 in body with borders it is 13in. The client uses IE6.

I read that IE7 supports Shrink-to-fit. Would that work with SSRS reports pending the Shrink-to-fit functionality printing in SSRS?

Thanks

Can I dynamically change the width of objects?

I have a problem using the table object. I have columns that I sometimes want to show, and I want the page to run off the edge and be two pages. However, usually I'm hiding many of the columns and the table "shrinks" enough to fit on one page. When this happens, the table looks right on the first page, but I get blank pages after it, because the report itself will not shrink.
Is there a way to dynamically change the size of the table and report so that it doesn't print blank pages if it doesn't need to?
Thanks,
John
Unfortunately, there is no way to shrink the body size of the report. This is under consideration for a future release.|||

We had same situation where user would select few columns out of some possible 50 data columns.

We ended with doing some logic programmatically.

1) GetReportDefinition() of report containing 50 columns

2) As rdl from #1 is XML, we manipluate the xml by deleting the unselected columns and reordering them as per selected display order

3) publish the new report with unique name to a temp folder in reporing server

4) Now generate the report

5) then delete the report from temp folder

|||

We have the same issue with a client where a dense grid in 12.5 in body with borders it is 13in. The client uses IE6.

I read that IE7 supports Shrink-to-fit. Would that work with SSRS reports pending the Shrink-to-fit functionality printing in SSRS?

Thanks

Can I do this without a cursor?

Consider a table called Target with a character column called StringColumn.
I
also have a lookup table called ToReplace which contains 2 columns, OldValue
and NewValue. I'm trying to use the replace function to update the
StringColumn value in the Target table, finding the text in OldValue and
replacing it with the text in NewValue from the ToReplace table. The update
I'm using just grabs the first record from the ToReplace table. Can anyone
think of a way to do this in one update statement? Thanks in advance. Here i
s
some sloppy ddl, but it gives you the idea:
Create table ToReplace
(OldValue varchar(10),
NewValue varchar(10))
insert into ToReplace
values
('old1', 'new1')
insert into ToReplace
values
('old2', 'new2')
insert into ToReplace
values
('old3', 'new3')
Create table Target
(StringColumn varchar(50))
insert into target
values
('old1 some other text old3')
insert into target
values
('old3 old2 some other text')
select * from target
Update target
set StringColumn = replace(StringColumn, OldValue, NewValue)
from ToReplace
select * from targetI'm . Why are you just using a simple update
statement against the table instead of all this insert
and create table stuff?
Update target
set StringColumn = replace(StringColumn, OldValue, NewValue)
where some condition is true
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:89BB53E2-C2BA-4BB5-9533-A940FFC3B403@.microsoft.com...
> Consider a table called Target with a character column called
> StringColumn. I
> also have a lookup table called ToReplace which contains 2 columns,
> OldValue
> and NewValue. I'm trying to use the replace function to update the
> StringColumn value in the Target table, finding the text in OldValue and
> replacing it with the text in NewValue from the ToReplace table. The
> update
> I'm using just grabs the first record from the ToReplace table. Can anyone
> think of a way to do this in one update statement? Thanks in advance. Here
> is
> some sloppy ddl, but it gives you the idea:
> Create table ToReplace
> (OldValue varchar(10),
> NewValue varchar(10))
> insert into ToReplace
> values
> ('old1', 'new1')
> insert into ToReplace
> values
> ('old2', 'new2')
> insert into ToReplace
> values
> ('old3', 'new3')
>
> Create table Target
> (StringColumn varchar(50))
> insert into target
> values
> ('old1 some other text old3')
> insert into target
> values
> ('old3 old2 some other text')
>
> select * from target
> Update target
> set StringColumn = replace(StringColumn, OldValue, NewValue)
> from ToReplace
> select * from target
>|||Now I'm . That insert and create table stuff is the ddl. If you run
it all you'll see my problem.
"Robbe Morris [C# MVP]" wrote:

> I'm . Why are you just using a simple update
> statement against the table instead of all this insert
> and create table stuff?
> Update target
> set StringColumn = replace(StringColumn, OldValue, NewValue)
> where some condition is true
>
> --
> Robbe Morris - 2004-2006 Microsoft MVP C#
> Earn money answering .NET questions
> http://www.eggheadcafe.com/forums/merit.asp
>
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:89BB53E2-C2BA-4BB5-9533-A940FFC3B403@.microsoft.com...
>
>|||The problem you have run into relates to why UPDATE has no FROM clause
in standard SQL.
A row in the table you are updating matches more than one row in the
table you are joining it to. However, the row you are updating is
always the same "before image" of that row. So one matching ToReplace
changes old1, another changes old2, but they BOTH change the ORIGINAL
image of the row in Target. So, the row in Target gets updated more
than once, but the result is any (unpredictable) ONE of the updates,
not all of them together.
I would simply run each value to be changed as a single UPDATE, with
hard-coded values, rather than use any sort of ToReplace table.
Otherwise you need to work in some sort of loop to apply only one
ToReplace row at a time.
Also, it would have been a good idea to add a WHERE clause to your
UPDATE:
where StringColumn like '%' + OldValue + '%'
Roy Harvey
Beacon Falls, CT
On Thu, 9 Mar 2006 13:39:27 -0800, sqlboy2000
<sqlboy2000@.discussions.microsoft.com> wrote:

>Consider a table called Target with a character column called StringColumn.
I
>also have a lookup table called ToReplace which contains 2 columns, OldValu
e
>and NewValue. I'm trying to use the replace function to update the
>StringColumn value in the Target table, finding the text in OldValue and
>replacing it with the text in NewValue from the ToReplace table. The update
>I'm using just grabs the first record from the ToReplace table. Can anyone
>think of a way to do this in one update statement? Thanks in advance. Here
is
>some sloppy ddl, but it gives you the idea:
>Create table ToReplace
>(OldValue varchar(10),
>NewValue varchar(10))
>insert into ToReplace
>values
>('old1', 'new1')
>insert into ToReplace
>values
>('old2', 'new2')
>insert into ToReplace
>values
>('old3', 'new3')
>
>Create table Target
>(StringColumn varchar(50))
>insert into target
>values
>('old1 some other text old3')
>insert into target
>values
>('old3 old2 some other text')
>
>select * from target
>Update target
>set StringColumn = replace(StringColumn, OldValue, NewValue)
>from ToReplace
>select * from target
>|||Thanks Robbe, I didn't see any elegant solution to this either. I'll just
loop through it, it's a nightly run, so it's not the end of the world.
"sqlboy2000" wrote:
> Now I'm . That insert and create table stuff is the ddl. If you ru
n
> it all you'll see my problem.
> "Robbe Morris [C# MVP]" wrote:
>|||Sorry, got your name wrong. Thank you Roy
"Roy Harvey" wrote:

> The problem you have run into relates to why UPDATE has no FROM clause
> in standard SQL.
> A row in the table you are updating matches more than one row in the
> table you are joining it to. However, the row you are updating is
> always the same "before image" of that row. So one matching ToReplace
> changes old1, another changes old2, but they BOTH change the ORIGINAL
> image of the row in Target. So, the row in Target gets updated more
> than once, but the result is any (unpredictable) ONE of the updates,
> not all of them together.
> I would simply run each value to be changed as a single UPDATE, with
> hard-coded values, rather than use any sort of ToReplace table.
> Otherwise you need to work in some sort of loop to apply only one
> ToReplace row at a time.
> Also, it would have been a good idea to add a WHERE clause to your
> UPDATE:
> where StringColumn like '%' + OldValue + '%'
> Roy Harvey
> Beacon Falls, CT
> On Thu, 9 Mar 2006 13:39:27 -0800, sqlboy2000
> <sqlboy2000@.discussions.microsoft.com> wrote:
>
>

Can I do this by MDX?

My fact table has a field QtyReleased. I would like to know the number of
records with QtyReleased > 1000.
Can we do this in SQL 2005 by MDX?
GuangmingI believe you can, but do you really need to do this with MDX? Why cant you
handle this on transactional side? Just make a field that contains 1 if
QtyReleased > 1000 and 0 if not. Then all you have to do is make a measure
from that field with a simple sum aggregate.
If you really need to do this with MDX look at the count functions..
MC
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com>
wrote in message news:6AAD5BCE-C77C-4D7E-BC0A-328628576F49@.microsoft.com...
> My fact table has a field QtyReleased. I would like to know the number of
> records with QtyReleased > 1000.
> Can we do this in SQL 2005 by MDX?
> Guangming
>|||MC is right, if you want a "record" count, add a calculated field on the
relational side and create a simple measure in the cube.
If however you want to calculate which hierarchy members have
QtyReleased > 1000, you would do this via MDX.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||I think the problem is coming from QtyReleased and Measure Count in same
measure dimention (fact table).
The rule is changing (QtyReleased > 1000 - > QtyReleased > 1500). I would
like to do it by MDX.
Count() does not take any condition like IIF. I tried both and not working.
Do you have any more thoughts?
Thanks,
Guangming
"Darren Gosbell" wrote:

> MC is right, if you want a "record" count, add a calculated field on the
> relational side and create a simple measure in the cube.
> If however you want to calculate which hierarchy members have
> QtyReleased > 1000, you would do this via MDX.
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>|||I still think MC's original suggestion is probably what you are after.
What you do is to create a view over your fact table and use the view in
your cube instead of using the fact table directly.
The view would look something like the following:
SELECT
..
<column list>
..
, CASE WHEN QtyReleased > 1000 THEN 1 ELSE 0 END AS QtyOverThreashold
FROM FactTable
Then you create a simple sum based measure over the QtyOverThreashold
column from the view.
If still think you need to do this in MDX (and you may - it's hard to
fully understand your situation over a newsgroup ). Then you need to
use the FILTER() function to give you the IIF type logic.
Assuming that you want to know the number of products with QtyReleased >
1000 the MDX would look something like this...
COUNT(
FILTER(
DESCENDANTS([Products].CurrentMember,,LEAVES)
,[Measures].[QtyReleased] > 1000
)
)
This gets the leaf level descendants of the currently selected member of
the product dimension and counts the number of members with QtyReleased
> 1000
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||> I tried the Count() one, which did not work. In my case
> [Measures].[QtyReleased] , and [Measures].[MeasureCount] a
re on Fact table
> as Measures, not as Dimensions.
> So I guess it does not work.
>
In that case just go with my first suggestion of creating a view with a
calculated column and create a measure in the cube off that.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Can I do this by MDX?

My fact table has a field QtyReleased. I would like to know the number of
records with QtyReleased > 1000.
Can we do this in SQL 2005 by MDX?
Guangming
I believe you can, but do you really need to do this with MDX? Why cant you
handle this on transactional side? Just make a field that contains 1 if
QtyReleased > 1000 and 0 if not. Then all you have to do is make a measure
from that field with a simple sum aggregate.
If you really need to do this with MDX look at the count functions..
MC
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
wrote in message news:6AAD5BCE-C77C-4D7E-BC0A-328628576F49@.microsoft.com...
> My fact table has a field QtyReleased. I would like to know the number of
> records with QtyReleased > 1000.
> Can we do this in SQL 2005 by MDX?
> Guangming
>
|||MC is right, if you want a "record" count, add a calculated field on the
relational side and create a simple measure in the cube.
If however you want to calculate which hierarchy members have
QtyReleased > 1000, you would do this via MDX.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||I think the problem is coming from QtyReleased and Measure Count in same
measure dimention (fact table).
The rule is changing (QtyReleased > 1000 - > QtyReleased > 1500). I would
like to do it by MDX.
Count() does not take any condition like IIF. I tried both and not working.
Do you have any more thoughts?
Thanks,
Guangming
"Darren Gosbell" wrote:

> MC is right, if you want a "record" count, add a calculated field on the
> relational side and create a simple measure in the cube.
> If however you want to calculate which hierarchy members have
> QtyReleased > 1000, you would do this via MDX.
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
|||I still think MC's original suggestion is probably what you are after.
What you do is to create a view over your fact table and use the view in
your cube instead of using the fact table directly.
The view would look something like the following:
SELECT
...
<column list>
...
, CASE WHEN QtyReleased > 1000 THEN 1 ELSE 0 END AS QtyOverThreashold
FROM FactTable
Then you create a simple sum based measure over the QtyOverThreashold
column from the view.
If still think you need to do this in MDX (and you may - it's hard to
fully understand your situation over a newsgroup ). Then you need to
use the FILTER() function to give you the IIF type logic.
Assuming that you want to know the number of products with QtyReleased >
1000 the MDX would look something like this...
COUNT(
FILTER(
DESCENDANTS([Products].CurrentMember,,LEAVES)
,[Measures].[QtyReleased] > 1000
)
)
This gets the leaf level descendants of the currently selected member of
the product dimension and counts the number of members with QtyReleased
> 1000
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||> I tried the Count() one, which did not work. In my case
> [Measures].[QtyReleased] , and [Measures].[MeasureCount] are on Fact table
> as Measures, not as Dimensions.
> So I guess it does not work.
>
In that case just go with my first suggestion of creating a view with a
calculated column and create a measure in the cube off that.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Can I do This

Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
AhmedAhmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Sorry,correction
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=C.Code)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahmed,
Try:
select code, name, monthdate, code,
(select sum(amount)
from b
where b.code = a.code
and month(b.date) = a.monthdate) as total
from a;
If you're after applying a table expression querying B and returning a
rowset, to each row from A, there's no set-based way to achieve this in SQL
Server 2000.
You'll have to use itterative logic. SQL Server 2005 solves this by
introducing the APPLY operator, e.g.,
-- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each Store
USE pubs;
SELECT ST.stor_id, CA.*
FROM dbo.Stores AS ST
CROSS APPLY
(SELECT TOP(2) ord_num, title_id, ord_date, qty
FROM dbo.Sales AS SL
WHERE SL.stor_id = ST.stor_id AND qty >= 10
ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on
a value in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON
A.Code=C.Code
thanks in advance
Ahmed|||I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahemd
Yes , you are right , please take a look at Itzik's solution
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=B.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:u5bldm1lFHA.32
88@.TK2MSFTNGP09.phx.gbl...
I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Thanks BG
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OLrJph1lFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Ahmed,
> Try:
> select code, name, monthdate, code,
> (select sum(amount)
> from b
> where b.code = a.code
> and month(b.date) = a.monthdate) as total
> from a;
> If you're after applying a table expression querying B and returning a
> rowset, to each row from A, there's no set-based way to achieve this in
> SQL Server 2000.
> You'll have to use itterative logic. SQL Server 2005 solves this by
> introducing the APPLY operator, e.g.,
> -- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each
> Store
> USE pubs;
> SELECT ST.stor_id, CA.*
> FROM dbo.Stores AS ST
> CROSS APPLY
> (SELECT TOP(2) ord_num, title_id, ord_date, qty
> FROM dbo.Sales AS SL
> WHERE SL.stor_id = ST.stor_id AND qty >= 10
> ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
> news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Dear all
> I need to make a correlated subquery that has the where clause depend
> on a value in the master query, ex
> Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
> I want to perform SQL like this
> SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
> B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C
> ON A.Code=C.Code
>
> thanks in advance
> Ahmed
>