Newbie question : sample of XML Application ?

Hi All,
I am new to XML.
Could anybody please tell me sample of aplication / cases study where we
can make use of XML
(in general or especially in SQL Server 2000)
Thank you for your help,
Krist
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I am not quite sure what you are looking for? Do you need a motivation to
use XML in the database? If you need that, you probably are fine with using
the relational feature and don't need to use XML. Otherwise,
http://msdn.microsoft.com/xml has whitepapers and other information...
Best regards
Michael
"Krist" <Kristanto@.me.com> wrote in message
news:uy4M%23m5KFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I am new to XML.
> Could anybody please tell me sample of aplication / cases study where we
> can make use of XML
> (in general or especially in SQL Server 2000)
> Thank you for your help,
> Krist
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Newbie question : sample of XML Application ?

Hi All,
I am new to XML.
Could anybody please tell me sample of aplication / cases study where we
can make use of XML
(in general or especially in SQL Server 2000)
Thank you for your help,
Krist
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I am not quite sure what you are looking for? Do you need a motivation to
use XML in the database? If you need that, you probably are fine with using
the relational feature and don't need to use XML. Otherwise,
http://msdn.microsoft.com/xml has whitepapers and other information...
Best regards
Michael
"Krist" <Kristanto@.me.com> wrote in message
news:uy4M%23m5KFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I am new to XML.
> Could anybody please tell me sample of aplication / cases study where we
> can make use of XML
> (in general or especially in SQL Server 2000)
> Thank you for your help,
> Krist
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Newbie Question

I am trying to sum some data in a table, and I can't figure it out. Below
is some sample data
Date,Customer,Order_Amt,Paid_Amt
2004-10-02 00:00:00,101,1418.82,-1400.00
2004-10-02 00:00:00,101,265.21,-200.00,
2004-10-02 00:00:00,101,648.74,-648.74
2004-10-02 00:00:00,102,95.95,-95.95
2004-10-02 00:00:00,102,457.42,-450.00
I trying to sum all of the orders for each customer from yesterday, without
showing each order, just the date, customer number, total orders, total
paid. I also want to add a 5th column stating order_amt-Paid_amt. Although
pathetic, this is the furthest I got :
select date, cust, order_amt, paid_amt from day_sales
where date > getdate()-2
group by date, cust, order_amt, paid_amt
I would these results :
date,cust,order_total,paid_total,amt_owe
d
2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
Any help would be greatly appreciated.
ThanksTry this:
select date, cust,
Count(*) OrderCount,
Sum(order_amt) TotalAmt,
Sum(paid_amt) TotalPaid,
Sum(order_amt-Paid_Amt) Balance
from day_sales
where date > DateAdd(day, -2, getdate())
group by date, cust
"J Abrams" wrote:

> I am trying to sum some data in a table, and I can't figure it out. Below
> is some sample data
> Date,Customer,Order_Amt,Paid_Amt
> 2004-10-02 00:00:00,101,1418.82,-1400.00
> 2004-10-02 00:00:00,101,265.21,-200.00,
> 2004-10-02 00:00:00,101,648.74,-648.74
> 2004-10-02 00:00:00,102,95.95,-95.95
> 2004-10-02 00:00:00,102,457.42,-450.00
> I trying to sum all of the orders for each customer from yesterday, withou
t
> showing each order, just the date, customer number, total orders, total
> paid. I also want to add a 5th column stating order_amt-Paid_amt. Althou
gh
> pathetic, this is the furthest I got :
> select date, cust, order_amt, paid_amt from day_sales
> where date > getdate()-2
> group by date, cust, order_amt, paid_amt
> I would these results :
> date,cust,order_total,paid_total,amt_owe
d
> 2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
> Any help would be greatly appreciated.
> Thanks
>
>|||Hi
Check out GROUP BY and SUM in books online.
Try (untested);
Select date, cust, SUM(order_amt), SUM(paid_amt), SUM(order_amt)
-SUM(paid_amt) AS Outstanding, COUNT(*) AS No_Orders from day_sales
where date > getdate()-2
group by date, cust
Your getdate()-2 may not give you the exact information required if you
have times with the orders to round to day one way is to use convert e.g.
Select CONVERT(CHAR(8),date,112) AS Date, cust, SUM(order_amt),
SUM(paid_amt), SUM(order_amt) -SUM(paid_amt) AS Outstanding, COUNT(*) AS
No_Orders from day_sales
where CONVERT(CHAR(8),date,112) >= CONVERT(CHAR(8),getdate()-2,112)
group by CONVERT(CHAR(8),date,112), cust
John
"J Abrams" wrote:

> I am trying to sum some data in a table, and I can't figure it out. Below
> is some sample data
> Date,Customer,Order_Amt,Paid_Amt
> 2004-10-02 00:00:00,101,1418.82,-1400.00
> 2004-10-02 00:00:00,101,265.21,-200.00,
> 2004-10-02 00:00:00,101,648.74,-648.74
> 2004-10-02 00:00:00,102,95.95,-95.95
> 2004-10-02 00:00:00,102,457.42,-450.00
> I trying to sum all of the orders for each customer from yesterday, withou
t
> showing each order, just the date, customer number, total orders, total
> paid. I also want to add a 5th column stating order_amt-Paid_amt. Althou
gh
> pathetic, this is the furthest I got :
> select date, cust, order_amt, paid_amt from day_sales
> where date > getdate()-2
> group by date, cust, order_amt, paid_amt
> I would these results :
> date,cust,order_total,paid_total,amt_owe
d
> 2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
> Any help would be greatly appreciated.
> Thanks
>
>|||thanks for your help, that did the trick. I have one quick question. How
does sql know how to count the total orders per customer ? What if I wanted
a count of all of the orders that equal 1418.82 ? I don't see anything in
the script below that points the count command to the customer number.
Thanks again for your help, I really appreciate it !!
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
> Try this:
> select date, cust,
> Count(*) OrderCount,
> Sum(order_amt) TotalAmt,
> Sum(paid_amt) TotalPaid,
> Sum(order_amt-Paid_Amt) Balance
> from day_sales
> where date > DateAdd(day, -2, getdate())
> group by date, cust
> "J Abrams" wrote:
>|||The group By statement says to SQL:
1) Collect all the records which match the criteria, and group them into one
groups, based on the values of the columns Cust, and Date, Then
2) Output ONE ROW for each of those GROUPS...
3) Any expression in the Select Clause, which has an aggregate function
(Sum, Count, Min, Max, etc.) IS then evaluated for ALL The records in each o
f
those constructed groups...
"J Abrams" wrote:

> thanks for your help, that did the trick. I have one quick question. How
> does sql know how to count the total orders per customer ? What if I want
ed
> a count of all of the orders that equal 1418.82 ? I don't see anything in
> the script below that points the count command to the customer number.
> Thanks again for your help, I really appreciate it !!
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
>
>|||Hi
If you wanted to restrict the whole query to add up certain rows i.e an
order_amt value of 1418.82 then you would need to add this to the where
clause.
i.e.
where date > DateAdd(day, -2, getdate())
and order_amt = 1418.82
You should notice that the OrderCount column is less than without the
additional clause (unless the customer only ever orders the one
amount!).
If you wanted to have an additional count that summed everything but
counted the number of times they ordered for an amout of 1418.82
select date, cust,
Count(*) AS OrderCount,
SUM(CASE WHEN order_amt = 1418.82 THEN 1 ELSE 0 END) AS
OrderedSpecificAmtCount,
Sum(order_amt) AS TotalAmt,
Sum(paid_amt) AS TotalPaid,
Sum(order_amt-Paid_Amt) AS Balance
from day_sales
where date > DateAdd(day, -2, getdate())
group by date, cust
The rest is in books online. Please spend some time reading it as is a
very rich source of information.
John
J Abrams wrote:
> thanks for your help, that did the trick. I have one quick question.
How
> does sql know how to count the total orders per customer ? What if I
wanted
> a count of all of the orders that equal 1418.82 ? I don't see
anything in
> the script below that points the count command to the customer
number.
> Thanks again for your help, I really appreciate it !!
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
out.
yesterday,
total

Newbie Question

Can any one please provide a sample of how to read a field in SQL database
using VBS? I'm trying to learn how to do something more complex then this,
but this should get me started, hopfully.
Thanks in advance
Here's a link to a simple ASP example. For straight VBS just remove the
Server. bits for CreateObject e.g.
Set DataConn = Server.CreateObject("ADODB.Connection")
becomes
Set DataConn = CreateObject("ADODB.Connection")
http://www.asp101.com/samples/viewas...database%2Easp
You can ignore the top bit and just look at the database access code. Should
give you some ideas. Also look at
http://www.w3schools.com/ado/default.asp
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"C_G" <CG@.discussions.microsoft.com> wrote in message
news:21C90F48-B7DF-47C7-AA6C-A3311676CE79@.microsoft.com...
> Can any one please provide a sample of how to read a field in SQL database
> using VBS? I'm trying to learn how to do something more complex then
> this,
> but this should get me started, hopfully.
> Thanks in advance

Newbie - Help required with Query (sample tables/data included)

** Also posted to: comp.databases.ms-sqlserver
Hi all,
I have 2 tables 'ZoneData' and 'ZoneUser'. The 'ZoneUser' table has a
column that refers
to a 'ZoneData' row. Table definitions and sample data are:
CREATE TABLE [dbo].[ZoneData](
[ZoneId] [int] NOT NULL,
[ZoneName] [nchar](10) NOT NULL,
[IsDefault] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ZoneUser](
[Id1] [int] NOT NULL,
[Id2] [int] NOT NULL,
[ZoneId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
GO
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
GO
Running the Query:
SELECT ZoneUser.*, ZoneData.IsDefault FROM ZoneData INNER JOIN ZoneUser
ON ZoneData.ZoneId = ZoneUser.ZoneId
Displays the data:
Id1 Id2 ZoneId IsDefault
100 1 3 1
100 1 2 0
100 1 4 0
101 2 5 0
101 2 1 0
101 2 3 1
102 3 5 0
103 4 3 1
104 5 2 0
104 5 5 0
For each combination of 'Id1' and 'Id2' there may be 0 or more rows
with different 'ZoneId' values.
The Problem: I would like to create a query that could return a row for
every
'Id1' and 'Id2' combination that showed the FIRST (in terms of 'first
found in
database' - not as a result of some sort order) row where 'IsDefault ==
0'
Using the above data, the output would be:
100 1 2 0 < ZoneId '2' id 1st row where 'IsDefault
# 1'
101 2 5 0
102 3 5 0
104 5 2 0
* There is no row returned for Id1=103 Id2=4 as there is no row where
'IsDefault = 0'
Has anyone got an idea on how I might do this? I'm using SQL Server
2005
cheers,
davedmm
Thanks for DDL.
I have added rowid column as IDENTITY property to ZoneUser table
CREATE TABLE [dbo].[ZoneData](
[ZoneId] [int] NOT NULL,
[ZoneName] [nchar](10) NOT NULL,
[IsDefault] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ZoneUser](rowid INT NOT NULL IDENTITY(1,1),
[Id1] [int] NOT NULL,
[Id2] [int] NOT NULL,
[ZoneId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
GO
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
GO
CREATE VIEW myview
AS
SELECT ZoneUser.*, ZoneData.IsDefault
FROM ZoneData INNER JOIN ZoneUser
ON ZoneData.ZoneId = ZoneUser.ZoneId
WHERE IsDefault=0
SELECT * FROM
(
SELECT *,(SELECT COUNT(*) FROM myview v
WHERE v.Id1=myview.Id1 AND v.rowId<=myview.rowId ) rnk
FROM myview
) AS Der WHERE rnk=1
ORDER BY id1
DROP VIEW myview
DROP TABLE ZoneData,ZoneUser
"dmm" <stope19@.optusnet.com.au> wrote in message
news:1141619306.147227.283440@.v46g2000cwv.googlegroups.com...
> ** Also posted to: comp.databases.ms-sqlserver
> Hi all,
> I have 2 tables 'ZoneData' and 'ZoneUser'. The 'ZoneUser' table has a
> column that refers
> to a 'ZoneData' row. Table definitions and sample data are:
>
> CREATE TABLE [dbo].[ZoneData](
> [ZoneId] [int] NOT NULL,
> [ZoneName] [nchar](10) NOT NULL,
> [IsDefault] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[ZoneUser](
> [Id1] [int] NOT NULL,
> [Id2] [int] NOT NULL,
> [ZoneId] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
> GO
>
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
> GO
>
> Running the Query:
>
> SELECT ZoneUser.*, ZoneData.IsDefault FROM ZoneData INNER JOIN ZoneUser
> ON ZoneData.ZoneId = ZoneUser.ZoneId
>
> Displays the data:
>
> Id1 Id2 ZoneId IsDefault
> 100 1 3 1
> 100 1 2 0
> 100 1 4 0
> 101 2 5 0
> 101 2 1 0
> 101 2 3 1
> 102 3 5 0
> 103 4 3 1
> 104 5 2 0
> 104 5 5 0
>
> For each combination of 'Id1' and 'Id2' there may be 0 or more rows
> with different 'ZoneId' values.
>
> The Problem: I would like to create a query that could return a row for
> every
> 'Id1' and 'Id2' combination that showed the FIRST (in terms of 'first
> found in
> database' - not as a result of some sort order) row where 'IsDefault ==
> 0'
> Using the above data, the output would be:
>
> 100 1 2 0 < ZoneId '2' id 1st row where 'IsDefault
> # 1'
> 101 2 5 0
> 102 3 5 0
> 104 5 2 0
>
> * There is no row returned for Id1=103 Id2=4 as there is no row where
> 'IsDefault = 0'
>
> Has anyone got an idea on how I might do this? I'm using SQL Server
> 2005
>
> cheers,
> dave
>|||Thanks for the help Uri. Much appreciated.
cheers,
dave

Newbie - Help required with Query (sample tables/data included)

Hi all,

I have 2 tables 'ZoneData' and 'ZoneUser'. The 'ZoneUser' table has a
column that refers
to a 'ZoneData' row. Table definitions and sample data are:

CREATE TABLE [dbo].[ZoneData](
[ZoneId] [int] NOT NULL,
[ZoneName] [nchar](10) NOT NULL,
[IsDefault] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ZoneUser](
[Id1] [int] NOT NULL,
[Id2] [int] NOT NULL,
[ZoneId] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
GO

INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
GO

Running the Query:

SELECT ZoneUser.*, ZoneData.IsDefault FROM ZoneData INNER JOIN ZoneUser
ON ZoneData.ZoneId = ZoneUser.ZoneId

Displays the data:

Id1Id2ZoneIdIsDefault
100131
100120
100140
101250
101210
101231
102350
103431
104520
104550

For each combination of 'Id1' and 'Id2' there may be 0 or more rows
with different 'ZoneId' values.

The Problem: I would like to create a query that could return a row for
every
'Id1' and 'Id2' combination that showed the FIRST (in terms of 'first
found in
database' - not as a result of some sort order) row where 'IsDefault ==
0'
Using the above data, the output would be:

100120 < ZoneId '2' id 1st row where 'IsDefault # 1'
101250
102350
104520

* There is no row returned for Id1=103Id2=4 as there is no row where
'IsDefault = 0'

Has anyone got an idea on how I might do this? I'm using SQL Server
2005

cheers,
daveHi, Dave

Your DDL has no primary keys, foreign keys or unique constraints.
This is a serious mistake, because:
1. It allows bad data to be entered in the tables
2. It prevents us from understanding the meaning of your tables, so we
cannot provide a good answer without them.

I assume the following constraints:
ALTER TABLE ZoneData ADD PRIMARY KEY (ZoneId), UNIQUE (ZoneName)
ALTER TABLE ZoneUser ADD UNIQUE (ZoneId, Id1)
ALTER TABLE ZoneUser ADD FOREIGN KEY (ZoneId) REFERENCES ZoneData

There is no such thing as "first found in database". By definition,
tables are unordered sets of rows. We have to use a sort criteria to
specify which is the first row.

First time I read your message, I believed you wanted something like
this:

SELECT U.*, D.IsDefault
FROM ZoneData D
INNER JOIN ZoneUser U ON D.ZoneId = U.ZoneId
INNER JOIN (
SELECT ZoneId, MIN(Id1) as MinOfId1
FROM ZoneUser GROUP BY ZoneId
) X ON U.ZoneId=X.ZoneId AND U.Id1=MinOfId1
WHERE D.IsDefault=0

The above query returns the following results:

Id1 Id2 ZoneId IsDefault
---- ---- ---- ---
101 2 1 0
100 1 2 0
100 1 4 0
101 2 5 0
(4 row(s) affected)

Are you sure you don't want these results instead of what you wrote ?

If you are sure, I'm going to try writing another query that will
return what you wrote (but it doesn't have a lot of sense). Maybe you
will tell us what Id1 and Id2 mean, so we can better understand what
you want to do.

Razvan|||Hi Razan,

Thanks for your reply. Your comment re 'database order' not existing
has me thinking perhaps my concept of what I want to do may be wrong. I
will consider your reply in detail to see where I might have 'got
lost'. Thanks you for taking the time to explain this.

cheers,
dave|||Do not use assembly language style bit flags in a high level language
like SQL. Use a sequence number for zones, if the zone-id will not do
the job. All relationships have to be expressed as values in columns
in tables. You never refer to the physical storage in a quiery.

CREATE TABLE Zones
(zone_id INTEGER NOT NULL PRIMARY KEY
zone_name CHAR(10) NOT NULL,
zone_rank INTEGER DEFAULT 0 NOT NULL
CHECK (zone_rank > 0),
UNIQUE (zone_id, zone_rank))
);

CREATE TABLE ZoneUsers
(user_id_1 INTEGER NOT NULL,
user_id_2 INTEGER NOT NULL,
PRIMARY KEY (user_id_1, user_id_2),
zone_id INTEGER NOT NULL
REFERENCES Zones(zone_id)
);

SELECT U.user_id_1, U.user_id_2, U.zone_id, MIN(Z.zone_rank)
FROM ZoneUsers AS U, Zones AS Z
WHERE Z.zone_id = U.zone_id
GROUP BY U.user_id_1, U.user_id_2, U.zone_id;|||Thanks for the comments. Looks like I have some work to do!

cheers,
dave|||After 20+ years of SQL, I tell people it takes one year of full-time
programming with college -level education to be an SQL programmer.
This is cheap; it takes 6 yers to become a Union Journeyman Carpenter
in New York State.

A bad ptrogrammer can kill or maim a lot more people than a bad
carpenter.|||>A bad ptrogrammer can kill or maim a lot more people than a bad
carpenter.

Oh?? I'll bite. How does a programmer kill or maim a lot of people?|||"Doug" <drmiller100@.hotmail.com> wrote in message
news:1141706342.190761.230170@.j33g2000cwa.googlegr oups.com...
> >A bad ptrogrammer can kill or maim a lot more people than a bad
> carpenter.
> Oh?? I'll bite. How does a programmer kill or maim a lot of people?

One of several ways.

There's a recent case in Panama where a radiological machine used to deliver
doses of radiation to kill cancer was improperly used and killed a number of
patients. Besides the techs being indicted there was at least talk of
bringing the programmers to court since they wrote the software that
permitted the misuse of the machine w/o proper feedback.

http://www.findarticles.com/p/artic...3/ai_ziff120920

Or imagine the case of the Shuttle Software (which is among the most
"perfect" ever written) where a condition was found (preflight fortunately)
that locked up the shuttle arm. Evidently the programmer made a simply
mistake and assumed that its rotational functionality extended from 0 to 360
degrees rather than 1-360 or 0-359. A search found a couple of other places
where a similar error (i.e. overrunning by 1) was in the code.

In the case of the arm, they could have jestisoned it. In the case of a
botched landing, a similar error could have crashed the shuttle.

It's not hard to imagine extended such errors to avionics software or
software controlling a nuclear reactor, etc.

Newbee:reporting services gets: Connection could not be made to reportserver

When I try to deploy the sample reports solution to the local IIS on this
win2003 server, the message "A connection could not be made to the report
server http://a1900/Reportserver" appears.
I do this by:
1. Loading the SampleReports.sln into the vb.net IDE.
2. Right-clicking on the SampleReports solution, then selecting Properties,
then setting 'TargetServer URL'=http://a1900/Reportserver (it also fails
when I set it to 'http://localhost/ReportServer'
3. Right-clicking on the SampleReports solution, then selecting 'Deploy'.
4. No errors during the build phase.
The IIS Manager window shows in the left pane:
iis information services
- a1900 (local computer)
-FTP Sites
--...
-Web Sites
-- Default Web Site
--_vti_bin
--reports
-- reportServer
etc.
-Web Service Extensions
...
The IIS is on the same machine that I operate on and has entries of 'Default
Web Site\Reportserver' and 'Default Web Site\Reports'. The 'Default Website'
has 'Permissions' set to 'Full control' for administrators, as which I am
logged in.
I notice that the 'Reports' and 'ReportServer' entries do not have the blue
globe icon (?) that most of the other IIS folder entries have. Perhaps
that's the cause of my problem.
Do I have to add Web service extensions for Reporting Services?
Perhaps the Reporting services installation did not go right?
How do I find the correct url for the target server?
Where do I have to look.
What books to read?
Any help is greatly appreciated.
Thanks.Some questions & answers:
"Do I have to add Web service extensions for Reporting Services? "
No.
"How do I find the correct url for the target server?"
You are using the correct URL. To test it manually, navigate to the
following path:
http://localhost/ReportServer/ReportService.asmx
This is the webservice that the "Deploy" option uses based upon the
"TargetServer" property you set in the Project settings.
You should receive a SOAP response (XML) w-hen you navigate to that
URL. If you do not get this, then you may have an IIS configuration
problem. Because of the security configurations needed, I generally
just reinstall Reporting Services to make sure the Virtual Directory
and permissions are setup properly. In IIS 6, you should see what
looks like a cog/gear as the symbol for the ReportServer virtual
directory. The globes are for websites (DefaultWeb and such) which
isnt required.
One other thing to check is if your DefaultWebsite is using port 80,
and accepts "(All Unassigned)" IP addresses. Anything unusual in
these IIS settings may indicate part of your problem.
I hope this helps a bit...
~Lance Hunt
http://weblogs.asp.net/lhunt/