Deriving data from nothing?

Hi all,
I did something like this and it gave me the result as 123456.please look at this and tell me how does something be derived from a blank table(set)?

create table a ( id integer);

select case when 1 = 1
then 123456
else count(*) end as NUMBER
from a
where 1 = 0;

I tried this on mysql and one of my friends -who had told me about this- says it works same on sql server.
PM: My friend said that he saw this in one of Joe Celko's columns but I could not find that column.
-Thanks in advanceThis is one of those things that doesn't make any sense at first, but once you work through it, it makes perfect sense.

The use of Count(*) within the column list implies grouping. That forces a result set row to be returned at the aggregate level. The WHERE clause is evaluated after the (degenerate case) join, and filters out any rows that might be in the table. After the aggregation, the CASE is evaluated and the result is posted.

Funky, but it does make sense!

-PatP

deriving a new column from another derived column

In a Derived Column data flow transformation, why can't I refer to a derived column (added in that same transformation) in the expression for another derived column? It seems I am forced to chain 2 DC data flows, just to do something as conceptually simple as:

a = x + y; b = a2

On a related note: Can I define a variable that is scoped to each row? Can I bind a variable in an expression to avoid creating a new row, e.g.

let a = x + y; a2

as the expression for new row b?

Kevin Rodgers wrote:

In a Derived Column data flow transformation, why can't I refer to a derived column (added in that same transformation) in the expression for another derived column? It seems I am forced to chain 2 DC data flows, just to do something as conceptually simple as:

a = x + y; b = a2

Kevin,

God yeah. I so wish you could do that. Seems like such simple funcitonality doesn't it?

I have requested it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127010

Please please please click through and vote for it.

Kevin Rodgers wrote:

On a related note: Can I define a variable that is scoped to each row? Can I bind a variable in an expression to avoid creating a new row, e.g.

let a = x + y; a2

as the expression for new row b?

No, you can't do that!

-Jamie

|||You can't do it in derived column, but it is very easy to do in script component - the component generates the row accessors, so the amount of code you need to write is almost the same as in derived column transform.|||

I haven't used the script component before -- I haven't even written any scripts. Could you show me a simple example of generating row accessors etc. as you've suggested?

Thanks!

|||

It's a good idea Jamie.

Do keep in mind that it adds another layer of complexity. Along with some other difficulties, the user would have to specify an order of execution for the expressions, and the UI would need a way too enable and maintain that. Chaining multiplie derived columns, while not as convenient or as pretty to look at, makes it abundantly clear what order the intermediate expressions occur in and eases some usability concerns.

It's something to look at for the future, though, certainly. Keep the suggestions coming!

Thanks
Mark

|||

Kevin Rodgers wrote:

I haven't used the script component before -- I haven't even written any scripts. Could you show me a simple example of generating row accessors etc. as you've suggested?

Thanks!

Just add a script transform where you would use Derived Column transform, select Transform in the first dialog. Check the columns you want to use in your expressions. Go to Inputs and Outputs tab, add output columns to Output 0.

Now edit the script, and type your code inside Input0_ProcessInputRow function. I quickly setup a "validation" for AdwentureWorks DB (the transform adds two colums CalculatedColumn and TotalOK to each row):

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim t As Decimal
t = Row.OrderQty * Row.UnitPrice * (1 - Row.UnitPriceDiscount)
Row.CalculatedColumn = t

If (t = Row.LineTotal) Then
Row.TotalOK = True
Else
Row.TotalOK = False
End If

End Sub

So the main difference in expressions is that you have to add "Row." prefix to column name :) Of course, the syntax is different, as the script transform uses full VB.NET language, which gives you ability to create temporary variables among other things.

sql

Derived types and backend.

I want the server side (sql and business logic) to know about one type. And
the client app(s) could derive there own types as needed but also store
their derivations so they can deserialize them. Is there a pattern for
this? What I am thinking now is simple example like:
public interface IVehicle
{
string Name
{
get;
set;
}
string Type
{
get;
set;
}
string Guid
{
get;
set;
}
public class Vehicle : IVehicle
{
private string name;
private string type;
private string data;
public string Name { get/set imp }
public string Type { get/set imp } // Derived type name. Used by
client to know how to deserilize Data.
public string Data { get/set imp} // Derived types xml.
}
So server knows about the Vehicle type and that is all. It can store three
columns: Name, Type, and Data.
If a client just wants to use Vehicle(s) then it is all set. However, it
may want to derive a Corvette or some other vehicle from base like so.
public class Corvette : IVehicle
{
private string name;
private string type;
private string data;
// Derived fields.
private string color;
public string Name { get/set imp }
public string Type { get/set imp }
public string Data { get/set imp}
public string Color { get/set imp}
public Corvette() { }
public Corvette(Vehicle vehicle) { //create a corvette from a vehicle. }
}
So I can get Vehicles from the server and create Corvettes on the client
side. However I need to store back a Corvette on the server, but the server
only knows about Vehicle type. So I am thinking serialize the Corvette type
into xml string, create a new Vehicle using same Name. Set vehicle.Type to
"Corvette" and store xml string in vehicle.Data. Now send the Vehicle type
to server for storage in SQL using the 3 columns in Vehicle. Now if the
client needs Corvette type, it gets the Vehicle, checks the Type and
deserializes the Data string into Corvette type and uses it. So that is the
round trip. Not pretty, but only way I can figure so far to do it. Any
ideas? TIA
William Stacey [MVP]
William Stacey [MVP]
There was an MSDN article written by Andrew Conrad that covers a scenario
very close to what you want to do. He uses an xml overflow column to store
the additional properties of the subclass.
"Death, Taxes, and Relational Databases, Part 1"
http://msdn.microsoft.com/library/de...ml04212003.asp
Specifically the section entitled: "Extending the Business Objects"
"William Stacey [MVP]" wrote:

> I want the server side (sql and business logic) to know about one type. And
> the client app(s) could derive there own types as needed but also store
> their derivations so they can deserialize them. Is there a pattern for
> this? What I am thinking now is simple example like:
> public interface IVehicle
> {
> string Name
> {
> get;
> set;
> }
> string Type
> {
> get;
> set;
> }
> string Guid
> {
> get;
> set;
> }
> public class Vehicle : IVehicle
> {
> private string name;
> private string type;
> private string data;
> public string Name { get/set imp }
> public string Type { get/set imp } // Derived type name. Used by
> client to know how to deserilize Data.
> public string Data { get/set imp} // Derived types xml.
> }
> So server knows about the Vehicle type and that is all. It can store three
> columns: Name, Type, and Data.
> If a client just wants to use Vehicle(s) then it is all set. However, it
> may want to derive a Corvette or some other vehicle from base like so.
> public class Corvette : IVehicle
> {
> private string name;
> private string type;
> private string data;
> // Derived fields.
> private string color;
> public string Name { get/set imp }
> public string Type { get/set imp }
> public string Data { get/set imp}
> public string Color { get/set imp}
> public Corvette() { }
> public Corvette(Vehicle vehicle) { //create a corvette from a vehicle. }
> }
> So I can get Vehicles from the server and create Corvettes on the client
> side. However I need to store back a Corvette on the server, but the server
> only knows about Vehicle type. So I am thinking serialize the Corvette type
> into xml string, create a new Vehicle using same Name. Set vehicle.Type to
> "Corvette" and store xml string in vehicle.Data. Now send the Vehicle type
> to server for storage in SQL using the 3 columns in Vehicle. Now if the
> client needs Corvette type, it gets the Vehicle, checks the Type and
> deserializes the Data string into Corvette type and uses it. So that is the
> round trip. Not pretty, but only way I can figure so far to do it. Any
> ideas? TIA
> --
> William Stacey [MVP]
> --
> William Stacey [MVP]
>
>
|||Thanks Todd. :-)
William Stacey [MVP]
"Todd Pfleiger [MSFT]" <ToddPfleigerMSFT@.discussions.microsoft.com> wrote in
message news:3B2852EA-495E-4D03-8CD7-10646EEF42E4@.microsoft.com...[vbcol=seagreen]
> There was an MSDN article written by Andrew Conrad that covers a scenario
> very close to what you want to do. He uses an xml overflow column to store
> the additional properties of the subclass.
> "Death, Taxes, and Relational Databases, Part 1"
> http://msdn.microsoft.com/library/de...ml04212003.asp
> Specifically the section entitled: "Extending the Business Objects"
>
> "William Stacey [MVP]" wrote:

Derived types and backend.

I want the server side (sql and business logic) to know about one type. And
the client app(s) could derive there own types as needed but also store
their derivations so they can deserialize them. Is there a pattern for
this? What I am thinking now is simple example like:
public interface IVehicle
{
string Name
{
get;
set;
}
string Type
{
get;
set;
}
string Guid
{
get;
set;
}
public class Vehicle : IVehicle
{
private string name;
private string type;
private string data;
public string Name { get/set imp }
public string Type { get/set imp } // Derived type name. Used by
client to know how to deserilize Data.
public string Data { get/set imp} // Derived types xml.
}
So server knows about the Vehicle type and that is all. It can store three
columns: Name, Type, and Data.
If a client just wants to use Vehicle(s) then it is all set. However, it
may want to derive a Corvette or some other vehicle from base like so.
public class Corvette : IVehicle
{
private string name;
private string type;
private string data;
// Derived fields.
private string color;
public string Name { get/set imp }
public string Type { get/set imp }
public string Data { get/set imp}
public string Color { get/set imp}
public Corvette() { }
public Corvette(Vehicle vehicle) { //create a corvette from a vehicle. }
}
So I can get Vehicles from the server and create Corvettes on the client
side. However I need to store back a Corvette on the server, but the server
only knows about Vehicle type. So I am thinking serialize the Corvette type
into xml string, create a new Vehicle using same Name. Set vehicle.Type to
"Corvette" and store xml string in vehicle.Data. Now send the Vehicle type
to server for storage in SQL using the 3 columns in Vehicle. Now if the
client needs Corvette type, it gets the Vehicle, checks the Type and
deserializes the Data string into Corvette type and uses it. So that is the
round trip. Not pretty, but only way I can figure so far to do it. Any
ideas? TIA
--
William Stacey [MVP]
William Stacey [MVP]There was an MSDN article written by Andrew Conrad that covers a scenario
very close to what you want to do. He uses an xml overflow column to store
the additional properties of the subclass.
"Death, Taxes, and Relational Databases, Part 1"
http://msdn.microsoft.com/library/d.../>
4212003.asp
Specifically the section entitled: "Extending the Business Objects"
"William Stacey [MVP]" wrote:

> I want the server side (sql and business logic) to know about one type. A
nd
> the client app(s) could derive there own types as needed but also store
> their derivations so they can deserialize them. Is there a pattern for
> this? What I am thinking now is simple example like:
> public interface IVehicle
> {
> string Name
> {
> get;
> set;
> }
> string Type
> {
> get;
> set;
> }
> string Guid
> {
> get;
> set;
> }
> public class Vehicle : IVehicle
> {
> private string name;
> private string type;
> private string data;
> public string Name { get/set imp }
> public string Type { get/set imp } // Derived type name. Used by
> client to know how to deserilize Data.
> public string Data { get/set imp} // Derived types xml.
> }
> So server knows about the Vehicle type and that is all. It can store thre
e
> columns: Name, Type, and Data.
> If a client just wants to use Vehicle(s) then it is all set. However, it
> may want to derive a Corvette or some other vehicle from base like so.
> public class Corvette : IVehicle
> {
> private string name;
> private string type;
> private string data;
> // Derived fields.
> private string color;
> public string Name { get/set imp }
> public string Type { get/set imp }
> public string Data { get/set imp}
> public string Color { get/set imp}
> public Corvette() { }
> public Corvette(Vehicle vehicle) { //create a corvette from a vehicle.
}
> }
> So I can get Vehicles from the server and create Corvettes on the client
> side. However I need to store back a Corvette on the server, but the serv
er
> only knows about Vehicle type. So I am thinking serialize the Corvette ty
pe
> into xml string, create a new Vehicle using same Name. Set vehicle.Type t
o
> "Corvette" and store xml string in vehicle.Data. Now send the Vehicle typ
e
> to server for storage in SQL using the 3 columns in Vehicle. Now if the
> client needs Corvette type, it gets the Vehicle, checks the Type and
> deserializes the Data string into Corvette type and uses it. So that is t
he
> round trip. Not pretty, but only way I can figure so far to do it. Any
> ideas? TIA
> --
> William Stacey [MVP]
> --
> William Stacey [MVP]
>
>|||Thanks Todd. :-)
William Stacey [MVP]
"Todd Pfleiger [MSFT]" <ToddPfleigerMSFT@.discussions.microsoft.com> wrote in
message news:3B2852EA-495E-4D03-8CD7-10646EEF42E4@.microsoft.com...
> There was an MSDN article written by Andrew Conrad that covers a scenario
> very close to what you want to do. He uses an xml overflow column to store
> the additional properties of the subclass.
> "Death, Taxes, and Relational Databases, Part 1"
> http://msdn.microsoft.com/library/d...
l04212003.asp
> Specifically the section entitled: "Extending the Business Objects"
>
> "William Stacey [MVP]" wrote:
>

Derived Tables and joining to them

I am struggling with some syntax. I have created a couple of derived tables
and now want to LEFT OUTER JOIN to them. Can someone help me?
Thanks in advance.
Here's the SQL...
SELECT DERIVE1A.column_1,
DERIVE1A.column_2,
DERIVE1A.column_3,
DERIVE1A.column_4,
DERIVE1A.column_5,
DERIVE2A.column_1,
DERIVE2A.column_2,
DERIVE2A.column_3,
DERIVE2A.column_4,
DERIVE2A.column_5
FROM #tbl_Export tbl_Export,
(SELECT column_1,
column_2,
column_3,
column_4,
column_5
FROM #tbl_Export tbl_export_1
WHERE column_x = 'f') DERIVE1,
(SELECT column_1,
column_2,
column_3,
column_4,
column_5
FROM #tbl_Export tbl_export_1
WHERE column_x = 's') DERIVE2,
LEFT OUTER JOIN DERIVE1 DERIVE1A
ON tbl_Export.key_column = DERIVE1A.key_column
LEFT OUTER JOIN DERIVE2 DERIVE2A
ON tbl_Export.key_column = DERIVE2A.key_column
wnfisbaStart by checking the syntax and examples from SQL Server Books Online.
Based on the sample code you posted, you could re-write it along the lines
of:
SELECT *
FROM tbl t1
LEFT OUTER JOIN
( SELECT col1, col2, ...... ) derived_1
ON t1.key = derived_1.col1
LEFT OUTER JOIN
( SELECT col1, col2, ...... ) derived_2
ON t1.key = derived_2.col2 ;
Anith

Derived Tables

OK...I know how to write a query to return for example :

All the people that ordered X and Y

but how do I write one for:

All the people that ordered X but not Y?

Thanks,
TreyO yea...this is how i did the first part

SELECT DISTINCT c.Company
FROM Customers as c
JOIN
 (SELECT CustomerID
 FROM Orders o
 JOIN [Order Details] od
 ON o.OrderID = od.OrderID
 JOIN products p
 ON od.ProductID = p.ProductID
 WHERE p.ProductName = 'X') as temp1
 ON c.CustomerID = temp1.CustomerID
JOIN
 (SELECT CustomerID
 FROM Orders o
 JOIN [Order Details] od
 ON o.OrderID = od.OrderID
 JOIN products p
 ON od.ProductID = p.ProductID
 WHERE p.ProductName = 'Y') as temp2
 ON c.CustomerID = temp2.CustomerID

Derived Table Problem

I've created this:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
Count(dt.Hits) as 'Submissions'
FROM
CME_TBL c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID

ORDER BY
c.ProjectID

and I get this:

ProjectID Registrants Submissions
--- ---- ----
adv_104699 99
adv_1047185 185
adv_110566 66
boh_107134 34

Instead, I want this:

ProjectID Registrants Submissions
--- ---- ----
adv_104699 14
adv_1047185 82
adv_110566 17
boh_107134 12

The "ProjectID" and "Submissions" columns are produced when I run the
derived table (dt, above) as a standalone query. By the same token,
the "Project ID" and "Registrants" columns are produced when I run the
"outer" query, above.

Am I on the right track here?

TIA,

-- Bill[posted and mailed, please reply in news]

Bill (w.white@.snet.net) writes:
> I've created this:
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID

COUNT(dt.Hits) returns the number of rows where this column is not null.
I would guess that you want SUM(dt.Hits) here instead.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9462F13D69581Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Bill (w.white@.snet.net) writes:
> > I've created this:
> > SELECT
> > c.ProjectID,
> > Count(c.ID) as 'Registrants',
> > Count(dt.Hits) as 'Submissions'
> > FROM
> > CME_TBL c
> > JOIN
> > (SELECT ProjectID, Count(*) as Hits FROM CME_TBL
> > WHERE evalDate Is Not NULL OR testDate Is Not NULL
> > GROUP BY ProjectID
> > ) dt
> > ON c.ProjectID = dt.ProjectID
> COUNT(dt.Hits) returns the number of rows where this column is not null.
> I would guess that you want SUM(dt.Hits) here instead.

Using SUM(dt.Hits) yields:

ProjectID Registrants Submissions
--- -- --
adv_104699 1881
adv_1047185 2960
adv_110566 462
boh_107134 952
boh_112238 608
boh_113637 444
brw_106544 1012

which I suspect is closer to my desired result, since the value in the
Submissions column = (Registrants * Submissions) for that ProjectID;
so the proper Submissions value is "in there somewhere". My need is
for the correct Submissions value to appear within the Submissions
column:

ProjectID Registrants Submissions
--- -- --
adv_104699 19
adv_1047185 16
adv_110566 7
boh_107134 28
boh_112238 16
boh_113637 12
brw_106544 23

Happy New Year!

-- Bill|||Bill (w.white@.snet.net) writes:
> Using SUM(dt.Hits) yields:
> ProjectID Registrants Submissions
> --- -- --
> adv_1046 99 1881
> adv_1047 185 2960
> adv_1105 66 462
> boh_1071 34 952
> boh_1122 38 608
> boh_1136 37 444
> brw_1065 44 1012
> which I suspect is closer to my desired result, since the value in the
> Submissions column = (Registrants * Submissions) for that ProjectID;
> so the proper Submissions value is "in there somewhere". My need is
> for the correct Submissions value to appear within the Submissions
> column:
> ProjectID Registrants Submissions
> --- -- --
> adv_1046 99 19
> adv_1047 185 16
> adv_1105 66 7
> boh_1071 34 28
> boh_1122 38 16
> boh_1136 37 12
> brw_1065 44 23

Indeed it seems that diving the Submissions column with the Registratns
column gives the result you are asking for. That is:

SUM(dt.Hits) / COUNT(c.ID)

Moral: when you ask a question like this, it is always a good idea to
provide:

o CREATE TABLE statements of the involved tables.
o INSERT statements with sample data.
o The desired output given the sample.

With this infomation, anyone who takes a stab with your problem can post a
tested solution. Without this information, the answer you get is more or
less guesswork.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9463F1E595160Yazorman@.127.0.0.1>...
> Bill (w.white@.snet.net) writes:
> > Using SUM(dt.Hits) yields:
> > ProjectID Registrants Submissions
> > --- -- --
> > adv_1046 99 1881
> > adv_1047 185 2960
> > adv_1105 66 462
> > boh_1071 34 952
> > boh_1122 38 608
> > boh_1136 37 444
> > brw_1065 44 1012
> > which I suspect is closer to my desired result, since the value in the
> > Submissions column = (Registrants * Submissions) for that ProjectID;
> > so the proper Submissions value is "in there somewhere". My need is
> > for the correct Submissions value to appear within the Submissions
> > column:
> > ProjectID Registrants Submissions
> > --- -- --
> > adv_1046 99 19
> > adv_1047 185 16
> > adv_1105 66 7
> > boh_1071 34 28
> > boh_1122 38 16
> > boh_1136 37 12
> > brw_1065 44 23
> Indeed it seems that diving the Submissions column with the Registratns
> column gives the result you are asking for. That is:
> SUM(dt.Hits) / COUNT(c.ID)
> Moral: when you ask a question like this, it is always a good idea to
> provide:
> o CREATE TABLE statements of the involved tables.
> o INSERT statements with sample data.
> o The desired output given the sample.
> With this infomation, anyone who takes a stab with your problem can post a
> tested solution. Without this information, the answer you get is more or
> less guesswork.

Alrighty, then! Here we go:

CREATE TABLE CME_TBL_dev
(
ID int IDENTITY (1, 1) NOT NULL,
ProjectID varchar (50) NULL,
registrationDate datetime NULL DEFAULT (getdate()),
lastName varchar (60) NULL,
testDate datetime NULL,
evalDate datetime NULL
)

--------------

INSERT INTO CME_TBL_dev
(
ProjectID,
lastName,
testDate,
evalDate
)

SELECT 'pmw_1129', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'pmw_1129', 'wilkins', NULL, NULL
UNION ALL
SELECT 'pmw_1129', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'pmw_1129', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'pmw_1129', 'fife', NULL, NULL
UNION ALL
SELECT 'pmw_1129', 'fonebone', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'brw_1065', 'wilkins', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'brw_1065', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'brw_1065', 'fife', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'fonebone', NULL, NULL
UNION ALL
SELECT 'any_8930', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'any_8930', 'wilkins', NULL, NULL
UNION ALL
SELECT 'any_8930', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'any_8930', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'any_8930', 'fife', NULL, NULL
UNION ALL
SELECT 'any_8930', 'fonebone', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'hir_1093', 'wilkins', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'hir_1093', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'hir_1093', 'fife', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'fonebone', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'yth_9804', 'wilkins', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'yth_9804', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'yth_9804', 'fife', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'fonebone', NULL, NULL

---------------
-- This is the query I'm hoping I can get to yield
-- the desired results (see below).

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
Count(dt.Hits) as 'Submissions'
FROM
CME_TBL_dev c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID

ORDER BY
c.ProjectID

--------
-- The following two queries are for utility purposes.

SELECT
c.ProjectID, Count(c.ID) as 'Registrants'
FROM
CME_TBL_dev c
GROUP BY
c.ProjectID
ORDER BY
c.ProjectID

--------

SELECT
c.ProjectID, Count(c.ID) as 'Submissions'
FROM
CME_TBL_dev c
WHERE
c.evalDate Is Not NULL OR
c.testDate Is Not NULL
GROUP BY
c.ProjectID
ORDER BY
c.ProjectID

------------

What I seek is this:

ProjectID Registrants Submissions
--- ---- ----
any_89306 3
brw_10656 3
hir_10936 3
pmw_11296 3
yth_98046 3

But what I get instead is this (per the derived table query above):

ProjectID Registrants Submissions
--- ---- ----
any_89306 6
brw_10656 6
hir_10936 6
pmw_11296 6
yth_98046 6

Solving this would have major positive impact on many aspects of my
reporting efforts.

Thanks in advance!

-- Bill|||Bill (w.white@.snet.net) writes:
> -- This is the query I'm hoping I can get to yield
> -- the desired results (see below).
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL_dev c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID
> GROUP BY c.ProjectID
> ORDER BY c.ProjectID
>...
> What I seek is this:
> ProjectID Registrants Submissions
> --- ---- ----
> any_8930 6 3
> brw_1065 6 3
> hir_1093 6 3
> pmw_1129 6 3
> yth_9804 6 3

It does indeed seem that my suggest to take sum divided by count
gives the desired result:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
SUM(dt.Hits) / Count(dt.Hits) as 'Submissions'
FROM
CME_TBL_dev c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID
GROUP BY c.ProjectID
ORDER BY c.ProjectID

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9464EE208FB4EYazorman@.127.0.0.1>...
> Bill (w.white@.snet.net) writes:
> > -- This is the query I'm hoping I can get to yield
> > -- the desired results (see below).
> > SELECT
> > c.ProjectID,
> > Count(c.ID) as 'Registrants',
> > Count(dt.Hits) as 'Submissions'
> > FROM
> > CME_TBL_dev c
> > JOIN
> > (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> > WHERE evalDate Is Not NULL OR testDate Is Not NULL
> > GROUP BY ProjectID
> > ) dt
> > ON c.ProjectID = dt.ProjectID
> > GROUP BY c.ProjectID
> > ORDER BY c.ProjectID
> >...
> > What I seek is this:
> > ProjectID Registrants Submissions
> > --- ---- ----
> > any_8930 6 3
> > brw_1065 6 3
> > hir_1093 6 3
> > pmw_1129 6 3
> > yth_9804 6 3
> It does indeed seem that my suggest to take sum divided by count
> gives the desired result:
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> SUM(dt.Hits) / Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL_dev c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID
> GROUP BY c.ProjectID
> ORDER BY c.ProjectID

Solved it! I took a different approach. I think the crux of my
difficulty lay in "overprocessing" dt.Hits:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
dt.Hits as 'Submissions'
FROM
CME_TBL_dev c,
(
SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt

WHERE
c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID, dt.Hits

ORDER BY
c.ProjectID

Yields:

ProjectID Registrants Submissions
--- ---- ----
any_8930 6 3
brw_1065 6 3
hir_1093 6 3
pmw_1129 6 3
yth_9804 6 3

Thanks for priming my mental pump!

-- Bill

Derived table not updatable

I got an error as follows:
Derived table 'A' is not updatable because a column of the derived table is derived or constant.
when I tried to run this query:
update A set MonthsUnbilled =99999888
FROM (select MonthsUnbilled from dbo.vw_MasterView
WHERE (RecordID =8377396)) A
This is a simplified query in order to pinpoint the culprit. I know I don't need to use a derived table if the real query is this simple.

Thanks in advance!It would appear that either the dbo.vw_MasterView.MonthsUnbilled column is either a constant, a computed column, or derived from one of them.

What is the DDL for dbo.vw_MasterView? What is the DDL for the table that contains the column referenced in dbo.vw_MasterView.MonthsUnbilled ?

-PatP|||Thank you Pat for your response.
I can update dbo.vw_MasterView.MonthsUnbilled without using a derived table. In other words, the following query runs just fine.
update dbo.vw_MasterView set MonthsUnbilled =99999888
FROM dbo.vw_MasterView
WHERE (RecordID =8377396)
But if I use a derived table like this:
update A set MonthsUnbilled =99999888
FROM (select MonthsUnbilled from dbo.vw_MasterView
WHERE (RecordID =8377396)) A
I get that error.|||Is there either a PRIMARY KEY or a UNIQUE constraint on the table column that eventually populates dbo.vw_MasterView.RecordID ? I think that there needs to be a constraint of one of those two types to make the virtualized view (the derived table created from an existing view) updatable.

-PatP|||Pat, you're right. RecordID is the primary key and an identity field. But is this the reason why I got the error?

derived table fails, I'm doing something wrong...

I am trying to join a list of stocks with a list of (optional) prices,
getting the newest price. I am using a derived table to get the latest price
,
but when I join, I always get NULL even though there are prices...
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
pr.accountId=a.accountid
ORDER BY a.code
If you run the inner query (the derived table) for a given accountId (adding
it to the where) it works fine, returning the correct row from the prices
table. But when used as above, it always returns null for pr.
Any ideas?
MauryMaury,
The problem is that you are asking for the TOP 1 record, thus getting only
one record. Then you are comparing the pr.accountID of that one record with
a.accountID. So, there could be some rows joining, but all the other rows
from tblAccounts would not find a match.
I think you are probably try to do something like this (untested) code:
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN
(SELECT accountId, price1, pricedate, pricenote
FROM tblPrices tp1
JOIN (SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID) AS tp2
ON tp1.accountID = tp2.accountID
AND tp1.pricedate = tp2.pricedate) AS pr
ON pr.accountId=a.accountid
ORDER BY a.code
If I did not blow this, what it is trying to do is:
tp2 = Give me the accountId and maximum pricedate foroevery account
pr = Join tblPrices to tp2 to return all information that matches the
maximum pricedate for each accountId
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:0F47DDED-8044-4F41-BBDE-C5734329D153@.microsoft.com...
>I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest
> price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId
> (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury|||"Russell Fields" wrote:

> I think you are probably try to do something like this (untested) code:
This is how I did it the first time actually, but the performance was
terrible.
You know, it's about time the SQL vendors added some syntax for time series!
Is it too much to ask for NEWEST and OLDEST?
Maury|||Maury,
I feel your pain, but the right answer is better than a fast answer. (Sort
of :-)) One thing that we did not discuss is indexes and how they may help
such a query. Having said that, however, what I would probably do is create
a temporary table with the innermost tp2 select, something like:
CREATE #Temp
(accountId int,
maxpricedate datetime)
INSERT INTO #temp
SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID
-- Then use the temp table in the last select.
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN #temp t
ON a.accountId = t.accountId
JOIN tblPrices pr
ON t.accountId = pr.accountId
AND t.pricedate = pr.pricedate
ORDER BY a.code
Put an index on accountId in #temp and so forth.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6A1230A2-19DC-4D1C-A297-DC4A56C1E149@.microsoft.com...
> "Russell Fields" wrote:
>
> This is how I did it the first time actually, but the performance was
> terrible.
> You know, it's about time the SQL vendors added some syntax for time
> series!
> Is it too much to ask for NEWEST and OLDEST?
> Maury
>|||You know, I think what I should really do is create a non-temp table called
tblCurrentPrices and keep the "latest" version in there. This sort of thing
is spread all over my code, I could probably make my whole app run faster al
l
over the place...
Wait, I even have a table to put it in already. Ignore me, I'm rambling.|||On Jul 6, 8:26 pm, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest pri
ce,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId (addi
ng
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury
Hi, I made made slight changes to your qiery:
Select a.AccountID, a.code, price1 = (SELECT TOP 1 price1 FROM
tblPrices pr WHERE pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc),
pricedate = (SELECT TOP 1 pricedate FROM tblPrices WHERE
pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc)
from tblAccounts asql

derived table fails, I'm doing something wrong...

I am trying to join a list of stocks with a list of (optional) prices,
getting the newest price. I am using a derived table to get the latest price,
but when I join, I always get NULL even though there are prices...
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
pr.accountId=a.accountid
ORDER BY a.code
If you run the inner query (the derived table) for a given accountId (adding
it to the where) it works fine, returning the correct row from the prices
table. But when used as above, it always returns null for pr.
Any ideas?
MauryMaury,
The problem is that you are asking for the TOP 1 record, thus getting only
one record. Then you are comparing the pr.accountID of that one record with
a.accountID. So, there could be some rows joining, but all the other rows
from tblAccounts would not find a match.
I think you are probably try to do something like this (untested) code:
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN
(SELECT accountId, price1, pricedate, pricenote
FROM tblPrices tp1
JOIN (SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID) AS tp2
ON tp1.accountID = tp2.accountID
AND tp1.pricedate = tp2.pricedate) AS pr
ON pr.accountId=a.accountid
ORDER BY a.code
If I did not blow this, what it is trying to do is:
tp2 = Give me the accountId and maximum pricedate foroevery account
pr = Join tblPrices to tp2 to return all information that matches the
maximum pricedate for each accountId
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:0F47DDED-8044-4F41-BBDE-C5734329D153@.microsoft.com...
>I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest
> price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId
> (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury|||"Russell Fields" wrote:
> I think you are probably try to do something like this (untested) code:
This is how I did it the first time actually, but the performance was
terrible.
You know, it's about time the SQL vendors added some syntax for time series!
Is it too much to ask for NEWEST and OLDEST?
Maury|||Maury,
I feel your pain, but the right answer is better than a fast answer. (Sort
of :-)) One thing that we did not discuss is indexes and how they may help
such a query. Having said that, however, what I would probably do is create
a temporary table with the innermost tp2 select, something like:
CREATE #Temp
(accountId int,
maxpricedate datetime)
INSERT INTO #temp
SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID
-- Then use the temp table in the last select.
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN #temp t
ON a.accountId = t.accountId
JOIN tblPrices pr
ON t.accountId = pr.accountId
AND t.pricedate = pr.pricedate
ORDER BY a.code
Put an index on accountId in #temp and so forth.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6A1230A2-19DC-4D1C-A297-DC4A56C1E149@.microsoft.com...
> "Russell Fields" wrote:
>> I think you are probably try to do something like this (untested) code:
> This is how I did it the first time actually, but the performance was
> terrible.
> You know, it's about time the SQL vendors added some syntax for time
> series!
> Is it too much to ask for NEWEST and OLDEST?
> Maury
>|||You know, I think what I should really do is create a non-temp table called
tblCurrentPrices and keep the "latest" version in there. This sort of thing
is spread all over my code, I could probably make my whole app run faster all
over the place...
Wait, I even have a table to put it in already. Ignore me, I'm rambling.|||On Jul 6, 8:26 pm, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury
Hi, I made made slight changes to your qiery:
Select a.AccountID, a.code, price1 = (SELECT TOP 1 price1 FROM
tblPrices pr WHERE pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc),
pricedate = (SELECT TOP 1 pricedate FROM tblPrices WHERE
pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc)
from tblAccounts a

derived table fails, I'm doing something wrong...

I am trying to join a list of stocks with a list of (optional) prices,
getting the newest price. I am using a derived table to get the latest price,
but when I join, I always get NULL even though there are prices...
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
pr.accountId=a.accountid
ORDER BY a.code
If you run the inner query (the derived table) for a given accountId (adding
it to the where) it works fine, returning the correct row from the prices
table. But when used as above, it always returns null for pr.
Any ideas?
Maury
Maury,
The problem is that you are asking for the TOP 1 record, thus getting only
one record. Then you are comparing the pr.accountID of that one record with
a.accountID. So, there could be some rows joining, but all the other rows
from tblAccounts would not find a match.
I think you are probably try to do something like this (untested) code:
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN
(SELECT accountId, price1, pricedate, pricenote
FROM tblPrices tp1
JOIN (SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID) AS tp2
ON tp1.accountID = tp2.accountID
AND tp1.pricedate = tp2.pricedate) AS pr
ON pr.accountId=a.accountid
ORDER BY a.code
If I did not blow this, what it is trying to do is:
tp2 = Give me the accountId and maximum pricedate foroevery account
pr = Join tblPrices to tp2 to return all information that matches the
maximum pricedate for each accountId
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:0F47DDED-8044-4F41-BBDE-C5734329D153@.microsoft.com...
>I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest
> price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId
> (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury
|||"Russell Fields" wrote:

> I think you are probably try to do something like this (untested) code:
This is how I did it the first time actually, but the performance was
terrible.
You know, it's about time the SQL vendors added some syntax for time series!
Is it too much to ask for NEWEST and OLDEST?
Maury
|||Maury,
I feel your pain, but the right answer is better than a fast answer. (Sort
of :-)) One thing that we did not discuss is indexes and how they may help
such a query. Having said that, however, what I would probably do is create
a temporary table with the innermost tp2 select, something like:
CREATE #Temp
(accountId int,
maxpricedate datetime)
INSERT INTO #temp
SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID
-- Then use the temp table in the last select.
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN #temp t
ON a.accountId = t.accountId
JOIN tblPrices pr
ON t.accountId = pr.accountId
AND t.pricedate = pr.pricedate
ORDER BY a.code
Put an index on accountId in #temp and so forth.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6A1230A2-19DC-4D1C-A297-DC4A56C1E149@.microsoft.com...
> "Russell Fields" wrote:
>
> This is how I did it the first time actually, but the performance was
> terrible.
> You know, it's about time the SQL vendors added some syntax for time
> series!
> Is it too much to ask for NEWEST and OLDEST?
> Maury
>
|||You know, I think what I should really do is create a non-temp table called
tblCurrentPrices and keep the "latest" version in there. This sort of thing
is spread all over my code, I could probably make my whole app run faster all
over the place...
Wait, I even have a table to put it in already. Ignore me, I'm rambling.
|||On Jul 6, 8:26 pm, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury
Hi, I made made slight changes to your qiery:
Select a.AccountID, a.code, price1 = (SELECT TOP 1 price1 FROM
tblPrices pr WHERE pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc),
pricedate = (SELECT TOP 1 pricedate FROM tblPrices WHERE
pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc)
from tblAccounts a

Derived table and adding another column problem

Hi
I have this query below, which I'm trying to add and group data on the w
number (ISO w, taken from Books online) fn_getISOW returns an integer
and works independently of this query. This query also works as I want when
there is no wno invloved (the 3 places)
The error I get is "Invalid column name 'wno'", I think what I'm doing is
a "copy" of what has been done in the count(case.....) as xx
Can anyone see what I'm doing wrong and how to fix it?
regards
Henry
declare @.ext char(4)
declare @.site int
declare @.calltype char(1)
SET DATEFORMAT mdy
set @.site = 1
set @.calltype = 'E'
set @.ext = '6810'
SELECT wno***, network, centre, center, team, total, besvaret, optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0 THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid) AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
GROUP BY wno***, network, centre, center, team) AS D
ORDER BY 1Try this
SELECT wno***, network, centre, center, team, total, besvaret,
optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0
THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as
wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid)
AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
) AS D
GROUP BY wno***, network, centre, center, team ORDER BY 1
Madhivanan|||> Try this
> Madhivanan
>
Hi
No I'm sorry that doesn't do the trick either :(
regards
Henry|||> > Try this
> Hi
> No I'm sorry that doesn't do the trick either :(
> regards
> Henry
Solved... I had to use the same syntax in the group by part since wno is
not know at that time.
regards
Henrik

Derived Table Alternative

I have an application that has two different database backends, one is SQL Server Compact Edition and the other is SQL Server. The reason is because the application may run at home on one of our sales agent's computers or here in the office.

I have a query that uses a derived table and works just fine in SQL Server, however when I run it in the compact edtion (having the exact same table structures) it will not run. My question is...does the Compact Edtion or the Mobile Edition allow derived tables. If not is there a way to work around this? I will happily give an example if it will help.

Thank you,

Adam

Hi Adam,

a sample would be very useful, thanks.

|||

The three tables used for this are Assignment, Activity, and User. The Assignment table can have multiple Activities entered by different Users (only 1 user per activity). I need a list of all of the active assignments for a given User along with the last activity that was added to that assignment. The assignment may or may not have an activity but the assignment still needs listed. The activity returned must be one entered by the same user that the assignment belongs to.

Here is an example that works in SQL Server but not SqlCE:

select assignment.assignmentID, activityDateStamp
from assignment
left join
(
select assignment.assignmentID, max(activityDateStamp) as activityDateStamp
from activity
join assignment on assignment.assignmentID = activity.assignmentID
where assignment.userID = 40
and activity.userID = 40
and activityActive = 1
and assignmentActive = 1
group by assignment.assignmentID
) maxActivity on maxActivity.assignmentID = assignment.assignmentID
where assignment.userID = 40
and assignmentActive = 1

The output would look similar to this:

assignmentID activityDateStamp
-
123 NULL
4322 2006-06-23
423 2006-12-15
431 NULL

Thanks again for any help.

|||

Derived tables are not supported in SSCE.

|||I guess if we can provide some feedback to the team for future versions having derived/nested queries would definitely be something worth having. This limitation currently prevents us doing more than the simplest of queries.|||

Thanks Nick.

What I did not mention earlier was that support for derived tables aka nested queries would be there with the next release of Orcas!!

derived table

Hi,
what's the syntax for a derived table?
select count(*) from (select hostname, program_name from sysprocesses where
program_name='mydb' group by hostname, program_name) as DB1?
I want to return how many rows after group by hostname, program_name. How to
do this in a query?
Please help. Thanks.Your syntax looks fine.
Is it not doing what you expect it to?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"js" wrote:

> Hi,
> what's the syntax for a derived table?
> select count(*) from (select hostname, program_name from sysprocesses whe
re
> program_name='mydb' group by hostname, program_name) as DB1?
> I want to return how many rows after group by hostname, program_name. How
to
> do this in a query?
> Please help. Thanks.
>
>
>|||Please post the intended results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"js" <js@.someone@.hotmail.com> wrote in message
news:uuCWWRjFGHA.2040@.TK2MSFTNGP14.phx.gbl...
Hi,
what's the syntax for a derived table?
select count(*) from (select hostname, program_name from sysprocesses where
program_name='mydb' group by hostname, program_name) as DB1?
I want to return how many rows after group by hostname, program_name. How to
do this in a query?
Please help. Thanks.|||I got error:
No column was specified for column 3 of 'DB1'?
what's that mean? Thanks.
"Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
> Your syntax looks fine.
> Is it not doing what you expect it to?
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "js" wrote:
>|||Is 'mydb' the name of the database you want information about?
program_name refers to the application that is accessing the database.
If so:
The Database is in the field dbid.
Try:
select count(*) from (select hostname, program_name from sysprocesses where
dbid=db_id('mydb') group by hostname, program_name) as DB1
"js" <js@.someone@.hotmail.com> wrote in message
news:uuCWWRjFGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> what's the syntax for a derived table?
> select count(*) from (select hostname, program_name from sysprocesses
> where program_name='mydb' group by hostname, program_name) as DB1?
> I want to return how many rows after group by hostname, program_name. How
> to do this in a query?
> Please help. Thanks.
>
>|||You must have posted a different query than what you are trying to run.
That error is telling you that all columns specified in a derived table need
a name, so that they can be referenced by the outer query.
If you had a query like:
select count(*) from (select hostname, program_name, count(1) from
sysprocesses
where
program_name='mydb' group by hostname, program_name) as DB1
I'd expect the error. Because the third column does not have a referencable
name for the outer query. It would need to be re-written like:
select count(*) from (select hostname, program_name, count(1) as rowCount
from sysprocesses
where
program_name='mydb' group by hostname, program_name) as DB1
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"js" wrote:

> I got error:
> No column was specified for column 3 of 'DB1'?
> what's that mean? Thanks.
>
> "Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
> news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
>
>|||your post is missing the aggregate for DB1, btw
however, this is the problem: all columsn in in a derived table must
have a name, e.g. [using max(spid) for example]
select count(*) from (select hostname, program_name, max(spid) as spid
from sysprocesses where program_name='mydb' group by hostname,
program_name) as DB1
or
select count(*) from (select hostname, program_name, max(spid)from
sysprocesses where program_name='mydb' group by hostname, program_name)
as DB1 (hostname, program_name, spid)
js wrote:
> I got error:
> No column was specified for column 3 of 'DB1'?
> what's that mean? Thanks.
>
> "Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
> news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
>
>
>|||Thanks Trey.
I got it. need to assign name: count(*) as counters first
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:u3fb5gjFGHA.984@.tk2msftngp13.phx.gbl...
> your post is missing the aggregate for DB1, btw
> however, this is the problem: all columsn in in a derived table must have
> a name, e.g. [using max(spid) for example]
> select count(*) from (select hostname, program_name, max(spid) as spid
> from sysprocesses where program_name='mydb' group by hostname,
> program_name) as DB1
> or
> select count(*) from (select hostname, program_name, max(spid)from
> sysprocesses where program_name='mydb' group by hostname, program_name) as
> DB1 (hostname, program_name, spid)
>
> js wrote:|||Thanks Ryan.
"Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
news:6026DBD4-29D4-426C-BF58-DCAB853CB8E0@.microsoft.com...
> You must have posted a different query than what you are trying to run.
> That error is telling you that all columns specified in a derived table
> need
> a name, so that they can be referenced by the outer query.
> If you had a query like:
> select count(*) from (select hostname, program_name, count(1) from
> sysprocesses
> where
> program_name='mydb' group by hostname, program_name) as DB1
> I'd expect the error. Because the third column does not have a
> referencable
> name for the outer query. It would need to be re-written like:
> select count(*) from (select hostname, program_name, count(1) as rowCount
> from sysprocesses
> where
> program_name='mydb' group by hostname, program_name) as DB1
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "js" wrote:
>sql

Derived Table

I am trying to use a derived Table and this will not work for me. Obviously,
I am NOT going to SELECT *, but for demonstration purposes, here it is.
SELECT *
FROM (SELECT Filename,
RACE =
CASE WHEN BRaceAA = 'X' THEN '1,' ELSE '
' END +
CASE WHEN BRaceA = 'X' THEN '2,' ELSE ''
END +
CASE WHEN BRaceB = 'X' THEN '3,' ELSE ''
END +
CASE WHEN BRaceNH = 'X' THEN '4,' ELSE '
' END +
CASE WHEN BRaceW = 'X' THEN '5,' ELSE ''
END
FROM HMDAFromPointIMP
WHERE BRaceAA IS NOT NULL
OR BRaceA IS NOT NULL
OR BRaceB IS NOT NULL
OR BRaceNH IS NOT NULL
OR BRaceW IS NOT NULL)
It is giving me Incorrect Syntax on the last ")"You need to give the derived table an alias, like I demonstrated in an
earlier thread about this same issue:
FROM
(
SELECT /* blah blah */
) x
--^ alias
Please stop starting new threads!
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:34AAF6C5-E9FF-4D7C-A26C-09DDF7223265@.microsoft.com...
>I am trying to use a derived Table and this will not work for me.
>Obviously,
> I am NOT going to SELECT *, but for demonstration purposes, here it is.
> SELECT *
> FROM (SELECT Filename,
> RACE =
> CASE WHEN BRaceAA = 'X' THEN '1,' ELSE '' END +
> CASE WHEN BRaceA = 'X' THEN '2,' ELSE '' END +
> CASE WHEN BRaceB = 'X' THEN '3,' ELSE '' END +
> CASE WHEN BRaceNH = 'X' THEN '4,' ELSE '' END +
> CASE WHEN BRaceW = 'X' THEN '5,' ELSE '' END
> FROM HMDAFromPointIMP
> WHERE BRaceAA IS NOT NULL
> OR BRaceA IS NOT NULL
> OR BRaceB IS NOT NULL
> OR BRaceNH IS NOT NULL
> OR BRaceW IS NOT NULL)
> It is giving me Incorrect Syntax on the last ")"|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OVFSQP2nFHA.420@.TK2MSFTNGP09.phx.gbl...
> Please stop starting new threads!
As a member of the B,NH,W race, I take exception to your suggestion that
new threads not be created! We are a proud race of thread-starters.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||In addition to the syntax error that my other fellow posters have mentioned,
you might want to consider (if you can) changing your table structures.
Having columns like this is messy (as your query demonstrates)
You might want to do something like:
HMDAFromPointIMP
===============
HMDAFromPointIMP_key --pk
<other columns>
HMDAFromPointIMPRace
===================
HMDAFromPointIMP_key --pk
Race_key --pk
Race
====
Race_key --pk
RaceCode --AA, B,NH, etc
Description --
Then adding a new race is easier, and building queries is also much easier.
Just an idea if you are building a new system :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:34AAF6C5-E9FF-4D7C-A26C-09DDF7223265@.microsoft.com...
>I am trying to use a derived Table and this will not work for me.
>Obviously,
> I am NOT going to SELECT *, but for demonstration purposes, here it is.
> SELECT *
> FROM (SELECT Filename,
> RACE =
> CASE WHEN BRaceAA = 'X' THEN '1,' ELSE '' END +
> CASE WHEN BRaceA = 'X' THEN '2,' ELSE '' END +
> CASE WHEN BRaceB = 'X' THEN '3,' ELSE '' END +
> CASE WHEN BRaceNH = 'X' THEN '4,' ELSE '' END +
> CASE WHEN BRaceW = 'X' THEN '5,' ELSE '' END
> FROM HMDAFromPointIMP
> WHERE BRaceAA IS NOT NULL
> OR BRaceA IS NOT NULL
> OR BRaceB IS NOT NULL
> OR BRaceNH IS NOT NULL
> OR BRaceW IS NOT NULL)
> It is giving me Incorrect Syntax on the last ")"|||...and I thought Tiger Woods was supposed to be white, Asian and black. Thi
s
is the fourth socially disturbing thread today. Plus there's approximately 3
0
different races in the world.
This could make such a nice relational model...
ML

Derived Table

I have created SQL using a Derived Table. Obviously, there's a method for my
madness here and it has been a while since I've used a derived table. I am
testing portions of my query and I am having a problem with the following
SQL...getting the following error message...
Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 6 of 'drvtbl'.
And here's the SQL...
SELECT drvtbl."loan_id",
drvtbl."corr_name",
drvtbl."corr_address_1",
drvtbl."corr_city",
drvtbl."corr_state",
drvtbl."corr_zip",
drvtbl."misc_field_value"
FROM (SELECT ms_address_information."loan_id",
ms_address_information."corr_name",
ms_address_information."corr_address_1",
ms_address_information."corr_city",
ms_address_information."corr_state",
CAST(ms_loan_misc_fields."misc_field_value" AS CHAR(15))
FROM { oj "fics"."dbo"."ms_address_information" ms_address_information
LEFT OUTER JOIN "fics"."dbo"."ms_loan_misc_fields" ms_loan_misc_fields
ON ms_address_information."loan_id" = ms_loan_misc_fields."loan_id"
AND ms_loan_misc_fields."misc_field_id" = 10079}) drvtbl
Can anyone help me out here and tell me why I am getting an error message on
this?
Thanks for your help!give your column an alias
i.e.
SELECT drvtbl."loan_id",
drvtbl."corr_name",
drvtbl."corr_address_1",
drvtbl."corr_city",
drvtbl."corr_state",
drvtbl."corr_zip",
miscField
FROM (SELECT ms_address_information."loan_id",
ms_address_information."corr_name",
ms_address_information."corr_address_1",
ms_address_information."corr_city",
ms_address_information."corr_state",
CAST(ms_loan_misc_fields."misc_field_value" AS CHAR(15)) as miscField
FROM { oj "fics"."dbo"."ms_address_information" ms_address_information
LEFT OUTER JOIN "fics"."dbo"."ms_loan_misc_fields" ms_loan_misc_fields
ON ms_address_information."loan_id" = ms_loan_misc_fields."loan_id"
AND ms_loan_misc_fields."misc_field_id" = 10079}) drvtbl
"wnfisba" wrote:

> I have created SQL using a Derived Table. Obviously, there's a method for
my
> madness here and it has been a while since I've used a derived table. I am
> testing portions of my query and I am having a problem with the following
> SQL...getting the following error message...
> Server: Msg 8155, Level 16, State 2, Line 1
> No column was specified for column 6 of 'drvtbl'.
> And here's the SQL...
> SELECT drvtbl."loan_id",
> drvtbl."corr_name",
> drvtbl."corr_address_1",
> drvtbl."corr_city",
> drvtbl."corr_state",
> drvtbl."corr_zip",
> drvtbl."misc_field_value"
> FROM (SELECT ms_address_information."loan_id",
> ms_address_information."corr_name",
> ms_address_information."corr_address_1",
> ms_address_information."corr_city",
> ms_address_information."corr_state",
> CAST(ms_loan_misc_fields."misc_field_value" AS CHAR(15))
> FROM { oj "fics"."dbo"."ms_address_information" ms_address_informatio
n
> LEFT OUTER JOIN "fics"."dbo"."ms_loan_misc_fields" ms_loan_misc_fields
> ON ms_address_information."loan_id" = ms_loan_misc_fields."loan_id"
> AND ms_loan_misc_fields."misc_field_id" = 10079}) drvtbl
>
> Can anyone help me out here and tell me why I am getting an error message
on
> this?
> Thanks for your help!
>

Derived Shape - Replacing a column with itself.

Hi there,

I have a derived column shape in which I replace a column with an expression.

The expression is an IF statement - a true result sets a value for the column and a false result just uses the existing value of the column (ie it replaces it with itself)

Like this.

ColumnName DerivedColumn Expression

ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : ColumnA

But whenever, the condition is NOT met, ColumnA is set to NULL!!!!

Does this mean that the column value is deleted before the Expression is applied?

If this is how it is meant to work, then does anyone have a way of doing what I want to do without creating extra columns in the dataset?

Cheers.

I'm wondering if there are some data typing issues here. Wha is the type of ColumnA?

-Jamie

|||

Hi Jamie,

It's just a unicode string.

Dave.

|||

I mean in the pipeline. Is it a DT_WSTR?

if so, ensure you don't have any implicit conversions going on. i.e. explicitly cast "Y" as a DT_WSTR.

Also, first try to get this working as a new column rather than replacing ColumnA. See if you exhibit the same problems in that scenario.

-Jamie

|||

Jamie,

in my example setting Column A to the value "y" works fine. It's setting Column A to itself (ie II just want it to retain it's original value) which is the problem.

Do you suggest I explicitly cast the ColumnA in the expression? So with my example....

ColumnName DerivedColumn Expression

ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : CAST(ColumnA...)

|||

Dave,

No, that's not quite what i meant.

Try this:

DerivedColumn Expression

Replace 'ColumnA' ColumnB != ColumnC ? (DT_WSTR)"y" : ColumnA

My second suggestion was to see if this worked first:

DerivedColumn Expression

Add as new column ColumnB != ColumnC ? "y" : ColumnA

I'm clutching at straws a little bit but if I were you I would definately try to recreate the problem by adding it as a new column rather than replacing ColumnA.

-Jamie

|||

Hi Jamie,

adding a column works fine. It's replacing an exising column that has the "problem".

I should point out as well that the issue only arises when you use an IF condition in the expression.

So this is OK

DerivedColumn Expression

Replace ColumnA ColumnA+"Hello"

This will replace ColumnA with what was in ColumnA + "Hello"

This is NOT OK

DerivedColumn Expression

Replace ColumnA ColumnB != ColumnC ? "y" : ColumnA

So if ColumnB = ColumnC, then ColumnA is set to NULL - NOT what ColumnA was before the expression was applied.

I reckon its a bug.

|||

Hi Dave,

Can you post a simple repro that doesn't reply on external data sources (i.e. just create the same data using a script source component) and then post it up here?

To post up here, just copy the contents of your .dtsx file into your reply.

Thanks

Jamie

|||

Sure,

I'll do it tomorrow.

Speak then.

|||Have you checked if Column B or C is NULL? If so you will always get NULL out. Example here http://wiki.sqlis.com/default.aspx/SQLISWiki/Expressions.html|||

You can quickly test for what Darren is saying by changing your expression like this:

false ? "y" : ColumnA

If ColumnB or ColumnC are NULL, and you want to fail the comparison in that case,you can make your expression like this:

!ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA

If you want the condition to do NULL comparisions (that is, you want ColumnB != ColumnC to return true if one is NULL and the other is not, you might need something more like:

(ISNULL(ColumnB) != ISNULL(ColumnC)) || !ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA

|||

Your right Darren - it was the NULL values. aaargh!!!

Thanks for everyone's input.

Derived Query

I'm trying to build a "derived" query from 2 seperate table a Build count
table and a defect table
I've done this before but never with the calculated columns as in
Year(TestDate) as 'Yr'. It fails and returns
Server: Msg 207, Level 16, State 1, Line 17
Invalid column name 'YR'.
Any help ?
Select Year(TestDate) as 'Yr',
DateName(mm,TestDate)as 'Mnth',
Category, SubCategory,
Sum(NumFailures) as 'Fails'
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as 'Yr',
DateName(mm,TestDate)as 'Mnth',
Sum(DropCount) as 'Dropped'
From FTC..FTC_DropCount_Results
Where DropCount > 0
And TestDate >= '09-01-2005'
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON T1.YR = CT.YR
Group By '"Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
news:OgAwBv2HGHA.1728@.TK2MSFTNGP09.phx.gbl...
> I'm trying to build a "derived" query from 2 seperate table a Build count
> table and a defect table
> I've done this before but never with the calculated columns as in
> Year(TestDate) as 'Yr'. It fails and returns
> Server: Msg 207, Level 16, State 1, Line 17
> Invalid column name 'YR'.
> Any help ?
>
> Select Year(TestDate) as 'Yr',
> DateName(mm,TestDate)as 'Mnth',
> Category, SubCategory,
> Sum(NumFailures) as 'Fails'
> From ftc_testresults T1
> INNER JOIN
> (
> Select Year(TestDate) as 'Yr',
> DateName(mm,TestDate)as 'Mnth',
> Sum(DropCount) as 'Dropped'
> From FTC..FTC_DropCount_Results
> Where DropCount > 0
> And TestDate >= '09-01-2005'
> Group By Year(TestDate),
> DateName(mm,TestDate)
> ) CT
> ON T1.YR = CT.YR
>
A column alias cannot be used in the same query in which it is assigned. So
in the outer query T1.YR is not legal. You must repeat the source
expression Year(T1.TestDate). And get rid of the single quotes around
column aliases, and add table aliases to all your column references.
Something like this:
Select Year(T1.TestDate) as Yr,
DateName(mm,T1.TestDate)as Mnth,
T1.Category,
T1.SubCategory,
Sum(T1.NumFailures) as Fails
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as Yr,
DateName(mm,TestDate)as Mnth,
Sum(DropCount) as Dropped
From FTC..FTC_DropCount_Results
Where DropCount > 0
And TestDate >= '09-01-2005'
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON Year(T1.TestDate) = CT.YR
David|||Dave,
Thanks for the quick and accurate reply.....
Curious... what's the taboo about single quotes around column aliases?
Final working product....
Select Year(T1.TestDate) as Yr,
DateName(mm,T1.TestDate) as Mnth,
T1.Category, T1.SubCategory,
IsNull(CT.Dropped , 0) as Dropped,
Sum(T1.NumFailures) as Failures
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as Yr,
DateName(mm,TestDate)as Mnth,
Sum(DropCount) as Dropped
From FTC..FTC_DropCount_Results
Where DropCount > 0
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON Year(T1.TestDate) = CT.YR
AND DateName(mm,TestDate) = CT.Mnth
Where T1.TestDate > = '09-01-2005'
Group By T1.TestDate,T1.Category,
T1.SubCategory, ct.dropped;
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uCN8P72HGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
> news:OgAwBv2HGHA.1728@.TK2MSFTNGP09.phx.gbl...
> A column alias cannot be used in the same query in which it is assigned.
> So in the outer query T1.YR is not legal. You must repeat the source
> expression Year(T1.TestDate). And get rid of the single quotes around
> column aliases, and add table aliases to all your column references.
> Something like this:
>
> Select Year(T1.TestDate) as Yr,
> DateName(mm,T1.TestDate)as Mnth,
> T1.Category,
> T1.SubCategory,
> Sum(T1.NumFailures) as Fails
> From ftc_testresults T1
> INNER JOIN
> (
> Select Year(TestDate) as Yr,
> DateName(mm,TestDate)as Mnth,
> Sum(DropCount) as Dropped
> From FTC..FTC_DropCount_Results
> Where DropCount > 0
> And TestDate >= '09-01-2005'
> Group By Year(TestDate),
> DateName(mm,TestDate)
> ) CT
> ON Year(T1.TestDate) = CT.YR
> David
>|||"Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
news:u9eSrD3HGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Dave,
> Thanks for the quick and accurate reply.....
> Curious... what's the taboo about single quotes around column aliases?
>
Not a taboo, just a style preference. A column alias is an identifier, a
name, and it should follow the same convention as other identifiers (tables,
columns, table aliases).
David|||>> Curious... what's the taboo about single quotes around column aliases? <<
In Standard SQL, strings are enclosed in single quotes and user created
names are in double quotes. Violating standards is never a good idea;
makes code harder to pport, harder to read and occassionally can scew
up things.
You might also want to look up some old postings of mine as to how a
SELECT statement works.

derived horizontal partitioning on SQL server 2000

hello
i want someone to help me in solving a problem in sql server 2000
considering that i have a table named PAY(TITLE, SAL) where TITLE is the
primary key of this table
also this table is related to another one named EMP, where the other table
has a foreign
key to this table.
table EMP(ENO, ENAME, TITLE) where ENO is the primary key and it is related
to another table that contains a foreign key to this table.
i want to perform Derived horizontal fragmentation (partitioning) on SQL
server 2000 for EMP:
first: I want to divide the table PAY into 2 relations. Subrelation PAY1
contains information about job titles whose salaries are less than or equal
to $300,000, whereas PAY2 stores information job titles with larger salaries.
second: I want to divide the table EMP into 2 relations. Subrelation EMP1
contains information about employees whose salaries are less than or equal
to 300,000, whereas EMP2 stores information about projects with larger salaries.
please try to send me the code (with comments) and a detailed step-by-step
of how to run this code so that i have these tables fragmented on the two
servers.
or if they don't need code, i hope you send me a step-by-step support of
how to do all these fragmentations on SQL server 2000.
Thanx all
My previous answer to your latest question will solve the problem
Regards
R.D
--Knowledge gets doubled when shared
"Nada Sherief" wrote:

> hello
> i want someone to help me in solving a problem in sql server 2000
> considering that i have a table named PAY(TITLE, SAL) where TITLE is the
> primary key of this table
> also this table is related to another one named EMP, where the other table
> has a foreign
> key to this table.
> table EMP(ENO, ENAME, TITLE) where ENO is the primary key and it is related
> to another table that contains a foreign key to this table.
> i want to perform Derived horizontal fragmentation (partitioning) on SQL
> server 2000 for EMP:
> first: I want to divide the table PAY into 2 relations. Subrelation PAY1
> contains information about job titles whose salaries are less than or equal
> to $300,000, whereas PAY2 stores information job titles with larger salaries.
> second: I want to divide the table EMP into 2 relations. Subrelation EMP1
> contains information about employees whose salaries are less than or equal
> to 300,000, whereas EMP2 stores information about projects with larger salaries.
> please try to send me the code (with comments) and a detailed step-by-step
> of how to run this code so that i have these tables fragmented on the two
> servers.
> or if they don't need code, i hope you send me a step-by-step support of
> how to do all these fragmentations on SQL server 2000.
> Thanx all
>
>
|||If PAY is to be partitoned based on Salary and an employees's salary
increases from $290,000 to $310,000, would their related salary information
need to be deleted from PAY1 and re-inserted into PAY2? Generally speaking,
tables are partitioned because there is large volume of rows, like >
1,000,000, and the data is split between tables based on something like
EntryDate, RegionID or some other ID that is static and indexed.
Is there a one to many relationship between EMP and PAY?
Perhaps you are just wanting to subset results for reporting purposes?
"Nada Sherief" <nadasherief@.hotmail.com> wrote in message
news:7c7276cf31f68c7b21d8f03cf12@.news.microsoft.co m...
> hello
> i want someone to help me in solving a problem in sql server 2000
> considering that i have a table named PAY(TITLE, SAL) where TITLE is the
> primary key of this table
> also this table is related to another one named EMP, where the other table
> has a foreign key to this table.
> table EMP(ENO, ENAME, TITLE) where ENO is the primary key and it is
> related to another table that contains a foreign key to this table.
> i want to perform Derived horizontal fragmentation (partitioning) on SQL
> server 2000 for EMP:
> first: I want to divide the table PAY into 2 relations. Subrelation PAY1
> contains information about job titles whose salaries are less than or
> equal to $300,000, whereas PAY2 stores information job titles with larger
> salaries.
> second: I want to divide the table EMP into 2 relations. Subrelation EMP1
> contains information about employees whose salaries are less than or equal
> to 300,000, whereas EMP2 stores information about projects with larger
> salaries.
> please try to send me the code (with comments) and a detailed step-by-step
> of how to run this code so that i have these tables fragmented on the two
> servers.
> or if they don't need code, i hope you send me a step-by-step support of
> how to do all these fragmentations on SQL server 2000.
> Thanx all
>
|||Hello JT,
the answer for the first question is consider that i have some data in the
tables and i don't care if they are going to be changed or not.
and yes there is a one to many relationship between EMP and PAY
so
first: I want to divide the table PAY into 2 relations. Subrelation
PAY1 contains information about job titles whose salaries are less
than or equal to $300,000, whereas PAY2 stores information job titles
with larger salaries.
second: I want to divide the table EMP into 2 relations. Subrelation
EMP1 contains information about employees whose salaries are less
than or equal to 300,000, whereas EMP2 stores information about
projects with larger salaries.
please help me find a solution to this problem
Thanx
If PAY is to be partitoned based on Salary and an employees's salary[vbcol=seagreen]
> increases from $290,000 to $310,000, would their related salary
> information need to be deleted from PAY1 and re-inserted into PAY2?
> Generally speaking, tables are partitioned because there is large
> volume of rows, like > 1,000,000, andda the ta is split between tables
> based on something like EntryDate, RegionID or some other ID that is
> static and indexed.
> Is there a one to many relationship between EMP and PAY?
> Perhaps you are just wanting to subset results for reporting purposes?
> "Nada Sherief" <nadasherief@.hotmail.com> wrote in message
> news:7c7276cf31f68c7b21d8f03cf12@.news.microsoft.co m...
|||I think that reading up on Views will solve your problem.
Related partitioned tables are typically joined (vertically) or unionized
(horizontally) by implementing views.
For example:
CREATE view InvoiceHistory
as
Select * from INVOICES_2004 UNION ALL
Select * from INVOICES_2003 UNION ALL
Select * from INVOICES_2002
GO
This would allow querying invoices across all years like so:
select * from InvoiceHistory
Here are a couple of good links describing this in more detail:
http://msdn.microsoft.com/library/de...itionsInDW.htm
http://www.microsoft.com/technet/pro...2005/spdw.mspx
However, notice that these articles revolve around data warehousing
concepts. Generally, speaking tables are not partitioned, unless you are
wanting to segment a large amount of data for performance reasons. It sounds
like you are wanting to partition related data into seperate tables for what
you think are logical reasons, but this de-normalizes your database model
and provides no benefit. All it would do is make your queries more complex.
http://en.wikipedia.org/wiki/Database_normalization
If you are wanting to retrict update to only specific columns in a table,
then consider implementing a view that only returns those updatble columns
and give the application access to that rather than the base table.
http://msdn.microsoft.com/library/de...urity_5whf.asp
"Nada Sherief" <nadasherief@.hotmail.com> wrote in message
news:7c7276cf35f88c7b2bed08573c2@.news.microsoft.co m...
> Hello JT,
> the answer for the first question is consider that i have some data in the
> tables and i don't care if they are going to be changed or not.
> and yes there is a one to many relationship between EMP and PAY
> so
> first: I want to divide the table PAY into 2 relations. Subrelation
> PAY1 contains information about job titles whose salaries are less
> than or equal to $300,000, whereas PAY2 stores information job titles
> with larger salaries.
> second: I want to divide the table EMP into 2 relations. Subrelation
> EMP1 contains information about employees whose salaries are less
> than or equal to 300,000, whereas EMP2 stores information about
> projects with larger salaries.
> please help me find a solution to this problem
> Thanx
>
> If PAY is to be partitoned based on Salary and an employees's salary
>
sql

derived horizontal partitioning on SQL server 2000

hello
i want someone to help me in solving a problem in sql server 2000
considering that i have a table named PAY(TITLE, SAL) where TITLE is the
primary key of this table
also this table is related to another one named EMP, where the other table
has a foreign
key to this table.
table EMP(ENO, ENAME, TITLE) where ENO is the primary key and it is related
to another table that contains a foreign key to this table.
i want to perform Derived horizontal fragmentation (partitioning) on SQL
server 2000 for EMP:
first: I want to divide the table PAY into 2 relations. Subrelation PAY1
contains information about job titles whose salaries are less than or equal
to $300,000, whereas PAY2 stores information job titles with larger salaries.
second: I want to divide the table EMP into 2 relations. Subrelation EMP1
contains information about employees whose salaries are less than or equal
to 300,000, whereas EMP2 stores information about projects with larger salaries.
please try to send me the code (with comments) and a detailed step-by-step
of how to run this code so that i have these tables fragmented on the two
servers.
or if they don't need code, i hope you send me a step-by-step support of
how to do all these fragmentations on SQL server 2000.
Thanx all
My previous answer to your latest question will solve the problem
Regards
R.D
--Knowledge gets doubled when shared
"Nada Sherief" wrote:

> hello
> i want someone to help me in solving a problem in sql server 2000
> considering that i have a table named PAY(TITLE, SAL) where TITLE is the
> primary key of this table
> also this table is related to another one named EMP, where the other table
> has a foreign
> key to this table.
> table EMP(ENO, ENAME, TITLE) where ENO is the primary key and it is related
> to another table that contains a foreign key to this table.
> i want to perform Derived horizontal fragmentation (partitioning) on SQL
> server 2000 for EMP:
> first: I want to divide the table PAY into 2 relations. Subrelation PAY1
> contains information about job titles whose salaries are less than or equal
> to $300,000, whereas PAY2 stores information job titles with larger salaries.
> second: I want to divide the table EMP into 2 relations. Subrelation EMP1
> contains information about employees whose salaries are less than or equal
> to 300,000, whereas EMP2 stores information about projects with larger salaries.
> please try to send me the code (with comments) and a detailed step-by-step
> of how to run this code so that i have these tables fragmented on the two
> servers.
> or if they don't need code, i hope you send me a step-by-step support of
> how to do all these fragmentations on SQL server 2000.
> Thanx all
>
>
|||If PAY is to be partitoned based on Salary and an employees's salary
increases from $290,000 to $310,000, would their related salary information
need to be deleted from PAY1 and re-inserted into PAY2? Generally speaking,
tables are partitioned because there is large volume of rows, like >
1,000,000, and the data is split between tables based on something like
EntryDate, RegionID or some other ID that is static and indexed.
Is there a one to many relationship between EMP and PAY?
Perhaps you are just wanting to subset results for reporting purposes?
"Nada Sherief" <nadasherief@.hotmail.com> wrote in message
news:7c7276cf31f68c7b21d8f03cf12@.news.microsoft.co m...
> hello
> i want someone to help me in solving a problem in sql server 2000
> considering that i have a table named PAY(TITLE, SAL) where TITLE is the
> primary key of this table
> also this table is related to another one named EMP, where the other table
> has a foreign key to this table.
> table EMP(ENO, ENAME, TITLE) where ENO is the primary key and it is
> related to another table that contains a foreign key to this table.
> i want to perform Derived horizontal fragmentation (partitioning) on SQL
> server 2000 for EMP:
> first: I want to divide the table PAY into 2 relations. Subrelation PAY1
> contains information about job titles whose salaries are less than or
> equal to $300,000, whereas PAY2 stores information job titles with larger
> salaries.
> second: I want to divide the table EMP into 2 relations. Subrelation EMP1
> contains information about employees whose salaries are less than or equal
> to 300,000, whereas EMP2 stores information about projects with larger
> salaries.
> please try to send me the code (with comments) and a detailed step-by-step
> of how to run this code so that i have these tables fragmented on the two
> servers.
> or if they don't need code, i hope you send me a step-by-step support of
> how to do all these fragmentations on SQL server 2000.
> Thanx all
>
|||Hello JT,
the answer for the first question is consider that i have some data in the
tables and i don't care if they are going to be changed or not.
and yes there is a one to many relationship between EMP and PAY
so
first: I want to divide the table PAY into 2 relations. Subrelation
PAY1 contains information about job titles whose salaries are less
than or equal to $300,000, whereas PAY2 stores information job titles
with larger salaries.
second: I want to divide the table EMP into 2 relations. Subrelation
EMP1 contains information about employees whose salaries are less
than or equal to 300,000, whereas EMP2 stores information about
projects with larger salaries.
please help me find a solution to this problem
Thanx
If PAY is to be partitoned based on Salary and an employees's salary[vbcol=seagreen]
> increases from $290,000 to $310,000, would their related salary
> information need to be deleted from PAY1 and re-inserted into PAY2?
> Generally speaking, tables are partitioned because there is large
> volume of rows, like > 1,000,000, andda the ta is split between tables
> based on something like EntryDate, RegionID or some other ID that is
> static and indexed.
> Is there a one to many relationship between EMP and PAY?
> Perhaps you are just wanting to subset results for reporting purposes?
> "Nada Sherief" <nadasherief@.hotmail.com> wrote in message
> news:7c7276cf31f68c7b21d8f03cf12@.news.microsoft.co m...
|||I think that reading up on Views will solve your problem.
Related partitioned tables are typically joined (vertically) or unionized
(horizontally) by implementing views.
For example:
CREATE view InvoiceHistory
as
Select * from INVOICES_2004 UNION ALL
Select * from INVOICES_2003 UNION ALL
Select * from INVOICES_2002
GO
This would allow querying invoices across all years like so:
select * from InvoiceHistory
Here are a couple of good links describing this in more detail:
http://msdn.microsoft.com/library/de...itionsInDW.htm
http://www.microsoft.com/technet/pro...2005/spdw.mspx
However, notice that these articles revolve around data warehousing
concepts. Generally, speaking tables are not partitioned, unless you are
wanting to segment a large amount of data for performance reasons. It sounds
like you are wanting to partition related data into seperate tables for what
you think are logical reasons, but this de-normalizes your database model
and provides no benefit. All it would do is make your queries more complex.
http://en.wikipedia.org/wiki/Database_normalization
If you are wanting to retrict update to only specific columns in a table,
then consider implementing a view that only returns those updatble columns
and give the application access to that rather than the base table.
http://msdn.microsoft.com/library/de...urity_5whf.asp
"Nada Sherief" <nadasherief@.hotmail.com> wrote in message
news:7c7276cf35f88c7b2bed08573c2@.news.microsoft.co m...
> Hello JT,
> the answer for the first question is consider that i have some data in the
> tables and i don't care if they are going to be changed or not.
> and yes there is a one to many relationship between EMP and PAY
> so
> first: I want to divide the table PAY into 2 relations. Subrelation
> PAY1 contains information about job titles whose salaries are less
> than or equal to $300,000, whereas PAY2 stores information job titles
> with larger salaries.
> second: I want to divide the table EMP into 2 relations. Subrelation
> EMP1 contains information about employees whose salaries are less
> than or equal to 300,000, whereas EMP2 stores information about
> projects with larger salaries.
> please help me find a solution to this problem
> Thanx
>
> If PAY is to be partitoned based on Salary and an employees's salary
>