combining datasets

I have two queries, one that brings back data from a cube and another that
returns records from a db. Is there anyway inside reporting services to
combine the two data sets?Only through subreports.
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:ucK4AE02FHA.3272@.TK2MSFTNGP09.phx.gbl...
> I have two queries, one that brings back data from a cube and another that
> returns records from a db. Is there anyway inside reporting services to
> combine the two data sets?
>

combining 2 rows

Hello,
I have a query that returns 2 rows, which I need to combine into 1 row.
The query looks like this:
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
CASE
WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Amount (incl. commission)',
CASE
WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
ORDER BY t.CName, t.AName, t.ACurrency
...the 2 rows that the query returns are:
CName AName ACurrency Payments Amount (incl. commission) Commission
ClientA ClientA EUR 69 NULL 173.27
ClientA ClientA EUR 69 3465.29 NULL
...and I want to combine those 2 rows into a single row that looks like
this:
CName AName ACurrency Payments Amount (incl. commission) Commission
ClientA ClientA EUR 69 3465.29 173.27
Thanks in advance,
Craig H.Use your query as a derived table and collapse the rows in a query around
it -- like this:
Select CName, AName, ACurrency, Payments, Sum( [Amount (incl. commission)]),
Sum ([Commission])
From
(
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
CASE
WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Amount (incl. commission)',
CASE
WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
ORDER BY t.CName, t.AName, t.ACurrency
) T
Group By CName, AName, ACurrency, Payments
Order By CName, AName, ACurrency
hth,
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"Craig H." <spam@.thehurley.com> wrote in message
news:u2%23N1$OnFHA.3336@.tk2msftngp13.phx.gbl...
> Hello,
> I have a query that returns 2 rows, which I need to combine into 1 row.
> The query looks like this:
> SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
> CASE
> WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Amount (incl. commission)',
> CASE
> WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Commission'
> FROM Reporting.dbo.RollUp t
> WHERE (t.PTCode='debit')
> AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
> GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
> ORDER BY t.CName, t.AName, t.ACurrency
>
> ...the 2 rows that the query returns are:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 NULL 173.27
> ClientA ClientA EUR 69 3465.29 NULL
>
> ...and I want to combine those 2 rows into a single row that looks like
> this:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 3465.29 173.27
>
> Thanks in advance,
> Craig H.|||hi
it might work, if u remove
t.TCode from the group by clause
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Craig H." wrote:

> Hello,
> I have a query that returns 2 rows, which I need to combine into 1 row.
> The query looks like this:
> SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
> CASE
> WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Amount (incl. commission)',
> CASE
> WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Commission'
> FROM Reporting.dbo.RollUp t
> WHERE (t.PTCode='debit')
> AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
> GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
> ORDER BY t.CName, t.AName, t.ACurrency
>
> ...the 2 rows that the query returns are:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 NULL 173.27
> ClientA ClientA EUR 69 3465.29 NULL
>
> ...and I want to combine those 2 rows into a single row that looks like
> this:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 3465.29 173.27
>
> Thanks in advance,
> Craig H.
>|||> it might work, if u remove
> t.TCode from the group by clause
No. It will give you the error t.TCCode is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:3485024E-D8DF-42DE-A9F5-13C37C0B22FF@.microsoft.com...
> hi
> it might work, if u remove
> t.TCode from the group by clause
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Craig H." wrote:
>|||Looks like you just need to remove TCode from your GROUP BY list:
...
GROUP BY t.CName, t.AName, t.ACurrency
David Portas
SQL Server MVP
--|||Good catch. I missed it too. So put the CASE expression inside the SUM
aggregate. Something like the following, depending on the desired
result of the SUM:
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
SUM(CASE WHEN t.TCode = 'debit_batch'
THEN t.LAmount/t.ExchangeRate END) AS 'Amount (incl. commission)',
SUM(CASE WHEN t.TCode = 'commission'
THEN t.LAmount/t.ExchangeRate END) AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency
ORDER BY t.CName, t.AName, t.ACurrency ;
David Portas
SQL Server MVP
--|||David,
Correct me If I am wrong.
As per my understanding , removing TCode fro the Group By list will
throw the error
TCCode is invalid in the select list because it is not contained in
either an aggregate function or
the GROUP BY clause.
SELECT pub_id,
CASE WHEN type = 'business' THEN SUM(ytd_sales) END
FROM Titles
GROUP By pub_id
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123599468.276266.144040@.g44g2000cwa.googlegroups.com...
> Looks like you just need to remove TCode from your GROUP BY list:
> ...
> GROUP BY t.CName, t.AName, t.ACurrency
> --
> David Portas
> SQL Server MVP
> --
>|||Never mind. I had seen the other post only after sending this.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23iPyJWPnFHA.764@.TK2MSFTNGP14.phx.gbl...
> David,
> Correct me If I am wrong.
> As per my understanding , removing TCode fro the Group By list will
> throw the error
> TCCode is invalid in the select list because it is not contained in
> either an aggregate function or
> the GROUP BY clause.
>
> SELECT pub_id,
> CASE WHEN type = 'business' THEN SUM(ytd_sales) END
> FROM Titles
> GROUP By pub_id
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1123599468.276266.144040@.g44g2000cwa.googlegroups.com...
>

combining 2 queries?

HI I have two queries I am trying to combine
the first one simply returns several integers
for the second part I am trying to insert these integers into another table.
Code below does not work together. Do I need an integer array for @.logids
SELECT @.logids field1 FROM dbo.table1
WHERE POC_ID = 15
INSERT INTO table2
(field2)
VALUES
(@.logids)
--
Paul G
Software engineer.You got it backwards, try
INSERT INTO table1 (field1)
SELECT table2.field2 FROM table2;
"Paul" wrote:
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Paul,
If I understand your question correctly, it would be just
insert into table2 (field2)
SELECT field1 FROM dbo.table1
WHERE POC_ID = 15
hth
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another
> table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Hi thanks for the response,
tried this below, but nothing is getting inserted,
SET @.pocid = 15
INSERT INTO table2
(field2 )
SELECT field1 FROM table1 WHERE POC_ID = @.pocid
since I have no Nulls allowed for table to I get the error can not insert
NULL.
so it is trying to insert a NULL
"Ash" wrote:
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.|||Hi thanks for the response. I tried this but it tries to insert NULL
get error statement Cannot insert the value NULL into column 'field2', table
'table2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
When I try the select statement without the insert it returns 83 values in
the
database output.
"Quentin Ran" wrote:
> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another
> > table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.
>
>|||It is working now thanks for the help.
"Ash" wrote:
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.|||it is working now thanks for the help.
"Quentin Ran" wrote:
> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another
> > table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.
>
>|||You need to make sure that if the field you are inserting into doesnt allow
nulls that the field you are selecting from also doesn't allow null.
Or unique...etc (ex. Primarykey)
INSERT into table2 (field2) SELECT field1 FROM dbo.table1 WHERE POC_ID = 15
If you have for example in table2 'field1' that is a PK then you need to
consider that.
ex INSERT into table2 (field1,field2) SELECT field1,field2 FROM dbo.table1
WHERE POC_ID = 15
But what you did is that you tried poplulating a row without assigning the
PK value.
"Paul" wrote:
> Hi thanks for the response. I tried this but it tries to insert NULL
> get error statement Cannot insert the value NULL into column 'field2', table
> 'table2'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> When I try the select statement without the insert it returns 83 values in
> the
> database output.
> "Quentin Ran" wrote:
> > Paul,
> >
> > If I understand your question correctly, it would be just
> >
> > insert into table2 (field2)
> > SELECT field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > hth
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > > HI I have two queries I am trying to combine
> > > the first one simply returns several integers
> > > for the second part I am trying to insert these integers into another
> > > table.
> > > Code below does not work together. Do I need an integer array for @.logids
> > >
> > > SELECT @.logids field1 FROM dbo.table1
> > > WHERE POC_ID = 15
> > >
> > > INSERT INTO table2
> > > (field2)
> > > VALUES
> > > (@.logids)
> > >
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
> >sqlsql

combining 2 queries?

HI I have two queries I am trying to combine
the first one simply returns several integers
for the second part I am trying to insert these integers into another table.
Code below does not work together. Do I need an integer array for @.logids
SELECT @.logids field1 FROM dbo.table1
WHERE POC_ID = 15
INSERT INTO table2
(field2)
VALUES
(@.logids)
Paul G
Software engineer.
You got it backwards, try
INSERT INTO table1 (field1)
SELECT table2.field2 FROM table2;
"Paul" wrote:

> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.
|||Paul,
If I understand your question correctly, it would be just
insert into table2 (field2)
SELECT field1 FROM dbo.table1
WHERE POC_ID = 15
hth
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another
> table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.
|||Hi thanks for the response,
tried this below, but nothing is getting inserted,
SET @.pocid = 15
INSERT INTO table2
(field2 )
SELECT field1 FROM table1 WHERE POC_ID = @.pocid
since I have no Nulls allowed for table to I get the error can not insert
NULL.
so it is trying to insert a NULL
"Ash" wrote:
[vbcol=seagreen]
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
|||Hi thanks for the response. I tried this but it tries to insert NULL
get error statement Cannot insert the value NULL into column 'field2', table
'table2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
When I try the select statement without the insert it returns 83 values in
the
database output.
"Quentin Ran" wrote:

> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
>
>
|||It is working now thanks for the help.
"Ash" wrote:
[vbcol=seagreen]
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
|||it is working now thanks for the help.
"Quentin Ran" wrote:

> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
>
>
|||You need to make sure that if the field you are inserting into doesnt allow
nulls that the field you are selecting from also doesn't allow null.
Or unique...etc (ex. Primarykey)
INSERT into table2 (field2) SELECT field1 FROM dbo.table1 WHERE POC_ID = 15
If you have for example in table2 'field1' that is a PK then you need to
consider that.
ex INSERT into table2 (field1,field2) SELECT field1,field2 FROM dbo.table1
WHERE POC_ID = 15
But what you did is that you tried poplulating a row without assigning the
PK value.
"Paul" wrote:
[vbcol=seagreen]
> Hi thanks for the response. I tried this but it tries to insert NULL
> get error statement Cannot insert the value NULL into column 'field2', table
> 'table2'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> When I try the select statement without the insert it returns 83 values in
> the
> database output.
> "Quentin Ran" wrote:

combining 2 queries?

HI I have two queries I am trying to combine
the first one simply returns several integers
for the second part I am trying to insert these integers into another table.
Code below does not work together. Do I need an integer array for @.logids
SELECT @.logids field1 FROM dbo.table1
WHERE POC_ID = 15
INSERT INTO table2
(field2)
VALUES
(@.logids)
Paul G
Software engineer.You got it backwards, try
INSERT INTO table1 (field1)
SELECT table2.field2 FROM table2;
"Paul" wrote:

> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another tabl
e.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Paul,
If I understand your question correctly, it would be just
insert into table2 (field2)
SELECT field1 FROM dbo.table1
WHERE POC_ID = 15
hth
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another
> table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Hi thanks for the response,
tried this below, but nothing is getting inserted,
SET @.pocid = 15
INSERT INTO table2
(field2 )
SELECT field1 FROM table1 WHERE POC_ID = @.pocid
since I have no Nulls allowed for table to I get the error can not insert
NULL.
so it is trying to insert a NULL
"Ash" wrote:
[vbcol=seagreen]
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
>|||Hi thanks for the response. I tried this but it tries to insert NULL
get error statement Cannot insert the value NULL into column 'field2', table
'table2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
When I try the select statement without the insert it returns 83 values in
the
database output.
"Quentin Ran" wrote:

> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
>
>|||It is working now thanks for the help.
"Ash" wrote:
[vbcol=seagreen]
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
>|||it is working now thanks for the help.
"Quentin Ran" wrote:

> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
>
>|||You need to make sure that if the field you are inserting into doesnt allow
nulls that the field you are selecting from also doesn't allow null.
Or unique...etc (ex. Primarykey)
INSERT into table2 (field2) SELECT field1 FROM dbo.table1 WHERE POC_ID = 15
If you have for example in table2 'field1' that is a PK then you need to
consider that.
ex INSERT into table2 (field1,field2) SELECT field1,field2 FROM dbo.table1
WHERE POC_ID = 15
But what you did is that you tried poplulating a row without assigning the
PK value.
"Paul" wrote:
[vbcol=seagreen]
> Hi thanks for the response. I tried this but it tries to insert NULL
> get error statement Cannot insert the value NULL into column 'field2', tab
le
> 'table2'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> When I try the select statement without the insert it returns 83 values in
> the
> database output.
> "Quentin Ran" wrote:
>

Combine two queries

Hello, i am an SMS guy trying to write some SQL queries and having little
luck. I have two queries. The first returns the dept code which in my case
is the first 2 letters a computer and a count of computers with that dept
code(see below).
Total counts by dept code
SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
FROM v_R_System
GROUP BY LEFT(Name0, 2)
Second is a query that returns the dept code and a count of machines that
have the client installed.
Totals counts installed by dept code
SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
FROM v_R_System where client0=1
GROUP BY LEFT(Name0, 2)
My question is this... How the heck can I combine the two into one query so
I return Dept code, total machines, and total machines with client?
Thanks for helping out this rookie...
scottYou can use a CASE like:
SELECT LEFT( Name0, 2 ) AS "dept_code",
COUNT( * ) AS "total_machines",
SUM( CASE WHEN client0 = 1 THEN 1 ELSE 0 END ) AS "client0_count"
FROM v_R_System
GROUP BY LEFT( Name0, 2 ) ;
Anith|||Try,
SELECT
LEFT(Name0, 2) AS [dept Code],
client0,
COUNT(*) AS [Total Machines],
(select count(*) from v_R_System as t1 where LEFT(t1.Name0, 2) =
LEFT(v_R_System.Name0, 2)) as total_dept_comp
FROM
v_R_System
GROUP BY
LEFT(Name0, 2),
client0
order by
LEFT(Name0, 2),
client0
AMB
"scott" wrote:

> Hello, i am an SMS guy trying to write some SQL queries and having little
> luck. I have two queries. The first returns the dept code which in my ca
se
> is the first 2 letters a computer and a count of computers with that dept
> code(see below).
> Total counts by dept code
> SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
> FROM v_R_System
> GROUP BY LEFT(Name0, 2)
> Second is a query that returns the dept code and a count of machines that
> have the client installed.
> Totals counts installed by dept code
> SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
> FROM v_R_System where client0=1
> GROUP BY LEFT(Name0, 2)
> My question is this... How the heck can I combine the two into one query
so
> I return Dept code, total machines, and total machines with client?
> Thanks for helping out this rookie...
> scott
>sqlsql

Combine two lots of xml in to one?

I have two stored procedures each returning xml using for xml explicit:
GetOrders returns data as:
<Orders><Order id = "1"/><Order id = "2"></Orders>
GetCustomers returns data as:
<Customers><Customer id = "1"/><Customer id = "2"/></Customers>
Now what I want is a third procedure that reuses both these stored
procedures to get customers and orders like so:
GetCustomersAndOrders returns data as:
<CustomersAndOrders>
<Orders>
<Order id = "1"/>
<Order id = "2">
</Orders>
<Customers>
<Customer id = "1"/>
<Customer id = "2"/>
</Customers>
</CustomersAndOrders>
Is there a way to do it?
Thanks!In SQL Server 2000, you have to do this on the mid-tier. You can use the
SQLXML templates for example.
In SQL Server 2005, you would need to change the stored procs into
user-defined functions and use another FOR XML call to compose them, if you
want to do it on the server.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I have two stored procedures each returning xml using for xml explicit:
> GetOrders returns data as:
> <Orders><Order id = "1"/><Order id = "2"></Orders>
> GetCustomers returns data as:
> <Customers><Customer id = "1"/><Customer id = "2"/></Customers>
> Now what I want is a third procedure that reuses both these stored
> procedures to get customers and orders like so:
> GetCustomersAndOrders returns data as:
> <CustomersAndOrders>
> <Orders>
> <Order id = "1"/>
> <Order id = "2">
> </Orders>
> <Customers>
> <Customer id = "1"/>
> <Customer id = "2"/>
> </Customers>
> </CustomersAndOrders>
> Is there a way to do it?
> Thanks!
>|||Thanks for your feedback. Shame that it is not possible though.
Is there a way to do it, say, by casting both lots of xml data to strings
and concatenating them with surrounding root tags?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2000, you have to do this on the mid-tier. You can use the
> SQLXML templates for example.
> In SQL Server 2005, you would need to change the stored procs into
> user-defined functions and use another FOR XML call to compose them, if
you
> want to do it on the server.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>|||You cannot cast results of FOR XML in SQL Server 2000 since it can only be
transported to the mid-tier. And even in SQL Server 2005, you cannot cast
the result of a stored procedure since stored procedures operate via a
side-effect.
This is not only the case for XML but for any result that a stored proc
produces as a side-effect.
So the best way to do what you want is on the client-side.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:%23eG%23roJ9EHA.2608@.TK2MSFTNGP10.phx.gbl...
> Thanks for your feedback. Shame that it is not possible though.
> Is there a way to do it, say, by casting both lots of xml data to strings
> and concatenating them with surrounding root tags?
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> you
>

Combine two lots of xml in to one?

I have two stored procedures each returning xml using for xml explicit:
GetOrders returns data as:
<Orders><Order id = "1"/><Order id = "2"></Orders>
GetCustomers returns data as:
<Customers><Customer id = "1"/><Customer id = "2"/></Customers>
Now what I want is a third procedure that reuses both these stored
procedures to get customers and orders like so:
GetCustomersAndOrders returns data as:
<CustomersAndOrders>
<Orders>
<Order id = "1"/>
<Order id = "2">
</Orders>
<Customers>
<Customer id = "1"/>
<Customer id = "2"/>
</Customers>
</CustomersAndOrders>
Is there a way to do it?
Thanks!
In SQL Server 2000, you have to do this on the mid-tier. You can use the
SQLXML templates for example.
In SQL Server 2005, you would need to change the stored procs into
user-defined functions and use another FOR XML call to compose them, if you
want to do it on the server.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I have two stored procedures each returning xml using for xml explicit:
> GetOrders returns data as:
> <Orders><Order id = "1"/><Order id = "2"></Orders>
> GetCustomers returns data as:
> <Customers><Customer id = "1"/><Customer id = "2"/></Customers>
> Now what I want is a third procedure that reuses both these stored
> procedures to get customers and orders like so:
> GetCustomersAndOrders returns data as:
> <CustomersAndOrders>
> <Orders>
> <Order id = "1"/>
> <Order id = "2">
> </Orders>
> <Customers>
> <Customer id = "1"/>
> <Customer id = "2"/>
> </Customers>
> </CustomersAndOrders>
> Is there a way to do it?
> Thanks!
>
|||Thanks for your feedback. Shame that it is not possible though.
Is there a way to do it, say, by casting both lots of xml data to strings
and concatenating them with surrounding root tags?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2000, you have to do this on the mid-tier. You can use the
> SQLXML templates for example.
> In SQL Server 2005, you would need to change the stored procs into
> user-defined functions and use another FOR XML call to compose them, if
you
> want to do it on the server.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>
|||You cannot cast results of FOR XML in SQL Server 2000 since it can only be
transported to the mid-tier. And even in SQL Server 2005, you cannot cast
the result of a stored procedure since stored procedures operate via a
side-effect.
This is not only the case for XML but for any result that a stored proc
produces as a side-effect.
So the best way to do what you want is on the client-side.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:%23eG%23roJ9EHA.2608@.TK2MSFTNGP10.phx.gbl...
> Thanks for your feedback. Shame that it is not possible though.
> Is there a way to do it, say, by casting both lots of xml data to strings
> and concatenating them with surrounding root tags?
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> you
>

Combine multiple columns into one

I have a table Venues
ID int
Location1 char(10),
Location2 char(10),
Location3 char(10),
Location4 char(10)
and would like to have a query that returns a single column of
Locations i.e
for the record ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
I would get the following result
Locations
Boston
NewYork
London
Paris
Is it possible to merge the values from columns location1,location2
etc into a new column?
SELECT
ID
,'Location 1 = ' + Location1 + ',Location 2 = ' + Location2 ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"michael" <michael.l.obrien@.ul.ie> wrote in message news:6dcedfaf.0404220202.1a24b6af@.posting.google.c om...
> I have a table Venues
> ID int
> Location1 char(10),
> Location2 char(10),
> Location3 char(10),
> Location4 char(10)
> and would like to have a query that returns a single column of
> Locations i.e
> for the record ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
> I would get the following result
> Locations
> --
> Boston
> NewYork
> London
> Paris
> Is it possible to merge the values from columns location1,location2
> etc into a new column?
|||On 22 Apr 2004 03:02:07 -0700, michael wrote:

>I have a table Venues
>ID int
>Location1 char(10),
>Location2 char(10),
>Location3 char(10),
>Location4 char(10)
>and would like to have a query that returns a single column of
>Locations i.e
>for the record ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
>I would get the following result
>Locations
>--
>Boston
>NewYork
>London
>Paris
>Is it possible to merge the values from columns location1,location2
>etc into a new column?
SELECT Location1 AS Locations
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location2
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location3
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location4
FROM Venues
WHERE ID = 2
By the way, your design is not properly normalized. The
Venue-Locations should be in a seperate table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||In response to "BTW, your design is not properly normalized..."
We really do not know what is being stored in the LOCATION columns.
If the 4 locations are somehow different - like location 1 is primary,
location 2 is secondary - then this design is fine in my book. If most of
the time the app in front of this table shows the 4 locations on one row,
then it's fine by me also. Normalization can and is often taken to way to
far a level.
I've seen "college" admin systems with 500 tables - so obsur that only the
original implementors have a clue as to what is going on.
In our K-12 student applications, we store all 4 marking period marks in one
row of a table. Each student/class has only one row, with all 4 marking
period marks within that row. In my book, they are different "entities",
thus this is properly normalized. I've had debates with other programmers
that they should be separated into a MARK table, underneath the
STUDENT/CLASS table. The STUDENT/CLASS table already typically has 50000+
rows per school per year - creating a sub-table with each marking period
mark, 200,000+ rows per year hurts my head.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com... [vbcol=seagreen]
> On 22 Apr 2004 03:02:07 -0700, michael wrote:
ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
> SELECT Location1 AS Locations
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location2
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location3
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location4
> FROM Venues
> WHERE ID = 2
>
> By the way, your design is not properly normalized. The
> Venue-Locations should be in a seperate table.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks to all for the help. Maybe I can clear up why I
have the table the way it is and apologise for not giving
enough details about what I am trying to do. I had a sit
down and figured out what I was trying to do after I
posted the first message.
My venues table is linked (VenueID) to a events table. The
venues table has a min of 30 locations i.e
location1...location30 and would like to have a sproc to
return all the non null locations for a particular event
ID without having to have at least 30 "select union"
sections with tests for null values
Here is my first draft of what I am thinking
--The variable have been declared @.inti=1,@.intj=2
--@.Column1 @.Column2 (both char(5)
--and have not added the null test yet
While @.intj <=35
Begin
Set @.Column1 ='Location'+ (cast(@.inti as char(2)))
Set @.Column2 ='Location'+ (cast(@.intj as char(2)))
SELECT @.Column1 Locations FROM Venues where
Venues.LocationID='200'
UNION
SELECT @.Column2 FROM Venues where Venues.LocationID='200'
Set @.inti =@.inti + 2
Set @.intj =@.intj + 2
End
--This only results in the following
Locations
Location1
Location2
Locations
Location3
Location4
Any suggestions
>--Original Message--
>In response to "BTW, your design is not properly
normalized..."
>We really do not know what is being stored in the
LOCATION columns.
>If the 4 locations are somehow different - like location
1 is primary,
>location 2 is secondary - then this design is fine in my
book. If most of
>the time the app in front of this table shows the 4
locations on one row,
>then it's fine by me also. Normalization can and is
often taken to way to
>far a level.
>I've seen "college" admin systems with 500 tables - so
obsur that only the
>original implementors have a clue as to what is going on.
>In our K-12 student applications, we store all 4 marking
period marks in one
>row of a table. Each student/class has only one row,
with all 4 marking
>period marks within that row. In my book, they are
different "entities",
>thus this is properly normalized. I've had debates with
other programmers
>that they should be separated into a MARK table,
underneath the
>STUDENT/CLASS table. The STUDENT/CLASS table already
typically has 50000+
>rows per school per year - creating a sub-table with each
marking period
>mark, 200,000+ rows per year hurts my head.
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
message[vbcol=seagreen]
>news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com.. .
column of
>ID=2,Location1=Boston,Location2=NewYork,Location3 =London,L
ocation4=Paris[vbcol=seagreen]
location1,location2
>
>.
>
sqlsql

Combine multiple columns into one

I have a table Venues
ID int
Location1 char(10),
Location2 char(10),
Location3 char(10),
Location4 char(10)
and would like to have a query that returns a single column of
Locations i.e
for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Loca
tion4=Paris
I would get the following result
Locations
--
Boston
NewYork
London
Paris
Is it possible to merge the values from columns location1,location2
etc into a new column?SELECT
ID
,'Location 1 = ' + Location1 + ',Location 2 = ' + Location2 ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"michael" <michael.l.obrien@.ul.ie> wrote in message news:6dcedfaf.0404220202.1a24b6af@.postin
g.google.com...
> I have a table Venues
> ID int
> Location1 char(10),
> Location2 char(10),
> Location3 char(10),
> Location4 char(10)
> and would like to have a query that returns a single column of
> Locations i.e
> for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Lo
cation4=Paris
> I would get the following result
> Locations
> --
> Boston
> NewYork
> London
> Paris
> Is it possible to merge the values from columns location1,location2
> etc into a new column?|||On 22 Apr 2004 03:02:07 -0700, michael wrote:

>I have a table Venues
>ID int
>Location1 char(10),
>Location2 char(10),
>Location3 char(10),
>Location4 char(10)
>and would like to have a query that returns a single column of
>Locations i.e
>for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Loc
ation4=Paris
>I would get the following result
>Locations
>--
>Boston
>NewYork
>London
>Paris
>Is it possible to merge the values from columns location1,location2
>etc into a new column?
SELECT Location1 AS Locations
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location2
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location3
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location4
FROM Venues
WHERE ID = 2
By the way, your design is not properly normalized. The
Venue-Locations should be in a seperate table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In response to "BTW, your design is not properly normalized..."
We really do not know what is being stored in the LOCATION columns.
If the 4 locations are somehow different - like location 1 is primary,
location 2 is secondary - then this design is fine in my book. If most of
the time the app in front of this table shows the 4 locations on one row,
then it's fine by me also. Normalization can and is often taken to way to
far a level.
I've seen "college" admin systems with 500 tables - so obsur that only the
original implementors have a clue as to what is going on.
In our K-12 student applications, we store all 4 marking period marks in one
row of a table. Each student/class has only one row, with all 4 marking
period marks within that row. In my book, they are different "entities",
thus this is properly normalized. I've had debates with other programmers
that they should be separated into a MARK table, underneath the
STUDENT/CLASS table. The STUDENT/CLASS table already typically has 50000+
rows per school per year - creating a sub-table with each marking period
mark, 200,000+ rows per year hurts my head.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.
4ax.com...
> On 22 Apr 2004 03:02:07 -0700, michael wrote:
>
ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Location4=Paris[vbcol=s
eagreen]
> SELECT Location1 AS Locations
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location2
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location3
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location4
> FROM Venues
> WHERE ID = 2
>
> By the way, your design is not properly normalized. The
> Venue-Locations should be in a seperate table.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all for the help. Maybe I can clear up why I
have the table the way it is and apologise for not giving
enough details about what I am trying to do. I had a sit
down and figured out what I was trying to do after I
posted the first message.
My venues table is linked (VenueID) to a events table. The
venues table has a min of 30 locations i.e
location1...location30 and would like to have a sproc to
return all the non null locations for a particular event
ID without having to have at least 30 "select union"
sections with tests for null values
Here is my first draft of what I am thinking
--
--The variable have been declared @.inti=1,@.intj=2
--@.Column1 @.Column2 (both char(5)
--and have not added the null test yet
While @.intj <=35
Begin
Set @.Column1 ='Location'+ (cast(@.inti as char(2)))
Set @.Column2 ='Location'+ (cast(@.intj as char(2)))
SELECT @.Column1 Locations FROM Venues where
Venues.LocationID='200'
UNION
SELECT @.Column2 FROM Venues where Venues.LocationID='200'
Set @.inti =@.inti + 2
Set @.intj =@.intj + 2
End
--This only results in the following
Locations
Location1
Location2
Locations
Location3
Location4
Any suggestions
>--Original Message--
>In response to "BTW, your design is not properly
normalized..."
>We really do not know what is being stored in the
LOCATION columns.
>If the 4 locations are somehow different - like location
1 is primary,
>location 2 is secondary - then this design is fine in my
book. If most of
>the time the app in front of this table shows the 4
locations on one row,
>then it's fine by me also. Normalization can and is
often taken to way to
>far a level.
>I've seen "college" admin systems with 500 tables - so
obsur that only the
>original implementors have a clue as to what is going on.
>In our K-12 student applications, we store all 4 marking
period marks in one
>row of a table. Each student/class has only one row,
with all 4 marking
>period marks within that row. In my book, they are
different "entities",
>thus this is properly normalized. I've had debates with
other programmers
>that they should be separated into a MARK table,
underneath the
>STUDENT/CLASS table. The STUDENT/CLASS table already
typically has 50000+
>rows per school per year - creating a sub-table with each
marking period
>mark, 200,000+ rows per year hurts my head.
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
message
> news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.
4ax.com...
column of[vbcol=seagreen]
> ID=2,Location1=Boston,Location2=NewYork,
Location3=London,L
ocation4=Paris
location1,location2[vbcol=seagreen]
>
>.
>

COLUMNS_UPDATED()

SQL Server 2000
BOL says:
The COLUMNS_UPDATED function returns the bits in order from left to right,
with the least significant bit being the leftmost. The leftmost bit
represents the first column in the table; the next bit to the right
represents the second column, and so on.
But in the example, bitmask to check the colums 2,3,4 calculated as 14,
in which rightmost bit is the first column in the table. is there something
wrong here?Yeah. what you are asking makes sense.
Looks like the bit stream is looked at as a string than a binary number, if
thats what you concern is.
Say for a 8 column table the first 4 are updated, then the columns_updated()
will read as
1111
and if 2 and 3 are updated its going to be
011
All it means is that the 0 has a significance to give out the position of
the column being updated or not and we cannot say 011 and 11 are equal in
this context.
and coming to your question,
14 is read is 0111 rather than 1110.
Its using a reverse binary system.. I believe.. But a good point you pointed
out nevertheless.
Lets wait for the other's comments though :)|||prefect a crit :
> SQL Server 2000
> BOL says:
> The COLUMNS_UPDATED function returns the bits in order from left to right,
> with the least significant bit being the leftmost. The leftmost bit
> represents the first column in the table; the next bit to the right
> represents the second column, and so on.
NOT AT ALL !
The bit calculate is based on the ordinal position deliver by
INFORMATION_SCHEMA.COLUMNS
Dmo :
CREATE TABLE T_TEST_BITCOLS_TBC
(COL1 INT,
COL2 INT,
COL3 INT,
COL4 INT,
COL5 INT)
GO
INSERT INTO T_TEST_BITCOLS_TBC VALUES (1, 2, 3, 4, 5)
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL2
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL3
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL5
GO
ALTER TABLE T_TEST_BITCOLS_TBC
ADD COL2 INT
GO
ALTER TABLE T_TEST_BITCOLS_TBC
ADD COL6 INT
GO
INSERT INTO T_TEST_BITCOLS_TBC VALUES (10, 20, 30, 40)
GO
CREATE TABLE T_TRIGGER_COLS_UPDATED_TCU
(TABLE_NAME SYSNAME,
BIT_COLS INT)
GO
CREATE TRIGGER E_U_TCU
ON T_TEST_BITCOLS_TBC
FOR UPDATE
AS
INSERT INTO T_TRIGGER_COLS_UPDATED_TCU
SELECT 'T_TEST_BITCOLS_TBC', COLUMNS_UPDATED()
GO
UPDATE T_TEST_BITCOLS_TBC
SET COL2 = 0
GO
SELECT *
FROM T_TRIGGER_COLS_UPDATED_TCU
TABLE_NAME BIT_COLS
-- --
T_TEST_BITCOLS_TBC 32
SELECT COLUMN_NAME, ORDINAL_POSITION,
POWER(2, ORDINAL_POSITION - 1) AS BIT_COL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'T_TEST_BITCOLS_TBC'
COLUMN_NAME ORDINAL_POSITION BIT_COL
-- -- --
COL1 1 1
COL4 4 8
COL2 6 32 <====
COL6 7 64

> But in the example, bitmask to check the colums 2,3,4 calculated as 14,
> in which rightmost bit is the first column in the table. is there somethi
ng
> wrong here?
YES !
>
A +
--
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||thanks , i guess rightmost bit of every byte corresponds to first one of
the every 8 column regarding the ordinal of column.
surely , bit order of columns is read from system tables.
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:OeCvlFFaGHA.1228@.TK2MSFTNGP02.phx.gbl...
> prefect a crit :
> NOT AT ALL !
>
> The bit calculate is based on the ordinal position deliver by
> INFORMATION_SCHEMA.COLUMNS
> Dmo :
>
> CREATE TABLE T_TEST_BITCOLS_TBC
> (COL1 INT,
> COL2 INT,
> COL3 INT,
> COL4 INT,
> COL5 INT)
> GO
> INSERT INTO T_TEST_BITCOLS_TBC VALUES (1, 2, 3, 4, 5)
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL2
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL3
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL5
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> ADD COL2 INT
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> ADD COL6 INT
> GO
> INSERT INTO T_TEST_BITCOLS_TBC VALUES (10, 20, 30, 40)
> GO
> CREATE TABLE T_TRIGGER_COLS_UPDATED_TCU
> (TABLE_NAME SYSNAME,
> BIT_COLS INT)
> GO
>
> CREATE TRIGGER E_U_TCU
> ON T_TEST_BITCOLS_TBC
> FOR UPDATE
> AS
> INSERT INTO T_TRIGGER_COLS_UPDATED_TCU
> SELECT 'T_TEST_BITCOLS_TBC', COLUMNS_UPDATED()
> GO
> UPDATE T_TEST_BITCOLS_TBC
> SET COL2 = 0
> GO
> SELECT *
> FROM T_TRIGGER_COLS_UPDATED_TCU
> TABLE_NAME BIT_COLS
> -- --
> T_TEST_BITCOLS_TBC 32
>
> SELECT COLUMN_NAME, ORDINAL_POSITION,
> POWER(2, ORDINAL_POSITION - 1) AS BIT_COL
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'T_TEST_BITCOLS_TBC'
> COLUMN_NAME ORDINAL_POSITION BIT_COL
> -- -- --
> COL1 1 1
> COL4 4 8
> COL2 6 32 <====
> COL6 7 64
>
>
>
> YES !
>
> A +
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

ColumnName Parameter for Table Valued Function

Hi All,

Is it possible to do the following:
1. I have a udf_xxx which returns Col1,Col2 and Col3
2. I need to join this udf_xxx to get Col1 and Col2 where Col3 matches with table tbl1.

SELECT udf.Col1, udf.Col2, t.Col3
FROM dbo.tbl1 t
JOIN dbo.udf_xxx (t.Col3) udf ON
t.Col3 = udf.Col3

Thanks in advance.In SQL Server 2005, you could use CROSS (OUTER) APPLY:
SELECT udf.Col1, udf.Col2, t.Col3
FROM dbo.tbl1 t
CROSS APPLY dbo.udf_xxx (t.Col3) udf|||I just happened to find out that too. Thanks Konstantin Kosinsky!

Column name not recognised even though the procedure returns a table with that name?

I am databinding a dataset formed from a stored procedure,(all done on page_load).

However, the reference to the column name is not recognised with the following error being returned.

Column with name "CalcVal" was not found.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.ArgumentException: Column with name "CalcVal" was not found.

Source Error:

Line 152: chartCommand.Fill(chartDs);Line 153: Chart1.DataSource = chartDs;Line 154: Chart1.DataBind();Line 155:Line 156: }

This worked before deployment and the other controls which are bound to data using stored procedures seem to work. However, these other data sources are all sqlDatasoure controls.

Any suggestions would be helpful.

I have found issues relating the to user ID and password which seems to depend on whether the web server and data server are the same machine or whether they are remote from each other.

cheers-jim.

The database folks left debug on which was returning an additionaltable that didn't have the column being referenced!!!

All solved. Cheers-jim

column collation

Hi,
I am using C++ to develop an application to support SQL Server 2005. I have
a problem whant I use SQLColAttribute function. It only returns "Collation
Name", instead of the real column's collation name. Does any body has any
idea?
Here it is my code:
retcode = SQLColAttribute(handleSTMT,i +1,
SQL_CA_SS_COLUMN_COLLATION ,
columnCollation,
256, &nameLength, &valueInInt);
Thanks for you help.
--Lijie
I believe is not supported in SQLColAttribute

column collation

Hi,
I am using C++ to develop an application to support SQL Server 2005. I have
a problem whant I use SQLColAttribute function. It only returns "Collation
Name", instead of the real column's collation name. Does any body has any
idea?
Here it is my code:
retcode = SQLColAttribute(handleSTMT,i +1,
SQL_CA_SS_COLUMN_COLLATION ,
columnCollation,
256, &nameLength, &valueInInt);
Thanks for you help.
--LijieI believe is not supported in SQLColAttribute

Column calculations

Hi,

I have a select query that returns three integer fields from a table the
values range from 0 to 5. On each row I would like to calculate the average
value in the three fields however, the difficulty is that only the rows
where the value is greater than 0 should be included in the calculation.

To make this clearer please consider the following example:

Col1 Col2 Col2 Average Calculation
1 2 3 2 (Col1+Col2+Col3)/3
2 2 0 2 (Col1+Col2+Col3)/2

The first row should be divided by 3 because each of the three columns has a
value greater than 0, however the second row should be divided by 2 because
only two of the value are greater than 0.

Is this possible?

Also is is possible to pass the results of one calculation into another
calculation.

Thanks in advance,

SteveSELECT col1, col2, col3, (col1 + col2 + col3)/(case when col1 = 0 then
0 else 1 end + case when col2 = 0 then 0 else 1 end + case when col3 =
0 then 0 else 1 end)
FROM My_Table

-Tom.|||(col1+col2+col3) /
(SIGN(col1)+ SIGN(col2)+ SIGN(col3))

If they can be negative, use ABS(SIGN (X))|||Thanks Tom,

This worked fine.

"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message
news:1109973262.691311.81820@.o13g2000cwo.googlegro ups.com...
> SELECT col1, col2, col3, (col1 + col2 + col3)/(case when col1 = 0 then
> 0 else 1 end + case when col2 = 0 then 0 else 1 end + case when col3 =
> 0 then 0 else 1 end)
> FROM My_Table
> -Tom.