Dynamic SQL Command: table

Showing posts with label table. Show all posts

Thursday, March 29, 2012

Edit data with Query Analyzer

Is there any way I can use Query Analyzer to edit data in a table via a
graphic grid, i.e. actually changing individual values in cells? Using
SQL statements is too cumbersome for my purpose; I need to have more
hands-on control.Rick,
Use the Object Browser (F8) --> right-click table --> Open.
HTH
Jerry
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1dc1e9977390b6f9989901@.msnews.microsoft.com...
> Is there any way I can use Query Analyzer to edit data in a table via a
> graphic grid, i.e. actually changing individual values in cells? Using
> SQL statements is too cumbersome for my purpose; I need to have more
> hands-on control.|||Rick Charnes wrote:
> Is there any way I can use Query Analyzer to edit data in a table via
> a graphic grid, i.e. actually changing individual values in cells?
> Using SQL statements is too cumbersome for my purpose; I need to have
> more hands-on control.
Some tables yes and some tables no. I've never figures out why. I would
use SQL Enterprise Manager instead if you absolutely have to edit the
data in a table in this fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I will give another solution, you can actually do everything you want to
manually. even queries can be developed using that... wondering what is it..
it is around every one but rarely people use that is access adp. More so, yo
u
can develop forms/ and reports from sql server data with ease.
follow these simple steps,
1.open access(better xp or above)
2.select new
3.select new project from exisiting data in the side pane( by this select
adp project not mdb)
4.data link dialogue appears, fill credentials and connection details
5.Rest is the world that you wont imagine
--
Regards
R.D
--Knowledge gets doubled when shared
"David Gugick" wrote:

> Rick Charnes wrote:
> Some tables yes and some tables no. I've never figures out why. I would
> use SQL Enterprise Manager instead if you absolutely have to edit the
> data in a table in this fashion.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||> Some tables yes and some tables no. I've never figures out why.
I always thought it is depending on whether the table has a PK, UQ, unique i
ndex or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eIQsIpc1FHA.3520@.TK2MSFTNGP10.phx.gbl...
> Rick Charnes wrote:
> Some tables yes and some tables no. I've never figures out why. I would us
e SQL Enterprise Manager
> instead if you absolutely have to edit the data in a table in this fashion
.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||David Gugick wrote:
> Rick Charnes wrote:
> Some tables yes and some tables no. I've never figures out why.
I've noticed that tables with no unique constraint of any
kind appear as read-only, but once you add a primary key for example,
you are able to modify data.
If QA front end is written in MFC, then it is probably related
to the behavior of MFC recordset, which is also read-only after
pulling data from such a table.|||Tibor Karaszi wrote:
> I always thought it is depending on whether the table has a PK, UQ,
> unique index or not.
That's probably the case.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Enterprise manager can do that, although I would not do it except when
creating a database for a non-live project.
All data manipulation needs to be done through applications such as
databrowsers and so on.
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1dc1e9977390b6f9989901@.msnews.microsoft.com...
> Is there any way I can use Query Analyzer to edit data in a table via a
> graphic grid, i.e. actually changing individual values in cells? Using
> SQL statements is too cumbersome for my purpose; I need to have more
> hands-on control.

Easy way to remove international alphabet from all rows?

Is there an easy way to loop through all rows and remove all
international alphabet characters from a column in a table, for example
remove German umlauts "" and convert them to a simple "u".
Thanks,
lqSee the following thread:
http://groups-beta.google.com/group...b45abaa5cf410b8

Razvan|||OK< thanks on that. How about keeping out these characters in the first
place?|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Is there an easy way to loop through all rows and remove all
> international alphabet characters from a column in a table, for example
> remove German umlauts "" and convert them to a simple "u".

Here is a much smarter version than the one in the linkn Razvan foun

create table T (
s nvarchar(20)
)
insert into T values ('cre')
insert into T values ('pt de fois gras')
insert into T values ('HLNE MELANON')

update T
set s = convert(varchar(20), s) COLLATE Cyrillic_General_CS_AS
go
select * from T
go
drop table T

This assumes that your data only has Latin characters.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> OK< thanks on that. How about keeping out these characters in the first
> place?

I don't know, nuke the parts of the world where we insist on using other
languages than English?

Seriously, I think it would be a very bad idea. If you want to send
mail to these persons, you do want to spell their names properly, don't
you?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's only one of the problems! I want to store the data with the
international characters but then I need to strip out the international
characters for certain output, such as in e-mail addresses...
lq|||Technically, international characters are permitted in email addresses
and domain names so stripping them out is still a bad idea.

You'll have to build your own table to translate characters that you
deem to be unacceptable. Google for some conversion tables but there
isn't really a single answer because it depends on which characters you
need to support (don't forget the two-byte character sets used in the
Far East and elsewhere for example).

--
David Portas
SQL Server MVP
--|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> That's only one of the problems! I want to store the data with the
> international characters but then I need to strip out the international
> characters for certain output, such as in e-mail addresses...

I would recomment that you store the e-mail address. While the e-mail
address often can be formed as firstname.lastname@.domain.xxx, this is
not a requirement.

And the local transformation may not be the one you expect. in German
after often replaced with ae, oe and ue. (Whether this is actually common
in mail addresses, I don't know.)

If you are thinking of the comment part of the e-mail address:

John Smith <johnsmith@.example.com
(John Smith is the comment here) You should either encode the comment
according to MIME as per RFC2047, or send as-is. But don't misspell
people's names.

(By the way, permit me to point out that the term "international characters"
is a misnomer. The internationalest characters I can think of is the
English alphabet A-Z. Non-ASCII characters is a better term, and
technically precise.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Thanks for that. You're right, I do mean non-ASCII characters...
This gets tricky because many city names and people names contain
non-ASCII characters and I want to display then properly, however, some
systems do not support non-ASCII characters and they need to be
cleansed for output to those systems, so as you point out, there is not
an always correct translation into ASCII characters.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Thanks for that. You're right, I do mean non-ASCII characters...
> This gets tricky because many city names and people names contain
> non-ASCII characters and I want to display then properly, however, some
> systems do not support non-ASCII characters and they need to be
> cleansed for output to those systems, so as you point out, there is not
> an always correct translation into ASCII characters.

Systems in 2005 that is not capable to handle non-ASCII characters? So,
not all programs are capable to handle Unicode, but one would at least
imagine that they should be able handle 8-bit charset. If I were you,
I would consider to attempt to kill these systems by starvation and
not feed them any data. :-)

On a more serious point, there are one letter that the snippet I
posted does not handle and that is . This gets converted into a ?.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Windows file names is an example on one such system...|||Which Windows are you referring to? FAT32? NTFS does support non-ASCII
characters in files names, at least it does under Win 2003 so I don't
know why it wouldn't under other versions.

--
David Portas
SQL Server MVP
--|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Windows file names is an example on one such system...

The 8.3 names, yes, but you rarely have to see them. Even less rarely
have to form them. As long as you are on NTFS you can use the whole range
of Unicode, and use letters from any script. The FAT files systems may
not support this, but I'm pretty sure that they support the full range
of the current ANSI code page.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Easy way to index a column with URLs

We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?If you need to enforce uniqueness, or you are frequently searching the table
by the URL column, then an index is appropriate. Bear in mind that an index
key can be no more than 900 bytes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Kevin" <none@.none.com> wrote in message
news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?|||I'm afraid that some of the urls possibly could get over that size after
encoding (the URL column is nvarchar).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
> If you need to enforce uniqueness, or you are frequently searching the
> table
> by the URL column, then an index is appropriate. Bear in mind that an
> index
> key can be no more than 900 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Kevin" <none@.none.com> wrote in message
> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
> We have a column that stores URL (varchar), which ultimately are unique
> within the table (though an identity is the actual primary key). Is it
> good
> practice to put an index on such a wide column? Are there are other
> alternatives, possibly different data type to use?
>|||"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>> If you need to enforce uniqueness, or you are frequently searching the
>> table
>> by the URL column, then an index is appropriate. Bear in mind that an
>> index
>> key can be no more than 900 bytes.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Kevin" <none@.none.com> wrote in message
>> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
>> We have a column that stores URL (varchar), which ultimately are unique
>> within the table (though an identity is the actual primary key). Is it
>> good
>> practice to put an index on such a wide column? Are there are other
>> alternatives, possibly different data type to use?
>|||Kevin,
if you badly want this column be indexed (of those values that do not excede
the 900 bites limit), you can do some more work:
create an indexed view that have a pk column and the URL column, with the
URL records no longer than 900 bites, index on the URL column;
create an indexed view that have a pk column and the URL column, with the
URL records longer than 900 bites, index on the pk column;
chances are that you may have a handful of records in the second view so
that you do not care to index scan it. And you have index seek on the
shorter URL values. Then join the views with your base table.
HTH
Quentin
"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>> If you need to enforce uniqueness, or you are frequently searching the
>> table
>> by the URL column, then an index is appropriate. Bear in mind that an
>> index
>> key can be no more than 900 bytes.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Kevin" <none@.none.com> wrote in message
>> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
>> We have a column that stores URL (varchar), which ultimately are unique
>> within the table (though an identity is the actual primary key). Is it
>> good
>> practice to put an index on such a wide column? Are there are other
>> alternatives, possibly different data type to use?
>sql

Easy way to index a column with URLs

Tuesday, March 27, 2012

easy trigger

I create trigger no table person. When I invoke sql command
insert into person values (... bla bala bal)
trigger is autommaticaly fired.
The trigger must log sql commads, so i must insert into table log this command, whos execute triger - insert into person values (... bla bala bal).
How I can do it?Make sure your database is set to allow cascading triggers, then your trigger should be able to both log its own insert and initiate other inserts.

blindman|||This in no problem.
Problem is:
How to find out what statement is making the trigger run and get this statement in trigger code

Originally posted by blindman
Make sure your database is set to allow cascading triggers, then your trigger should be able to both log its own insert and initiate other inserts.

blindman|||The trigger has no idea what caused the insert, update, or delete. The best you can do is reference some of the nyladic functions (look them up) that return login and user information. Then you can at least see WHO or WHAT LOGIN initiated the process.

blindman|||Well...you can tell what type...

INSERT: Rows in inserted, none in deleted
DELETE: Rows in deleted, mone in inserted
UPDATE: Rows in both

...|||The following example uses a table called PARENT as the base for INSERT TRIGGER:

if object_id('dbo.tblLog') is not null
drop table dbo.tblLog
go
create table dbo.tblLog (
EventType varchar(15) null,
Parameters int null,
EventInfo varchar(8000) null)
go
if object_id('dbo.trig_parent') is not null
drop trigger dbo.trig_parent
go
create trigger dbo.trig_parent on dbo.parent for insert as
set nocount on
declare @.cmd varchar(8000)
set @.cmd = 'create table #tbl (
EventType varchar(15) null,
Parameters int null,
EventInfo varchar(8000) null);'
set @.cmd = @.cmd + 'insert #tbl '
set @.cmd = @.cmd + 'exec ('
set @.cmd = @.cmd + char(39)+'dbcc inputbuffer (' + cast(@.@.spid as varchar(25)) + ') '
set @.cmd = @.cmd + 'with no_infomsgs' + char(39)
set @.cmd = @.cmd + ');'
set @.cmd = @.cmd + 'insert dbo.tblLog select * from #tbl'
exec (@.cmd)
go

Easy to Say but hard to implement

I have a table named Holding_Value that has several fields in it among
which are UID, fkHolding, EffDate, Units, MarketValue, AssetPrice. UID
is an identity field and fkHolding is a foreign key to a different
table. EffDate is the the effective date while units and marketvalue
are values stored in the table.
what i'm trying to do is get all the values (fkHolding, Effdate, Units,
MarketValue) for all fkHolding for a specific date. That would be
pretty easy if there each unique fkHolding had a corresponding value
for every date. The exception is that if no date is found than you
would have to get the next date less then or equal ot the query date.
To furhter explain assume that there 100 records in the table and there
are only 10 distinct fkHolding values. My result will need to include
only 10 records. Each record will have the values of the row containing
the values less than or equal to the given date for a specific given
date. so if given date (EffDate) is 12/1/2004 and 5 of the 10 distinct
fkHolding have been priced on that date, than we get those values, the
rest 5 rows in the resultset need to be the values of of the latest
date less than the given date.

Now the second problem is that this needs to be efficient because this
is only a part of my subquery and the table does not have 100 records
but a few million records. Now what i can do is get the latest value if
i were given an fkHolding for example i would write

declare @.fkHolding as integer
declare @.DateValue as datetime
select @.fkHolding = 2981
select @.DateValue = '9/2/2004'

select Holding_Values.UID, Holding_Values.EffDate,
Holding_Values.fkHolding, Holding_Values.AssetPrice,
Holding_Values.MarketValue
from Holding_Values INNER JOIN
(select max(Holding_Values.effdate) as DatePriced from
Holding_Values INNER JOIN
(select * from Holding_values where fkHolding = @.fkHolding and
Holding_Values.EffDate < @.DateValue) as a
on a.UID = Holding_values.UID ) as b
on Holding_Values.EffDate = b.DatePriced and Holding_Values.fkHolding =
@.fkHolding

or also would write it in the same way taking a different approach:

declare @.fkHolding as integer
declare @.DateValue as datetime
select @.fkHolding = 2981
select @.DateValue = '9/2/2004'

select Top 1 Holding_Values.UID, Holding_Values.EffDate,
Holding_Values.fkHolding,
Holding_Values.AssetPrice, Holding_Values.MarketValue from
Holding_Values INNER JOIN
(select * from Holding_values where fkHolding = @.fkHolding and
Holding_Values.EffDate < @.DateValue) as a
on a.UID = Holding_values.UID
Order by Holding_Values.EffDate desc

Both these queries produce a row each when ran for a specific date and
fkHolding. Now the challege is to get all the latest distinct fkHolding
values given only a date.
Thank you for your time and help.

Gent MetajOn 15 Dec 2004 12:07:48 -0800, Gent wrote:

(snip)
>what i'm trying to do is get all the values (fkHolding, Effdate, Units,
>MarketValue) for all fkHolding for a specific date. That would be
>pretty easy if there each unique fkHolding had a corresponding value
>for every date. The exception is that if no date is found than you
>would have to get the next date less then or equal ot the query date.
(snip)

Hi Gent,

Since you didn't post CREATE TABLE and INSERT statements to recreate your
situation, I couldn't test it, but something like this should do the
trick:

SELECT h.UID, h.EffDate, h.fkHolding, h.AsseetPrice, h.MarketValue
FROM Holding_Values AS h
WHERE h.EffDate <= @.DateValue
AND NOT EXISTS (SELECT *
FROM Holding_Values AS h2
WHERE h2.EffDate <= @.DateValue
AND h2.EffDate > h.EffDate)

Another way to do it (test them both to see which one gives you the best
performance):

SELECT h.UID, h.EffDate, h.fkHolding, h.AsseetPrice, h.MarketValue
FROM Holding_Values AS h
INNER JOIN (SELECT fkHolding, MAX(EffDate) AS EffDate
FROM Holding_Values
WHERE EffDate <= @.DateValue
GROUP BY fkHolding) AS h2
ON h.fkHolding = h2.fkHolding
AND h.EffDate = h2.EffDate

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||It would have been a lot easier to say if you'd posted some DDL, sample data
and expected results.
http://www.aspfaq.com/etiquette.asp?id=5006

What are the key(s)? Please include them with CREATE TABLE statements. Keys
are going to make a big difference to any query. Without sample data I'm
also unclear if your required result is to include ALL rows for the latest
date for each fkholding or just ONE row for each.

Is this it?

CREATE TABLE Holding_Values (uid INTEGER NOT NULL, effdate DATETIME NOT
NULL, fkholding INTEGER NOT NULL, assetprice INTEGER NOT NULL, marketvalue
INTEGER NOT NULL /* ? PRIMARY KEY NOT SPECIFIED */)

SELECT H.uid, H.effdate, H.fkholding, H.assetprice, H.marketvalue
FROM Holding_Values AS H,
(SELECT fkholding, MAX(effdate) AS effdate
FROM Holding_Values
WHERE effdate <= @.datevalue
GROUP BY fkholding) AS D
WHERE H.fkholding = D.fkholding
AND H.effdate = D.effdate

--
David Portas
SQL Server MVP
--|||>> have a table named Holding_Value that has several fields [sic] in
it .. <<

Where is the DDL? And tables have columns which are completely
different from fields

>> .. among which are UID, fkHolding, EffDate, Units, MarketValue,
AssetPrice. UID is an identity field [sic] and fkHolding is a foreign
key to a different
table. EffDate is the the effective date while units and marketvalue
are values stored in the table. <<

INDENTITY is never a key and should not be used. There is no magical
"Univerisal Identifier"; do you also believe that God put a 17-letter
hebrew number to everything in Creation? That is how silly using
IDENTITY for a key in an RDBMS is.

A name element tells us what the entity or attribute is in terms of a
data model. You do not use affixes to tell us HOW it is used in one
occurrence in one table. The name "fkHolding" looks slightly obscene
(sorry, but it looks like "F**kHolding" to me).

"Holding_Values" is an attribute, not an entity name. This is a
hisotry, so use that in the name. If you have an asset_price, where is
the asset? I am guessing that used two names for the same entity, so
the holding is the asset.

Get the ISO-11179 Standards or any book on data modeling.

Again, without DDL and proper keys, here is my wild guess:

SELECT H1.*
FROM HoldingHistory AS H1
INNER JOIN
(SELECT asset_id, MAX(eff_date)
FROM HoldingHistory
WHERE eff_date <= @.report_date
GROUP BY asset_id)
AS H2(asset_id, eff_date)
ON H1.asset_id = H2.asset_id
AND H1.eff_date = H2. eff_date;|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message >
....
>INDENTITY[sic] is never a key and should not be used. .
....
I keep hearing this, and to some extent agree, however, I also keep seeing
it in use, and when I'm running a
quick scenario, I use identity to generate a key for small data sets.
Can you expound or give links / ref to articles that go into detail on this.

>The name "fkHolding" looks slightly obscene....
fk is used as prefix to indicate Foreign Key.

Kevin Ruggles|||kevin ruggles wrote:
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message >
> ...
>>INDENTITY[sic] is never a key and should not be used. .
> ...
> I keep hearing this, and to some extent agree, however, I also keep seeing
> it in use, and when I'm running a
> quick scenario, I use identity to generate a key for small data sets.
> Can you expound or give links / ref to articles that go into detail on this.

Do a google search of this group. It is usually discussed, passionately,
every couple months.

Zach|||Hugo you got it right. I guess i had a brain fart, i was not grouping
by fkHolding when i was trying to do the query. The first approach is a
drag. I let it run for over 2 minutes with no results (indexes might
have something to do wiht it too) but the second one worked like a
charm. It took less than 1 second for a 2.5 million record table. David
Portas solutions works as well.

In response to CELKO's comment about Identity and Primary key I
remember one of my database professors recommending against a while
back ago, but we seem to use that quite often here at work, and i see
identity used a lot as primary key. My prof did not elaborate too much
on why it was a stupid idea to use an identity as primary key but i
would appreciate if someone else has more info.
And fk is a convention often used to mean Foreign Key.

Thanks,

Gent|||> I use identity to generate a key for small data sets.
> Can you expound or give links / ref to articles that go into detail
on this

A search of the web and the microsoft.public.sqlserver.programming
hierarchy will find you many, many articles by Celko and others on this
topic.

> when I'm running a
> quick scenario, I use identity to generate a key for small data sets

What for? IDENTITY is part of a physical implementation not part of the
logical model of your data. If you post a CREATE TABLE statement here,
for example, just with just an IDENTITY column but don't identify any
other key then that tells us nothing about the entities involved and
we'll probably have a much harder time trying to solve your problem. In
modelling and problem-solving scenarios it is usually the natural key
of your data matters. (IDENTITY prompts other problems of its own of
course, but that's where you came in...)

> fk is used as prefix to indicate Foreign Key

Reasonable people differ when it comes to naming conventions. However,
I'll bet if you take a quick poll you'll find that most SQL pros (good
ones anyway) loathe to see prefixes on column and table names. One
reason is that if you represent structure and datatype and other info
in identifiers and then that metadata changes you have to change the
identifier even though the data element itself hasn't changed. Another
reason is that they are harder to type and remember. The standards
document that Joe cited defines some naming conventions for data
elements.

--
David Portas
SQL Server MVP
--|||>> one of my database professors recommending against a while back ago,
but we seem to use that quite often here at work, and I see identity
used a lot as primary key. <<

For the technical reasons that have to do with portability, relational
design and data integrity, you can Google my name and IDENTITY to some
of my rants.

The *real* reasons have to do with human behavior. In the working world,
RDBMS systems come from legacy file systems and untrained legacy file
system programmers. They mimic the designs they learned in the old
technology.

There was little separation of logical and physical data. Physically
contigous fields made up *physically contigous* records in files that
were in a sequence on a magnetic tape. The tape was sorted on a key and
all the EDP depended on that sort order to locate a record -- you did
not do random access on a tape.

Then comes the RDBMS, with the concept of relational keys. This meant
you had to know your data model, you had to do research! That's work!
It is so much easier to use some proprietary exposed physical locator
like IDENTITY or a row number to mimic the physical position of record
at the end of a magnetic tape. You can write code with cursors and
completely mimic a 3GL programming language.

You get to SQL and you have to think in sets and in much more complex
logic. It is hard work if you have never had a class in set theory or
formal logic. So people avoid it with IDENTITY and often miss needed
constraints for real key. It also gives them the feeling that the table
is normalized because it has this "false key" and they leave all kinds
of flaws in the schema. You can clean the results in the front end,
just like you did with COBOL in 1968, right?

>> And fk is a convention often used to mean Foreign Key.<<

I know, I know, but that was too good a straight line not to use :)

Seriously, you name something for *what it is* in the logical data
model, not for *how it is used* in one particular table. Would drop the
FK- prefix if it were used as a non-key column in another table? Do you
use a PK- prefix on it in the the referenced table? Would add "<table
name>-" prefixes for every occurence of the data element? When you sign
a check, do you change your name to include the room you are in at the
time?

The rule is that a data element has one and only one name, one and only
one meaning. This why a data dictionary can work. The only time you
change a data element name is when it occurs in two or more roles in a
query. Thus, "boss_emp_id" and "worker_emp_id" are both "emp_id"
values, but play two different roles.

--CELKO--
Please post DDL in a human-readable format and not a machne-generated
one. This way people do not have to guess what the keys, constraints,
Declarative Referential Integrity, datatypes, etc. in your schema are.
Sample data is also a good idea, along with clear specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||--CELKO-- (remove.jcelko212@.earthlink.net) writes:
> The *real* reasons have to do with human behavior. In the working world,
> RDBMS systems come from legacy file systems and untrained legacy file
> system programmers. They mimic the designs they learned in the old
> technology.
> There was little separation of logical and physical data. Physically
> contigous fields made up *physically contigous* records in files that
> were in a sequence on a magnetic tape. The tape was sorted on a key and
> all the EDP depended on that sort order to locate a record -- you did
> not do random access on a tape.

This is complete bullshit. You and few more people may actually have
programmed against tapes, but most of us haven't.

There's no need to involved tapes and other forms of arcane computer
technology to explain why the concept of an artificial key is popular.
Simpler and even older technique is more applicable, technique that is
still in use: pen and paper.

> It is so much easier to use some proprietary exposed physical locator
> like IDENTITY or a row number to mimic the physical position of record

IDENTITY has nothing to do with a physical location. The row may be
send around - the automatically assigned value for the row will be the
same.

You seriously need to learn how modern RDBMS work, Celko. And unlearn
what you happen to know about magtapes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> This is complete bullshit. You and few more people may actually have
programmed against tapes, but most of us haven't. <<

That does not matter. The newbies are re-discovering tape file systems
without every having worked on them. When you were taking computer
science courses, how many times did you "invent" an algorithm that other
people already knew?

Sequential files are a very natural way to look at data. It mimics
paper forms, index cards and a single processor (person). These evolved
into notched edge cards and then sorted punch cards and then sequential
mag tapes.

>> here's no need to involved tapes and other forms of arcane computer
technology to explain why the concept of an artificial key is popular.
Simpler and even older technique is more applicable, technique that is
still in use: pen and paper. <<

Okay, I'll start telling people that they are 100 years behind instead
of only 50 years behind the technology!

But they do not always re-discover a "pen & paper" model. When they
write nested looping cursors, this is classic COBOL tape merges.

>> IDENTITY has nothing to do with a physical location. <<

So if I put the same row into a new database, I will aways get the same
IDENTITY value? If I put the same row with a real key into a table, I
will aways get the same IDENTITY value?

The IDENTITY column depends on the internal state of a counter in the
PHYSICAL hardware. It is HARDWARE DEPENDENT, just like a ROWID in other
products (but not as fast).

>> You seriously need to learn how modern RDBMS work, Celko. <<

Would you like to see how how many of the really "Modern RDBMS" products
to which I have consulted on implementation issues? While I was on the
X3H2 Committee, a lot of my consulting work was reading and explaining
how SQL was supposed to work to new RDBMS developers.

The Nucleus engine, which uses compressed bit vectors for the entire
schema was a client. WATCOM SQL (now part of Sybase) and their single
index structure that ties PK-FK together in one structure was a client
(you still have to index the FK side in SQL Server -- the tables are
seen as disjoint, not related inside a total schema). Looked at minor
stuff at Teradata, Etc. I have been all over the insides of the Modern
RDBMS.

Right now, SQL Server is still based on a file system that uses
contigous storage and is 20+ years old in its basic architecture. So is
DB2 and Oracle really stinks. If RDBMS developers did not understand
thinking in sets or viewing the schema as an integrated whole, instead
of disjoint tables (files), why would application developers understand
non-procedural algorithms?

>> And unlearn what you happen to know about magtapes. <<

No, I need to know when a magtape model of data processing is
appropriate. It works very nicely in ETL jobs for a data warehouse with
parallelism and scrubbing. I also need to remember it well enough to
know it when I see it and avoid it in an OLTP environment.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||--CELKO-- (remove.jcelko212@.earthlink.net) writes:
> That does not matter. The newbies are re-discovering tape file systems
> without every having worked on them. When you were taking computer
> science courses, how many times did you "invent" an algorithm that other
> people already knew?

Then probably that tells us something that this is a good model that is
easy to work with. If you want to convince people that it is not, you
should not talk about tapes, because they will not understand what you
are talking about.

An autonumber is a convenient way of numbering rows in the order they
are entered in a database. Sometimes, this is not very useful, sometimes
this is the only way to identify data. (Example: you load a file from
an external source. The file may be supposed to come with real keys, but
you cannot be sure that the file adheres to its supposed format. So the
only key when it comes to describe the file is the line number. (Then
after examining the data, you might be able to get the data into the
target table with some other keys.)

> So if I put the same row into a new database, I will aways get the same
> IDENTITY value? If I put the same row with a real key into a table, I
> will aways get the same IDENTITY value?

Yes, once assigned the value will not change. Do you really think that
if take copy of a database, that all identity values gets replaced? Would
be quite a useless feature.

> The IDENTITY column depends on the internal state of a counter in the
> PHYSICAL hardware. It is HARDWARE DEPENDENT, just like a ROWID in other
> products (but not as fast).

So, if the database is moved from one machine to another, and the last
inserted row before the move got an IDENTITY value of 987, the next
inserted after the move could get the value -234, 876 or 23445?

Nonsense. The IDENTITY value has nothing to do with the hardware, but
it does reflect in which order the rows where inserted. (Although, there
is now guarantee that this is he case.)

>>> You seriously need to learn how modern RDBMS work, Celko. <<
> Would you like to see how how many of the really "Modern RDBMS" products
> to which I have consulted on implementation issues?

I don't have to see the list. I just see how many inaccurate statements
you make about SQL Server, and that tells me much more that any lists
you may produce.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

easy table based update statement??

Hello,

I have 2 ways of updating data I'm using often

1) via a cursor on TABLE1 update fields in TABLE2
2) via an some of variables ...

SELECT @.var1=FLD1, @.var2=FLD2 FROM TABLE1 WHERE FLD-ID = @.inputVAR
UPDATE TABLE2
SET FLDx = @.var1, FLDy = @.var2
WHERE ...

Now I have a system with 2 databases and I need to update table DB2.TAB
based on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,
I thought it would be much easier to get the required data immediately from
DB1.TAB in the update-statement of DB2.TAB ... but the way to do that
confuses me. I've checked books online and a lot of newsgrouppostings
giving good information but still I keep getting errors like this ...

The column prefix 'x.ADS' does not match with a table name or alias name
used in the query.

while executing the following statement ...

UPDATE DB2.dbo.TAB
SET
FLD1 = x.FLD1,
FLD2 = x.FLD2,
...
FROM DB1.dbo.TAB x, DB2.dbo.ADS
WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
@.InputParameter

So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
table DB1.TAB
AND I only want to update the row in DB2.TAB with the unique keyfield
IDOFTAB2 equal to variable @.InputParameter

Do you see what I'm doing wrong?

--
Thank you,
Kind regards,
Perre Van Wilrijk,
Remove capitals to get my real email address,Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> The column prefix 'x.ADS' does not match with a table name or alias name
> used in the query.
> while executing the following statement ...
> UPDATE DB2.dbo.TAB
> SET
> FLD1 = x.FLD1,
> FLD2 = x.FLD2,
> ...
> FROM DB1.dbo.TAB x, DB2.dbo.ADS
> WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> @.InputParameter
> So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> table DB1.TAB
> AND I only want to update the row in DB2.TAB with the unique keyfield
> IDOFTAB2 equal to variable @.InputParameter

The string x.ADS is not in the part of the query you posted. Maybe you
should post the complete query?

But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
but not in the WHERE clause. That could cause some unexpectedly bad
performance, as you get a cartesian join.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply Mr Erland Sommarskog.

I changed the names of the fields and tables in order to eliminate dutch
databasenames and in order to avoid confusion about the tablename ADS which
appears in both databases, but which isn't an exact copy. DBIngeven is made
only to insert new rows. DBLezen is made only to read data earlier entered
in DBIngeven and other databases. So I'm sorry I made the statement unclear
by replacing tablename ADS by tablename TAB, forgetting to do it
everywhere.in the statement.

This is actually the statement I wrote to synchronize the data, the
statement causing the error "The column prefix 'x.ADS' does not match with a
table name or alias name used in the query.". Might the problem being
caused by the fact that both tables, being in different databases, have the
same name?

CREATE STORED PROCEDURE USP_SYNC
@.ADIDLezen int
AS
DECLARE @.ER int

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
@.ADIDLezen

SET @.ER = @.@.ERROR

Thanks,
Perre.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B3F1B592928Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > The column prefix 'x.ADS' does not match with a table name or alias name
> > used in the query.
> > while executing the following statement ...
> > UPDATE DB2.dbo.TAB
> > SET
> > FLD1 = x.FLD1,
> > FLD2 = x.FLD2,
> > ...
> > FROM DB1.dbo.TAB x, DB2.dbo.ADS
> > WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> > @.InputParameter
> > So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> > table DB1.TAB
> > AND I only want to update the row in DB2.TAB with the unique keyfield
> > IDOFTAB2 equal to variable @.InputParameter
> The string x.ADS is not in the part of the query you posted. Maybe you
> should post the complete query?
> But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
> but not in the WHERE clause. That could cause some unexpectedly bad
> performance, as you get a cartesian join.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 3 Dec 2004 14:30:43 +0100, Perre Van Wilrijk wrote:

> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
>@.ADIDLezen

Hi Perre,

Change x.ADS.ADID to x.ADID (or x.ADS_ADID - I can only guess here).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> I changed the names of the fields and tables in order to eliminate dutch
> databasenames and in order to avoid confusion about the tablename ADS
> which appears in both databases, but which isn't an exact copy.

Instead you caused confusion. (And Dutch is not a problem to understand,
at least as it's single. Double-dutch may be more difficult...)

> This is actually the statement I wrote to synchronize the data, the
> statement causing the error "The column prefix 'x.ADS' does not match
> with a table name or alias name used in the query.". Might the problem
> being caused by the fact that both tables, being in different databases,
> have the same name?

No, but because you the alias in the wrong place:

> FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
> @.ADIDLezen

x.ADS.ADID would refer to a table ADS owned by the user x.

A tip is to always use aliases. They usually make queries less verbose,
not the least when you use three-part names.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
Thanks Hugo,

Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
messages given back by SQL Server wasn't clear enough for me to locate the
synthax error I made. Great you saw it without knowing my table and field
names. Sorry to have bothered you with such a stupid mistake.

I also made an alias for DBLezen making the WHERE-clause better readable and
maintainable.
A little bit strange now I can't use the y alias in the SET clause (eg SET
y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
refers to the table name after the UPDATE-word, which doesn't seem to be
aliasable. So this on works fine ...

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B4AD8233CE9Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > I changed the names of the fields and tables in order to eliminate dutch
> > databasenames and in order to avoid confusion about the tablename ADS
> > which appears in both databases, but which isn't an exact copy.
> Instead you caused confusion. (And Dutch is not a problem to understand,
> at least as it's single. Double-dutch may be more difficult...)
> > This is actually the statement I wrote to synchronize the data, the
> > statement causing the error "The column prefix 'x.ADS' does not match
> > with a table name or alias name used in the query.". Might the problem
> > being caused by the fact that both tables, being in different databases,
> > have the same name?
> No, but because you the alias in the wrong place:
> > FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> > WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID
=
> > @.ADIDLezen
> x.ADS.ADID would refer to a table ADS owned by the user x.
> A tip is to always use aliases. They usually make queries less verbose,
> not the least when you use three-part names.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 7 Dec 2004 13:55:55 +0100, Perre Van Wilrijk wrote:

(snip)
>I also made an alias for DBLezen making the WHERE-clause better readable and
>maintainable.
>A little bit strange now I can't use the y alias in the SET clause (eg SET
>y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
>refers to the table name after the UPDATE-word, which doesn't seem to be
>aliasable. So this on works fine ...
> UPDATE DBLezen.dbo.ADS
> SET
> ADS_HR = x.ADS_HR,
(snip)

This should work as well:

UPDATE y
SET
ADS_HR = x.ADS_HR,
(...)
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

Personally, I prefer to always use the UPDATE .. FROM syntax this way, so
I won't forget to use it if a self-join is included (than it becomes
mandatory to use the alias in the UPDATE clause).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
> messages given back by SQL Server wasn't clear enough for me to locate the
> synthax error I made.

It's kind of difficult for SQL Server to second-guess what you really meant.

But it is true that the error messages from SQL Server are not always
crystal clear. When it comes to true parsing errors, the obscureness of
the messages partly comes from the too rich syntax of T-SQL. A typo can
lead to some legal syntax SQL that you are not aware of, but then lead
a syntax error further ahead.

For the error message you got, I guess the main problem is that the
message points to the first line in the UPDATE statement, instead of
the line where the error is. That makes it more difficult to spot the
error.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> It's kind of difficult for SQL Server to second-guess what you really
meant.
> But it is true that the error messages from SQL Server are not always
> crystal clear. When it comes to true parsing errors, the obscureness
of
> the messages partly comes from the too rich syntax of T-SQL. A typo
can
> lead to some legal syntax SQL that you are not aware of, but then
lead
> a syntax error further ahead.

It might not be easy to parse especially my code :-)
I can imagine that.
I once believed I could give people using my programs always clear
messages, for each kind of problem. We all have to learn.

Easy SQL question

I have a simple SQL table. The data looks like this:

products_ID--category--product_local

1--CORE--0

2--BASE--0

3--BRANCH--1

4--LEAF--3

I need to create a SQL statement that produces all category items where that ROW's products_ID is not found in ANY product_local in the table. So in the table above only the category BASE and LEAF would be printed because their products_ID are not found in any product_local in the table.

TIA as I am completely stuck!Hello newcents,


SELECT *
FROM <tableName>
WHERE products_ID NOT IN
 ( SELECT DISTINCT product_local
 FROM <tableName> )

Enjoy,

Easy SQL query question

Hi,
I have a table (PartNumberTbl) includes all the part number.
I want to add a part number 'ALL' in the following output so that user
can select "ALL" in the DropDownList control:
SELECT DISTINCT (PartNumber) FROM PartNumberTbl WHERE Customer =
@.Customer
I don't want to create a Part Number "ALL" for each customer.
How to do it without create a temp table?
Or is there a better way to handle this?
Thanks,
BenConsider adding it to the client code so that the general query without part
number 'ALL' can be reused by several other applications. If all
applications require this entry as one of the rows, then consider using the
UNION operator.
Anith|||You can handle this at the client side of your application,
or, you can issue the following SELECT statement.
(Suppose that PartNumber is a char column.)
SELECT PartNumber
FROM PartNumberTbl
WHERE Customer = @.Customer
UNION
SELECT 'ALL' as PartNumber
Martin C K Poon
Senior Analyst Programmer
====================================
"Ben" <wubin_98@.yahoo.com> ?
news:1150146922.304849.52960@.c74g2000cwc.googlegroups.com ?...
> Hi,
> I have a table (PartNumberTbl) includes all the part number.
> I want to add a part number 'ALL' in the following output so that user
> can select "ALL" in the DropDownList control:
> SELECT DISTINCT (PartNumber) FROM PartNumberTbl WHERE Customer =
> @.Customer
> I don't want to create a Part Number "ALL" for each customer.
> How to do it without create a temp table?
> Or is there a better way to handle this?
> Thanks,
> Ben
>

Easy SQL Problem

I have a simple table that stores
name score
-- --
jim 343
bob 322
jane 122
I need a SQL query that will return the name and score of the person who has
the lowest score:
eg. jane 122
SELECT name, min(score) as score FROM tester group by name;
What is the best query for the job?
http://www.plus2net.com/sql_tutorial/sql_min.php
this tutorial seems to be wrong. It doesn't work when i run it.Mark Thomson wrote:
> I have a simple table that stores
> name score
> -- --
> jim 343
> bob 322
> jane 122
> I need a SQL query that will return the name and score of the person who h
as
> the lowest score:
> eg. jane 122
I would write something like this:
SELECT name, score FROM tester WHERE score = (SELECT MIN(score) FROM tester)|||Try,
select *
from tester
where score = (select min(score) from tester)
-- or
select top 1 with ties *
from tester
order by score desc
AMB
"Mark Thomson" wrote:

> I have a simple table that stores
> name score
> -- --
> jim 343
> bob 322
> jane 122
>
> I need a SQL query that will return the name and score of the person who h
as
> the lowest score:
> eg. jane 122
>
> SELECT name, min(score) as score FROM tester group by name;
> What is the best query for the job?
> http://www.plus2net.com/sql_tutorial/sql_min.php
> this tutorial seems to be wrong. It doesn't work when i run it.
>
>|||Many Thanks,
I've never heard of the ties keyword before.
Cheers.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BC89C4FC-E232-4DCA-85B8-FCA33AC303CC@.microsoft.com...
> Try,
> select *
> from tester
> where score = (select min(score) from tester)
> -- or
> select top 1 with ties *
> from tester
> order by score desc
>
> AMB
> "Mark Thomson" wrote:
>|||select top 1 name, score from tableName order by score
If course, if 2 people have the same score, this will return only one of
them.
"Mark Thomson" <@.@.@.> wrote in message
news:OacoT6sjFHA.476@.TK2MSFTNGP14.phx.gbl...
>I have a simple table that stores
> name score
> -- --
> jim 343
> bob 322
> jane 122
>
> I need a SQL query that will return the name and score of the person who
> has the lowest score:
> eg. jane 122
>
> SELECT name, min(score) as score FROM tester group by name;
> What is the best query for the job?
> http://www.plus2net.com/sql_tutorial/sql_min.php
> this tutorial seems to be wrong. It doesn't work when i run it.
>|||...and for that you to work as you want, remove 'desc'.
"Mark Thomson" <@.@.@.> wrote in message
news:OUd1QFtjFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Many Thanks,
> I've never heard of the ties keyword before.
> Cheers.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:BC89C4FC-E232-4DCA-85B8-FCA33AC303CC@.microsoft.com...
>|||You are right.
AMB
"Raymond D'Anjou" wrote:

> ...and for that you to work as you want, remove 'desc'.
> "Mark Thomson" <@.@.@.> wrote in message
> news:OUd1QFtjFHA.1204@.TK2MSFTNGP12.phx.gbl...
>
>

Monday, March 26, 2012

Easy query question

Hello all,

I have a table representing a tree structure with three fields, let's call them this for now:

ID, Name, ParentID

ID is an autonumber. ParentID points back to an ID.

I need to find all those records that are not parents of other records. That is, I need to SELECT * FROM myTable WHERE (my ID is not in the ParentID column for any record.)

I just need to translate that last bit ("my ID is not in the ParentID column for any record") into SQL.

Any help would be greatly appreciated!!

Thanks,
FarazSELECT * from tbl t1 left outer join tbl t2 on t1.ID = t2.ParentID where t2.ParentID is null|||Not to be to fancy using having clause,

SELECT * FROM myTable WHERE ID not in (select ParentID from mytable)|||SELECT c.*
FROM myTable c
LEFT JOIN myTable p
ONc.ID = p.ParentId
WHERE p.ParentId IS NULL

OR

SELECT c.*
FROM myTable c
WHERE NOT EXISTS
( SELECT * FROM myTable p
WHERE c.ID = p.ParentId)

OR

SELECT *
FROM myTable
WHERE NOT IN
( SELECT ParentId FROM myTable)

Your Choice|||Thanks! I knew this was easy, and I know I've done it before, but somehow kept getting either too many or too few results.

Out of all your generous replies, I selected:

"SELECT * FROM myTable WHERE ID NOT IN (select ParentID from MyTable)"|||Your choice to do it right or wrong.

Use the JOIN method like rdjabarov or Brett's first example. SQL Server may translate the other two examples into a JOIN before executing it, but if it does not then your resulting query plan is less efficient.

blindman|||Originally posted by blindman
Your choice to do it right or wrong.

Use the JOIN method like rdjabarov or Brett's first example. SQL Server may translate the other two examples into a JOIN before executing it, but if it does not then your resulting query plan is less efficient.

blindman

it depends.....

Change the IN one to this

SELECT *
FROM myTable
WHERE ID NOT IN
( SELECT DISTINCT ParentId FROM myTable)

If there is a low cardinality, this might be most effecient...

You really need to a show plan to see which is most effecient (Blindman's right though, the Join usually wins)|||Okay, if you insist...

I'm now using the Joins... everything works swell.

Thanks everyone.|||Originally posted by Brett Kaiser
You really need to a show plan to see which is most effecient

Well actually I'd rather "insist" you do a SHOWPLAN on All three to see which one performs the best for you.

This is normal SQL development...|||If you use "not in ("... Then make sure to either "set ansi_nulls off" or add "where parentid is not null" - otherwise, no records will be returned if there is a parentid which is null. For example:

SELECT *
FROM myTable
WHERE ID NOT IN
( SELECT DISTINCT ParentId FROM myTable where parentid is not null)

Easy query question

This one should be easy, but for some reason I can't get it.
I have a table with an XML column, containing the following:
<xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
<xs:Caption />
<xs:Description />
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1" />
<xs:Rateplan Caption="TN1|H" Description="" />
<xs:Roomtype Caption="A1A" Description="" />
<xs:Mealplan Caption="None" Description="" />
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9" />
<xs:Target Sell="107.9" Net="107.9" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5" />
<xs:Target Sell="124.5" Net="124.5" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8" />
<xs:Target Sell="132.8" Net="132.8" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1" />
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87" />
<xs:Target Sell="32.87" Net="32.87" />
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z"
PickUp="0001-01-01T00:00:00Z" DropOff="0001-01-02T00:00:00Z"
Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z" Nights="1"
Days="2" Weeks="1" />
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description />
<xs:UnitRate>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07" />
<xs:Target Sell="398.07" Net="398.07" />
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0" />
<xs:PickUpFee />
<xs:DropOffFee />
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None" />
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:Costs>
</xs:ExtraLimit>
<xs:Information />
</xs:DistanceLimit>
</xs:ReservationChargeItem>
What I want is a select statement that gives me the values for net and sell
from the TotalSum/Source. Seems like it should be simple, but I'm missing
something somewhere. Thanks for any help you can give me.
Hello PK,
How's this:
use scratch
go
declare @.x xml
set @.x = '<?xml version="1.0" encoding="UTF-8"?>
<xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
<xs:Caption/>
<xs:Description/>
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1"/>
<xs:Rateplan Caption="TN1|H" Description=""/>
<xs:Roomtype Caption="A1A" Description=""/>
<xs:Mealplan Caption="None" Description=""/>
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9"/>
<xs:Target Sell="107.9" Net="107.9"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5"/>
<xs:Target Sell="124.5" Net="124.5"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8"/>
<xs:Target Sell="132.8" Net="132.8"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1"/>
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87"/>
<xs:Target Sell="32.87" Net="32.87"/>
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" PickUp="0001-01-01T00:00:00Z"
DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z"
Nights="1" Days="2" Weeks="1"/>
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description/>
<xs:UnitRate>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07"/>
<xs:Target Sell="398.07" Net="398.07"/>
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0"/>
<xs:PickUpFee/>
<xs:DropOffFee/>
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None"/>
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:Costs>
</xs:ExtraLimit>
<xs:Information/>
</xs:DistanceLimit>
</xs:ReservationChargeItem>
'
selectt.c.value('data(@.Sell)','money') as Sell
, t.c.value('data(@.Net)','money') as Net
from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSchema";
//xs:TotalSum/xs:Source') as t(c);
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Fantastic! Thanks!
"Kent Tegels" wrote:

> Hello PK,
> How's this:
> use scratch
> go
> declare @.x xml
> set @.x = '<?xml version="1.0" encoding="UTF-8"?>
> <xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
> DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
> <xs:Caption/>
> <xs:Description/>
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" Weeks="1"/>
> <xs:Rateplan Caption="TN1|H" Description=""/>
> <xs:Roomtype Caption="A1A" Description=""/>
> <xs:Mealplan Caption="None" Description=""/>
> <xs:GuestIds>
> <xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
> <xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
> </xs:GuestIds>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="107.9" Net="107.9"/>
> <xs:Target Sell="107.9" Net="107.9"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="124.5" Net="124.5"/>
> <xs:Target Sell="124.5" Net="124.5"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="132.8" Net="132.8"/>
> <xs:Target Sell="132.8" Net="132.8"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
> ApplicableForCancellation="True">
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" Weeks="1"/>
> <xs:Caption>32.87</xs:Caption>
> <xs:TotalSum>
> <xs:Source Sell="32.87" Net="32.87"/>
> <xs:Target Sell="32.87" Net="32.87"/>
> </xs:TotalSum>
> </xs:Taxes>
> <xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
> TN1" NumberOfUnits="0">
> <xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" PickUp="0001-01-01T00:00:00Z"
> DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z"
> Nights="1" Days="2" Weeks="1"/>
> <xs:Caption>A1A - TN1</xs:Caption>
> <xs:Description/>
> <xs:UnitRate>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:UnitRate>
> </xs:Products>
> <xs:TotalSum>
> <xs:Source Sell="398.07" Net="398.07"/>
> <xs:Target Sell="398.07" Net="398.07"/>
> </xs:TotalSum>
> <xs:MultipleDriversFee ChargeType="0"/>
> <xs:PickUpFee/>
> <xs:DropOffFee/>
> <xs:DistanceLimit DistanceUnit="0">
> <xs:FreeLimit Type="None"/>
> <xs:ExtraLimit Type="PerUnit" Distance="0">
> <xs:Costs>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:Costs>
> </xs:ExtraLimit>
> <xs:Information/>
> </xs:DistanceLimit>
> </xs:ReservationChargeItem>
> '
> selectt.c.value('data(@.Sell)','money') as Sell
> , t.c.value('data(@.Net)','money') as Net
> from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSchema";
> //xs:TotalSum/xs:Source') as t(c);
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>

Easy query question

s="1" />
<xs:Rateplan Caption="TN1|H" Description="" />
<xs:Roomtype Caption="A1A" Description="" />
<xs:Mealplan Caption="None" Description="" />
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9" />
<xs:Target Sell="107.9" Net="107.9" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5" />
<xs:Target Sell="124.5" Net="124.5" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8" />
<xs:Target Sell="132.8" Net="132.8" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" W

s="1" />
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87" />
<xs:Target Sell="32.87" Net="32.87" />
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z"
PickUp="0001-01-01T00:00:00Z" DropOff="0001-01-02T00:00:00Z"
Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z" Nights="1"
Days="2" W

s="1" />
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description />
<xs:UnitRate>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07" />
<xs:Target Sell="398.07" Net="398.07" />
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0" />
<xs:PickUpFee />
<xs:DropOffFee />
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None" />
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:Costs>
</xs:ExtraLimit>
<xs:Information />
</xs:DistanceLimit>
</xs:ReservationChargeItem>
What I want is a select statement that gives me the values for net and sell
from the TotalSum/Source. Seems like it should be simple, but I'm missing
something somewhere. Thanks for any help you can give me.Hello PK,
How's this:
use scratch
go
declare @.x xml
set @.x = '<?xml version="1.0" encoding="UTF-8"?>
<xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
<xs:Caption/>
<xs:Description/>
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" W

s="1"/>
<xs:Rateplan Caption="TN1|H" Description=""/>
<xs:Roomtype Caption="A1A" Description=""/>
<xs:Mealplan Caption="None" Description=""/>
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9"/>
<xs:Target Sell="107.9" Net="107.9"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5"/>
<xs:Target Sell="124.5" Net="124.5"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8"/>
<xs:Target Sell="132.8" Net="132.8"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" W

s="1"/>
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87"/>
<xs:Target Sell="32.87" Net="32.87"/>
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" PickUp="
0001-01-01T00:00:00Z"
DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="000
1-01-02T00:00:00Z"
Nights="1" Days="2" W

s="1"/>
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description/>
<xs:UnitRate>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07"/>
<xs:Target Sell="398.07" Net="398.07"/>
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0"/>
<xs:PickUpFee/>
<xs:DropOffFee/>
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None"/>
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:Costs>
</xs:ExtraLimit>
<xs:Information/>
</xs:DistanceLimit>
</xs:ReservationChargeItem>
'
select t.c.value('data(@.Sell)','money') as Sell
, t.c.value('data(@.Net)','money') as Net
from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSche
ma";
//xs:TotalSum/xs:Source') as t(c);
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Fantastic! Thanks!
"Kent Tegels" wrote:

> Hello PK,
> How's this:
> use scratch
> go
> declare @.x xml
> set @.x = '<?xml version="1.0" encoding="UTF-8"?>
> <xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema
"
> DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
> <xs:Caption/>
> <xs:Description/>
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" W

s="1"/>
> <xs:Rateplan Caption="TN1|H" Description=""/>
> <xs:Roomtype Caption="A1A" Description=""/>
> <xs:Mealplan Caption="None" Description=""/>
> <xs:GuestIds>
> <xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
> <xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
> </xs:GuestIds>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="107.9" Net="107.9"/>
> <xs:Target Sell="107.9" Net="107.9"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="124.5" Net="124.5"/>
> <xs:Target Sell="124.5" Net="124.5"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="132.8" Net="132.8"/>
> <xs:Target Sell="132.8" Net="132.8"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
> ApplicableForCancellation="True">
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" W

s="1"/>
> <xs:Caption>32.87</xs:Caption>
> <xs:TotalSum>
> <xs:Source Sell="32.87" Net="32.87"/>
> <xs:Target Sell="32.87" Net="32.87"/>
> </xs:TotalSum>
> </xs:Taxes>
> <xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
> TN1" NumberOfUnits="0">
> <xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" Pick
Up="0001-01-01T00:00:00Z"
> DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="0
001-01-02T00:00:00Z"
> Nights="1" Days="2" W

s="1"/>
> <xs:Caption>A1A - TN1</xs:Caption>
> <xs:Description/>
> <xs:UnitRate>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:UnitRate>
> </xs:Products>
> <xs:TotalSum>
> <xs:Source Sell="398.07" Net="398.07"/>
> <xs:Target Sell="398.07" Net="398.07"/>
> </xs:TotalSum>
> <xs:MultipleDriversFee ChargeType="0"/>
> <xs:PickUpFee/>
> <xs:DropOffFee/>
> <xs:DistanceLimit DistanceUnit="0">
> <xs:FreeLimit Type="None"/>
> <xs:ExtraLimit Type="PerUnit" Distance="0">
> <xs:Costs>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:Costs>
> </xs:ExtraLimit>
> <xs:Information/>
> </xs:DistanceLimit>
> </xs:ReservationChargeItem>
> '
> select t.c.value('data(@.Sell)','money') as Sell
> , t.c.value('data(@.Net)','money') as Net
> from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSc
hema";
> //xs:TotalSum/xs:Source') as t(c);
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>

Easy query help

I want to perform the following query but i dont know how :

select count(num) from X

and I want the X to be a table from the following query :

select table from bla bla bla . . .

This cannot be done by :select count(num) from (select table from bla bla bla) !

How can i build it ? I have stuck !which database is this? because the method you described should work

what was the actual error message you got?

perhaps you could also show real table and column names so we could see if there is an obvious syntax error|||I get a incorrect synbtax :

The query is
select count(distinct timestamp) from (select cf.table from general_cfg cf, can c where cf.x1 = c.y1);

This should have worked ?|||no, it shouldn't've

the subquery produces an intermediate table result which is used as the data source for the outer query's FROM clause, yes?

well, that intermediate table result has a single column, called "table"

thus, the outer table cannot count the distinct values of a column called "timestamp" because that column doesn't exist|||Are we trying to sneak up on dynamic SQL here?

-PatP|||And is there a solution to this problem at last ...???|||yes there is!!!!!!!!!!!!|||select count(num) from (select table from bla bla bla)
Add an alias name for the table:select count(num) from (select num from table from bla bla bla) AS tmp

easy query for you...

hi can you help me? i have a table like this:
agent client year import
-- ---- -- -------
2 DF 2004 3,00000000000000E+001
2 DF 2004 7,02166900000000E+004
1 DF 2003 6,47549000000000E+003
2 DIEFFE 2004 7,14400000000000E+001
1 ISC 2004 1,50000000000000E+001
2 ISC 2003 4,00000000000000E+001

but i needed a table like:

agent client import2003 import2004

have you any idea? thanksThis is the never ending question.

Look up 'pivot table' in http://asktom.oracle.com

:rolleyes:

Easy Q.. Query without temp table

How do I rewrite this query so that I do not use a temp table?

SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1

INTO #TEMP1

FROM SITECONTROLDATA

WHERE CALC_DATE > GETDATE() - 12

GROUP BY SITEID

SELECT * FROM #TEMP1 WHERE MINR1 = MAXR1

Use a derived table:

SELECT *

FROM (SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1

FROM SITECONTROLDATA

WHERE CALC_DATE > GETDATE() - 12

GROUP BY SITEID ) as TEMP1
WHERE MINR1 = MAXR1

|||Thanks!sql

Thursday, March 22, 2012

Easy Insert Statement ... Hopefully!

In Oracle I have an insert statement where Im selecting from one table and isnerting into another. One of the columns Im inserting is the current date/time

Ex.

Insert Into NEW_TABLE

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',SYSDATE,'1'

from REGION;

In SQL Server 2005 I cannot seem to find the right fit for the SYSDATE command: Please help

Insert Into NEW_TABLE

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',?,'1'

from REGION;

use GETDATE()

If you can use the same datetime for every row I would do this because it is much faster:

DECLARE @.curdate DATETIME
SET @.curdate = GETDATE()

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',@.curdate,'1'

from REGION;

|||AND not subject ot time variations.|||

Great!!! Exactly what I was looking for

Thanks

easy design tool

I have fairly simple table queries, and only a few dicey things, which I can handle in access. Is their a design tool for sql as easy and convenient as access?

Can someone suggest a good beginner's book on sql server 2005?

Use Access.

Explore how an Access Data Project works (it has a SQL Server backend).

Easy aggregation question

Given this table
CREATE TABLE SearchFieldDetail (ExpSetNo int, TableAlias char(10), Operator
char(2))
which has multiple rows for each ExpSetNo, I want to find each ExpSetNo that
has only one TableAlias among all of the same ExpSetNo rows and which has
'CC' as the Operator in all those rows.
I would only want to return ExpSetNo 1 from this data:
INSERT SearchFieldDetail
SELECT 1, 'COMCF', 'CC'
UNION
SELECT 1, 'COMCF', 'CC'
UNION
SELECT 2, 'COMCF', 'CC'
UNION
SELECT 2, 'COC', '='
UNION
SELECT 3, 'COC', 'CC'
UNION
SELECT 3, 'COMCF', 'CC'
And here's what I'm doing.
SELECT ExpSetNo
FROM SearchFieldDetail
GROUP BY ExpSetNo
HAVING COUNT(TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0 ELSE 1
END) = 0
DROP TABLE SearchFieldDetail
This works, but are there any suggestions for improvement?> I want to find each ExpSetNo that
> has only one TableAlias among all of the same ExpSetNo rows
In this case, you need to add DISTINCT to your COUNT function like the
example below. Also, I believe your sample data insert query needs to
specify UNION ALL rather than UNION.
SELECT ExpSetNo
FROM SearchFieldDetail
GROUP BY ExpSetNo
HAVING COUNT(DISTINCT TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0
ELSE 1
END) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:xqadnVdqk_RebmvcRVn-sg@.sti.net...
> Given this table
> CREATE TABLE SearchFieldDetail (ExpSetNo int, TableAlias char(10),
> Operator
> char(2))
> which has multiple rows for each ExpSetNo, I want to find each ExpSetNo
> that
> has only one TableAlias among all of the same ExpSetNo rows and which has
> 'CC' as the Operator in all those rows.
> I would only want to return ExpSetNo 1 from this data:
> INSERT SearchFieldDetail
> SELECT 1, 'COMCF', 'CC'
> UNION
> SELECT 1, 'COMCF', 'CC'
> UNION
> SELECT 2, 'COMCF', 'CC'
> UNION
> SELECT 2, 'COC', '='
> UNION
> SELECT 3, 'COC', 'CC'
> UNION
> SELECT 3, 'COMCF', 'CC'
> And here's what I'm doing.
> SELECT ExpSetNo
> FROM SearchFieldDetail
> GROUP BY ExpSetNo
> HAVING COUNT(TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0 ELSE 1
> END) = 0
> DROP TABLE SearchFieldDetail
> This works, but are there any suggestions for improvement?
>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eCvRsJ1AFHA.2104@.TK2MSFTNGP14.phx.gbl...
> In this case, you need to add DISTINCT to your COUNT function like the
> example below. Also, I believe your sample data insert query needs to
> specify UNION ALL rather than UNION.
> SELECT ExpSetNo
> FROM SearchFieldDetail
> GROUP BY ExpSetNo
> HAVING COUNT(DISTINCT TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN
0
> ELSE 1
> END) = 0
Excellent, Dan - thanks. A good example of bad test data (created without
UNION ALL) giving misleading results.

Thursday, March 29, 2012

Edit data with Query Analyzer

Easy way to remove international alphabet from all rows?

Easy way to index a column with URLs

Easy way to index a column with URLs

Easy way to index a column with URLs

Tuesday, March 27, 2012

easy trigger

Easy to Say but hard to implement

easy table based update statement??

Easy SQL question

Easy SQL query question

Easy SQL Problem

Monday, March 26, 2012

Easy query question

Easy query question

Easy query question

Easy query help

easy query for you...

Easy Q.. Query without temp table

Thursday, March 22, 2012

Easy Insert Statement ... Hopefully!

easy design tool

Easy aggregation question

Dynamic SQL Command

Blog Archive

About Me