Basic Query: Alternatives to Group By for nText column

I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.

So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:

http://64.225.154.232/temp_dbDiagram.gif

I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.

SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL,
topic.TOPIC_NAME, topic.TOPIC_URL
FROM tbl_CCF_Headlines headline INNER JOIN
tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN
tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID
WHERE (headline.HEADLINE_DATE IN
(SELECT TOP 3 HEADLINE_DATE
FROM tbl_CCF_HEADLINES
GROUP BY HEADLINE_DATE
ORDER BY HEADLINE_DATE DESC))
ORDER BY headline.HEADLINE_DATE DESCTry to cast youe text column to varchar(1000).|||What if the nText-field contains more than 1000 characters? What if it contains more than 4000 characters which I believe is the limit for nVarChar? I have this very same situation and am yet to find a solution...

-Tuukka

basic query question

Hello,
I was wondering how best to write a valid query that
Assimilates the following query.
SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0
GROUP BY id;
basically it's a query that returns a recordset with
unique values for one of the alias fields
any suggestions greatly appreciatedYou can't use the column alias in the where clause, but if I understand your
query correctly, you don't need it.
SQL Server will only returns rows for id's which actually occur, so their
count will always be greater than 0.
Just use hte query you have without the WHERE clause.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"aylwin" <aylwinagena@.hotmail.com> wrote in message
news:00ed01c3939d$db056be0$a001280a@.phx.gbl...
> Hello,
> I was wondering how best to write a valid query that
> Assimilates the following query.
> SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0
> GROUP BY id;
> basically it's a query that returns a recordset with
> unique values for one of the alias fields
> any suggestions greatly appreciated|||Hi thank you,
My actual query is different. my example is simplified.
My actual query is more like:
SELECT z.application_id as AppID, queued_by_user, queued_date,
'-1' AS NoInQueue, ProcessType, Reprint_Flag, First_Name
FROM l_DraftPrintingQueue z, l_customer a, d_customer_detail b
WHERE suspendprinting = 0
and
a.customer_id = b.customer_id
and
b.application_id = z.application_id
--Group By AppID <<problem>>
ORDER BY first_name ASC
the query returns many AppID's. I was trying to use a Group By but there
seems to be a problem with the where clause.
any ideas' thanks again!!!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||See my replies in the other group you posted to. Please don't multipost.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"aylwin agena" <aylwinagena@.hotmail.com> wrote in message
news:%23PWmhh6kDHA.2964@.tk2msftngp13.phx.gbl...
> Hi thank you,
> My actual query is different. my example is simplified.
> My actual query is more like:
> SELECT z.application_id as AppID, queued_by_user, queued_date,
> '-1' AS NoInQueue, ProcessType, Reprint_Flag, First_Name
> FROM l_DraftPrintingQueue z, l_customer a, d_customer_detail b
> WHERE suspendprinting = 0
> and
> a.customer_id = b.customer_id
> and
> b.application_id = z.application_id
> --Group By AppID <<problem>>
> ORDER BY first_name ASC
> the query returns many AppID's. I was trying to use a Group By but there
> seems to be a problem with the where clause.
> any ideas' thanks again!!!
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||select id, count(*) as cnt from table_name
group by id having count(*) > 0|||The HAVING clause is unnecessary here as you won't ever ever count an id
value if it doesn't appear in the data, so the counts will always be
positive.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"bill" <anonymous@.discussions.microsoft.com> wrote in message
news:2DBC44C3-5418-4B4C-8B4F-CD18F62A9761@.microsoft.com...
> select id, count(*) as cnt from table_name
> group by id having count(*) > 0

Basic query question

Hi,
I have a basic query question and hope that someone can clarify for me.
2 queries...
#1
\\\
SELECT *
FROM
Table1 AS A
INNER JOIN Table2 AS B ON
B.Column1 = A.Column1 AND
B.Column2 = A.Column2
///
#2
\\\
SELECT *
FROM
Table1 AS A, Table2 AS B
WHERE
B.Column1 = A.Column1 AND
B.Column2 = A.Column2
///
I suppose I will get the same result among these 2 queries, I just wonder is
there any performance different between these 2 queries? Any opinion which
one is better?
Thanks in advance.it will really give you the same result
but as a matter of standard
i suggest you use the first one.
performance wise i dont know.
its just that the first one is my preferred
choice
<hr>
MCP #2324787
"Kent" wrote:

> Hi,
> I have a basic query question and hope that someone can clarify for me.
> 2 queries...
> #1
> \\\
> SELECT *
> FROM
> Table1 AS A
> INNER JOIN Table2 AS B ON
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> #2
> \\\
> SELECT *
> FROM
> Table1 AS A, Table2 AS B
> WHERE
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> I suppose I will get the same result among these 2 queries, I just wonder
is
> there any performance different between these 2 queries? Any opinion whic
h
> one is better?
> Thanks in advance.|||AFAIK, there will be no performance difference between these two
particular queries; the optimizer should recognize them as being the
same and generate an identical execution plan for them (something which
you can easily test in Query Analyzer).
I prefer the JOIN syntax because it
a) is easier to read (to me), and
b) allows you to perform more complex joins. You can easily join three
tables with an INNER JOIN between two of those tables, and an OUTER
JOIN with the last table.
HTH,
Stu|||Kent,
This issue is a old issue and I think that our gurus here could answer you
with guarantee. From my own point of view it's the same although I prefer th
e
first one.
Regards,
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> it will really give you the same result
> but as a matter of standard
> i suggest you use the first one.
> performance wise i dont know.
> its just that the first one is my preferred
> choice
> --
>
> <hr>
> MCP #2324787
>
> "Kent" wrote:
>|||Kent
No, should not be any differences bettwen two queries. See an execution
plan to make sure.
"Kent" <Kent@.discussions.microsoft.com> wrote in message
news:EBE53BFA-BCF0-437A-847C-548B447FBDD5@.microsoft.com...
> Hi,
> I have a basic query question and hope that someone can clarify for me.
> 2 queries...
> #1
> \\\
> SELECT *
> FROM
> Table1 AS A
> INNER JOIN Table2 AS B ON
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> #2
> \\\
> SELECT *
> FROM
> Table1 AS A, Table2 AS B
> WHERE
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> I suppose I will get the same result among these 2 queries, I just wonder
> is
> there any performance different between these 2 queries? Any opinion
> which
> one is better?
> Thanks in advance.|||The optimizer will recognize these doing the same thing and optimize them th
e same way. The first is
preferred and the more modern syntax.
Outer joins is another story, though... In short, don't use the old ( *= ) s
yntax.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kent" <Kent@.discussions.microsoft.com> wrote in message
news:EBE53BFA-BCF0-437A-847C-548B447FBDD5@.microsoft.com...
> Hi,
> I have a basic query question and hope that someone can clarify for me.
> 2 queries...
> #1
> \\\
> SELECT *
> FROM
> Table1 AS A
> INNER JOIN Table2 AS B ON
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> #2
> \\\
> SELECT *
> FROM
> Table1 AS A, Table2 AS B
> WHERE
> B.Column1 = A.Column1 AND
> B.Column2 = A.Column2
> ///
> I suppose I will get the same result among these 2 queries, I just wonder
is
> there any performance different between these 2 queries? Any opinion whic
h
> one is better?
> Thanks in advance.|||Ya, they are the same in Execution Plan.
I think the first one is more neat when I get to join more tables.
Thanks all for help. :)
"Tibor Karaszi" wrote:

> The optimizer will recognize these doing the same thing and optimize them
the same way. The first is
> preferred and the more modern syntax.
> Outer joins is another story, though... In short, don't use the old ( *= )
syntax.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kent" <Kent@.discussions.microsoft.com> wrote in message
> news:EBE53BFA-BCF0-437A-847C-548B447FBDD5@.microsoft.com...
>

Basic Query

Hi,
I want to know, is it possible to have MQSQL Database Server on UNIX. is it supported in UNIX platforms.
this is just a general query.
Thanx for the help ... GiriNo, that would be Anti-Microsoft.

Basic Full Text search question

Hi

I am just starting to do a full text search on a sql 2000 database. My question is:

If I want to query a table Employee for 1. a particular word in a very large varchar field named "resume" 2 - a boolean field named "willtransfer" and 3. a field that holds the index of another table named "Cities" Cities just holds an index and cityname field of 15 characters.

do I create a fulltext index for all three fields or only the "resume" field since it is the only field that is being searched? What would a query for these 3 fields look like if I am searching for. I have seen no examples that combine multiple fields.

"speaks French" in resume, "true" for "willtransfer " field, and "Miami" in the cities table (there would need to be an inner join on the cityid field in both Employee and City table.

Thanks for any help on this. This is not the tables I am using, but this seemed like an easy example to explain.

smhaig

SELECT e.resume, e.willtransfer, c.cityname FROM Employee e
JOIN City c ON e.cityid = c.cityid
WHERE e.resume LIKE '%FRENCH%'
AND e.willtransfer = True
AND c.CityName = 'MIAMI'

I think maybe you might be looking for something like:

DECLARE @.mystring varchar(50)
SET @.mystring = 'french transfer=true Miami'

SELECT e.resume, e.willtransfer, c.cityname FROM Employee e
JOIN City c ON e.cityid = c.cityid
WHERE @.MyString LIKE '%FRENCH%'
AND @.MyString LIKE '%transfer=True%'
AND @.MyString@. = 'Miami'
AND e.resume LIKE '%FRENCH%'
AND e. willtransfer = 'True'
AND c.cityname = 'Miami'

I'm not sure if you want to concatenate all three fields or not.

Please see above

Adamus

|||

You only put a full text index on the 1 field you filter on the other columns using a standard where clause

i.e.

select employeeId

from employee

join city on employee.cityId = cities.cityId

where willtransfer = 1

and cityname = 'Miami'

and contains (employee.resume,'French')

This would match any resume that contains French you might want to include more complex search like "French NEAR speaks"

Basic "RANK" question: how to stop null records appearing.

i have a query that runs just fine, returning the turnover per sector, where the sector has non-null turnover.

I'd like to add ranking into the query, but when I do, I now get ALL sectors, with those sectors having null turnover all ranking equal bottom.

This kinda makes sense, but essentially I'd like to exclude the null turnover ones. I've tried various combinations of nonempty, NON EMPTY and Exists without success. What's the answer to this, and more importantly, how should I be thinking this through to get the right answer myself?

1. First query: i have defined the ranking measure, but not used it. I get only non empty sectors.

WITH

member measures.[turnover rank] as rank([SECTOR3].[SECTOR3 ID].CurrentMember, [SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS)

SELECT {[Measures].[TURNOVER]} on columns,

nonempty {[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS} ONROWS

FROM [WmCube4]

where [TIME].[Date].&[2007-06-04T00:00:00]

2. Second query: i include the ranking measure in the output - now null records appear.

WITH

member measures.[turnover rank] as rank([SECTOR3].[SECTOR3 ID].CurrentMember, [SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS)

SELECT {[Measures].[TURNOVER],measures.[turnover rank]} on columns,

nonempty {[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS} ONROWS

FROM [WmCube4]

where [TIME].[Date].&[2007-06-04T00:00:00]

Not sure how ranking could occur without using a 3rd parameter for Rank() - but you can return null sectors with empty turnover, like:

member measures.[turnover rank] as

iif(IsEmpty([Measures].[TURNOVER]), Null,

rank([SECTOR3].[SECTOR3 ID].CurrentMember,

[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS,

[Measures].[TURNOVER]))

|||

works perfectly, thanks!

Bank numbers check

I want to make a query that check in a table for bank numbers that are incorrect. This need to be done in sql and using modulo 97.

Thanks

Modulo operator in SQL server is % (which returns the remainder of an integer division).

So to get modulo 97 you do:

<somenumber> % 97

You might need to convert the datatype of your bank number from varchar to int or bigint to be able to perform this operation. If the bank number contains any non-number characters such as . or - then you must strip these out.

|||

Don't know if this is what you're after but I wrote this to validate ABA numbers:

public class CheckFedWireRoutingNumber
{

//Checks that the fed wire routing number (also known as an ABA number)
//is in the valid format. Does not verify that it exists, just that
//it's in the right format
static public bool IsABA(string abaNumber)
{
//Validate format of ABA number. This is taken fromhttp://www.brainjar.com/js/validation/
//andhttp://javascript.internet.com/forms/aba-routing-number-checksum.html
//andhttp://www.azcode.com/ABA/aba.htm
//function isABA(t)
//
//Here's a javascript version, taken from the first url, above
//{
// // Remove dashes if entered
// t = removeCharacters(t, '-');
//
// // All digits should be numeric (0 - 9)
// if (!isInteger(t))
// return false;
//
// // Run through each digit and calculate the total.
// n = 0;
// for (i = 0; i < t.length; i += 3)
// {
// n += parseInt(t.charAt(i), 10) * 3
// + parseInt(t.charAt(i + 1), 10) * 7
// + parseInt(t.charAt(i + 2), 10);
// }
// if (n != 0 && n % 10 == 0)
// return true;
// else
// return false;
//}

//remove dashes and spaces
abaNumber=abaNumber.Replace("-","").Replace(" ","");
if (!IsNumeric(abaNumber))
{
return false;
}

//Perform checksum
int n = 0;
try
{
for (int i = 0; i < abaNumber.Length; i += 3)
{
n += (Convert.ToInt32(abaNumber.Substring(i,1)) * 3) +
(Convert.ToInt32(abaNumber.Substring(i+1,1)) * 7) +
(Convert.ToInt32(abaNumber.Substring(i+2,1)));
}
}
catch (ArgumentOutOfRangeException)
{
//Caused by an ABA number that is not the right length
return false;
}
if (n != 0 && n % 10 == 0)
{
return true;
}
else
{
return false;
}
}

//Each character should be a valid number
static public bool IsNumeric(string input)
{
foreach(char c in input)
{
if(!char.IsNumber(c)) return false;
}
return true;
}

}

|||

Thanks dbland07666 but I need to do it in sql.

Johram how does this convert work? I now how to do that in vb.net code but not in sql. And another problem is that I only need to see the record where the bank number is incorrect. And I also don't may see the records where there is no bank number.

Thanks

|||

Could you give us an example of how a bank number looks like in your database? And what is the algorithm for verifying that it is valid? Is modulo 97 the only check you do? If so, what result do you expect in order to determine if it is valid? We need to know how your check is made in order to help you with the SQL code. Thanks!

|||

My bank numbers are only numbers.

BN = 72020290081: 97 - (modulo 97 van 720202900) = 97 - 16 =81

Thanks

Davidnyh

|||

Basically, what you need to do in your SQL is to split the number to get the "number part" and the "checksum part". Then you perform the modulo check and compare with the given checksum.

OK, so here's our first shot at it. Assuming table nameAccount and column nameNumber. This query will list all accounts with their calculated checksums, next to their given checksums.

SELECT Number,CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT)AS StrippedNumber,CAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT)AS GivenChecksum, 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97AS CalculatedChecksumFROM Account

Visually, we can now compare the calculated checksum with the given checksum and spot the deviations. But we need to do this in code as well.

If we only want the numbers that are valid, we can do something like:

SELECT NumberFROM AccountWHERECAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT) = 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97

Maybe you would like to list all accounts and have a column which says whether it is valid or not? Then we can do like this:

SELECT Number,'Yes'AS ValidFROM(SELECT Number,CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT)AS StrippedNumber,CAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT)AS GivenChecksum, 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97AS CalculatedChecksumFROM Account)AS T1WHERE GivenChecksum = CalculatedChecksumUNIONSELECT Number,'No'AS ValidFROM(SELECT Number,CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT)AS StrippedNumber,CAST(SUBSTRING(Number,LEN(Number) - 1, 2)AS INT)AS GivenChecksum, 97 -CAST(SUBSTRING(Number, 0,LEN(Number) - 1)AS INT) % 97AS CalculatedChecksumFROM Account)AS T2WHERE GivenChecksum != CalculatedChecksumOR GivenChecksumISNULL

Good luck with the numbers! If you have any more questions, just let us know!

Please don't forget to mark this post as answer if it was useful to you. Thanks!

|||

I get this error: Argument data type decimal is invalid for argument 1 of substring function.

And if I try CAST(Number AS INT) I get this error: Arithmetic overflow error converting expression to data type int.

|||It works.Thanks|||It works. Thanks

Bandwidth check?

Hi there.

a Quick question, and a stupid one may I add!

How can I check how much bandwidth is being used in a query to be executed, running SQL Server 2000 in bytes/KB?

I would like to know how to do this in both, 2000 and 2005 if possible. I just want to see if I am doing things correctly and effeciently, I believe I am, but also would like to make sure I am not over using my bandwidth from the web hosting point of view also.

Thanks!

Hi ahmedilyas,

Include Client Statistics. In QA or Managment Studio, elect to display the client statistics in the tsql editor, in Mgmt Studio (as I am looking at it right now) there is a section of the returned client stats labeled "network statistics", it includes the number of TDS packets sent across the wire etc...

Hope this helps,

Derek

sql

Baffled! Cant figure out how to do this query. Is it even possible?

I have an "Issues" table for my technicians. An issue can be on "hold"
or "assigned".
I want to get a count for each tech with a column showing number of
issues on hold and a column for number of issues assigned. It would
look like this --

Tech Num_Assigned Num_On_Hold
Fred 3 10
Carol 6 7

I can get each column separately, but I want both in the same answer
table!
Is that too much to ask? :)You need to do a self join on the table. Without your table
definition, it would be something like this

SELECT TI1.Tech,
Num_Assigned = COUNT(TI1.TechID),
Num_On_Hold = COUNT(TI2.TechID)
FROM TechIssues TI1, TechIssues TI2
WHERE TI1.TechID = TI2.TechID
GROUP BY TI1.Tech

Now, the above assumes that all techs have issues assigned AND issues
on HOLD. You'd need to UNION a couple more of these to handle where
Techs have records assigned but not on hold and vice versa. But this
should get you started.

Hope it helps
Teresa Masino|||jonescv@.gw.ccsd.net wrote:
> I have an "Issues" table for my technicians. An issue can be on "hold"
> or "assigned".
> I want to get a count for each tech with a column showing number of
> issues on hold and a column for number of issues assigned. It would
> look like this --
> Tech Num_Assigned Num_On_Hold
> Fred 3 10
> Carol 6 7
>
> I can get each column separately, but I want both in the same answer
> table!
> Is that too much to ask? :)

Here's a guess:

SELECT tech,
COUNT(CASE WHEN status = 'assigned' THEN 1 END),
COUNT(CASE WHEN status = 'hold' THEN 1 END)
FROM your_table
GROUP BY tech ;

--
David Portas
SQL Server MVP
--

Bad sql query and slow response

Can someone please confirm with me that this is not a good sql query. It
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David
|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in the
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:

> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
sql

Bad sql query and slow response

Can someone please confirm with me that this is not a good sql query. It
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in th
e
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:

> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes

Bad sql query and slow response

Can someone please confirm with me that this is not a good sql query. It
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
--
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in the
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes

Bad results from my MDX Adventure Works query

I have an Adventure Works MDX query that I want to return all the male employees and their total reseller-sales (including the people below them)

Select [Measures].[Reseller Sales-Sales Amount] on Columns,
non empty
Exists(
[Employee].[Employees].AllMembers
,[Employee].[Gender].&[M]
) on Rows
from [Analysis Services Tutorial]

which, when run, returns

Reseller Sales-Sales Amount
All Employees $80,450,596.98
Ken J. Sánchez $80,450,596.98
Brian S. Welcker $80,450,596.98
Amy E. Alberts $15,535,946.26
Ranjit R. Varkey Chudukatil $4,509,888.93
Stephen Y. Jiang $63,320,315.35
David R. Campbell $3,729,945.35
Garrett R. Vargas $3,609,447.22
Jos Edvaldo. Saraiva $5,926,418.36
Michael G. Blythe $9,293,903.01
Shu K. Ito $6,427,005.56
Stephen Y. Jiang $1,092,123.86
Tete A. Mensa-Annan $2,312,545.69
Tsvi Michael. Reiter $7,171,012.75
Syed E. Abbas $1,594,335.38
Syed E. Abbas $172,524.45

so there are three problems with this result, and I think they all have 1 solution. First, I don't want the 'All Employees' row. Second, there is a female in my results, seemingly because this female is the supervisor of some of the males. I asked for no females in my query. Third, Syed shows up twice, because he is a supervisor and a salesman himself. What I want are these results..

Ken J. Sánchez $80,450,596.98
Brian S. Welcker $80,450,596.98
Ranjit R. Varkey Chudukatil $4,509,888.93
Stephen Y. Jiang $63,320,315.35
David R. Campbell $3,729,945.35
Garrett R. Vargas $3,609,447.22
Jos Edvaldo. Saraiva $5,926,418.36
Michael G. Blythe $9,293,903.01
Shu K. Ito $6,427,005.56
Stephen Y. Jiang $1,092,123.86
Tete A. Mensa-Annan $2,312,545.69
Tsvi Michael. Reiter $7,171,012.75
Syed E. Abbas $1,594,335.38

Notice that there are no "All Employees", the woman is gone, and Syed is only a supervisor, and not an underling also. What MDX query would give me these results?

Thanks,

Todd Wilder

Referring to my response to your earlier post, this query seems to return the results you want - except for the order:

Select [Measures].[Reseller Sales Amount] on Columns,
non empty Generate(exists([Employee].[Employee].[Employee],
[Employee].[Gender].&[M]),
{LinkMember([Employee].[Employee].CurrentMember,
[Employee].[Employees])}) on Rows
from [Adventure Works]

|||Your queries dont seem to return anything on my cubes - your measure is named slightly different then mine and I don't have any tuples like [Employee].[Employee].[Employee]. Where did your cube come from?|||

Are you aware of the Adventure Works standard sample cube?

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

>>

SQL Server 2005 Books Online

Running Setup to Install AdventureWorks Sample Databases and Samples

Updated: 17 July 2006

The AdventureWorks (OLTP), AdventureWorksDW (data warehouse), and Adventure Works DW (analysis services) sample databases, as well as the companion samples, are not installed by default in SQL Server 2005. You can download these from SQL Server 2005 Samples and Sample Databases at the Microsoft Download Center, or you can use the following procedures to install the sample databases and samples during or after setup. Additional instructions for deploying the Adventure Works DW analysis services project are also provided.

...

>>

Bad Query?

I have a query like:

SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
"ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
"ContactTable1"."Name2" FROM "ContactTable","ProjectMembers","ContactTable"
"ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
("ContactTable"."RefContact" = "ContactTable1"."NDX")

This works as expected.
But I am concerned about this if the
"ContactTable"."RefContact" = "ContactTable1"."NDX"
is FALSE. (the RefContact is missing or broked)
The whole query fails.

Is there any way to prevent that the whole quey fails and only return NULL
fields?

Regards
GTiOn Fri, 7 Jan 2005 20:34:02 +0100, GTi wrote:

> I have a query like:
> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
> "ContactTable1"."Name2" FROM "ContactTable","ProjectMembers","ContactTable"
> "ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
> ("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
> ("ContactTable"."RefContact" = "ContactTable1"."NDX")
> This works as expected.
> But I am concerned about this if the
> "ContactTable"."RefContact" = "ContactTable1"."NDX"
> is FALSE. (the RefContact is missing or broked)
> The whole query fails.
> Is there any way to prevent that the whole quey fails and only return NULL
> fields?
> Regards
> GTi

You want LEFT JOIN. While you're at it, change the other table-joining
phrase into an INNER JOIN. It makes it easier to understand which
conditions join tables together and which conditions filter the rows.

SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
"ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
"ContactTable1"."Name2"
FROM "ProjectMembers"
INNER JOIN "ContactTable"
ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
LEFT JOIN "ContactTable1"
ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
WHERE ("ProjectMembers"."ProjectNDX"=4)|||On Fri, 7 Jan 2005 20:34:02 +0100, GTi wrote:

>I have a query like:
>SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>"ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
>"ContactTable1"."Name2" FROM "ContactTable","ProjectMembers","ContactTable"
>"ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
>("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
>("ContactTable"."RefContact" = "ContactTable1"."NDX")
>This works as expected.
>But I am concerned about this if the
>"ContactTable"."RefContact" = "ContactTable1"."NDX"
>is FALSE. (the RefContact is missing or broked)
>The whole query fails.
>Is there any way to prevent that the whole quey fails and only return NULL
>fields?

Hi GTi,

I don't really understand what you're trying to achieve. Please post
again, supplying
* Table structure (as CREATE TABLE statements, including all constraints
and properties but omitting irrelevant columns - see www.aspfaq.com/5006)
* Sample data that illustrates what you need to get done (posted as INSERT
statements)
* Expected output
* And a description of the business problem you're trying to solve.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Ross Presser" <rpresser@.imtek.com> skrev i melding
news:uw58xnrbmdjl$.dlg@.rpresser.invalid...
> On Fri, 7 Jan 2005 20:34:02 +0100, GTi wrote:
>> I have a query like:
>>
>> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
>> "ContactTable1"."Name2" FROM
>> "ContactTable","ProjectMembers","ContactTable"
>> "ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
>> ("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
>> ("ContactTable"."RefContact" = "ContactTable1"."NDX")
>>
>> This works as expected.
>> But I am concerned about this if the
>> "ContactTable"."RefContact" = "ContactTable1"."NDX"
>> is FALSE. (the RefContact is missing or broked)
>> The whole query fails.
>>
>> Is there any way to prevent that the whole quey fails and only return
>> NULL
>> fields?
>>
>> Regards
>> GTi
> You want LEFT JOIN. While you're at it, change the other table-joining
> phrase into an INNER JOIN. It makes it easier to understand which
> conditions join tables together and which conditions filter the rows.
> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
> "ContactTable1"."Name2"
> FROM "ProjectMembers"
> INNER JOIN "ContactTable"
> ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
> LEFT JOIN "ContactTable1"
> ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
> WHERE ("ProjectMembers"."ProjectNDX"=4)

At first I get this error:
ODBC Error: SQLSTATE = S1000, Native error code = 0
Unable to open table: ContactTable1.
No such table or object.

Then I changed it to:
SELECT ProjectMembers.RoleText, ProjectMembers.Description,
ContactTable.Name1, ContactTable.Name2, ContactTable1.Name1,
ContactTable1.Name2
FROM ProjectMembers, ContactTable, ContactTable1 as ContactTable
INNER JOIN ContactTable ON ProjectMembers.ContactNDX = ContactTable.NDX
LEFT JOIN ContactTable1 ON ContactTable.RefContact = ContactTable1.NDX
WHERE (ProjectMembers.ProjectNDX=4)

and get this error:
ODBC Error: SQLSTATE = 37000, Native error code = 0
Table reference [ContactTable] must be unique.

Now I'm a stuck newbie....

What I want...
I want to get a list of all contacts and ref. contacts to that contacts
(nested contacts) that
is connected to a project listed in the ProjectMembers table.
The original scripts works just fine, but I see a problem when a contact is
deleted
(and the ProjectMembers table is not updated) or/and when a contact don't
have
any refContact. Then the whole scrips fails.
If that happen I just want the xx.Name1, xx.Name2 fields left blank/NULL in
the script result
but returns the RoleText and Description from the ProjectMembers table.
Then "someone" can open the records and edit the correct contact.

OK?|||Do you mean that the table may be missing? or matching entries in the table?

If you mean entries in the table - a left join will work for ya.

if however the table is missing you will need to use dynamic sql to modify
your query,
or in a seperate step prior to your query make sure its there by creating it
if not.

"GTi" <bill@.gates.com> wrote in message news:3nFDd.668$VR2.22@.amstwist00...
> "Ross Presser" <rpresser@.imtek.com> skrev i melding
> news:uw58xnrbmdjl$.dlg@.rpresser.invalid...
>> On Fri, 7 Jan 2005 20:34:02 +0100, GTi wrote:
>>
>>> I have a query like:
>>>
>>> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>>> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
>>> "ContactTable1"."Name2" FROM
>>> "ContactTable","ProjectMembers","ContactTable"
>>> "ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
>>> ("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
>>> ("ContactTable"."RefContact" = "ContactTable1"."NDX")
>>>
>>> This works as expected.
>>> But I am concerned about this if the
>>> "ContactTable"."RefContact" = "ContactTable1"."NDX"
>>> is FALSE. (the RefContact is missing or broked)
>>> The whole query fails.
>>>
>>> Is there any way to prevent that the whole quey fails and only return
>>> NULL
>>> fields?
>>>
>>> Regards
>>> GTi
>>
>> You want LEFT JOIN. While you're at it, change the other table-joining
>> phrase into an INNER JOIN. It makes it easier to understand which
>> conditions join tables together and which conditions filter the rows.
>>
>> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
>> "ContactTable1"."Name2"
>> FROM "ProjectMembers"
>> INNER JOIN "ContactTable"
>> ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
>> LEFT JOIN "ContactTable1"
>> ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
>> WHERE ("ProjectMembers"."ProjectNDX"=4)
> At first I get this error:
> ODBC Error: SQLSTATE = S1000, Native error code = 0
> Unable to open table: ContactTable1.
> No such table or object.
> Then I changed it to:
> SELECT ProjectMembers.RoleText, ProjectMembers.Description,
> ContactTable.Name1, ContactTable.Name2, ContactTable1.Name1,
> ContactTable1.Name2
> FROM ProjectMembers, ContactTable, ContactTable1 as ContactTable
> INNER JOIN ContactTable ON ProjectMembers.ContactNDX = ContactTable.NDX
> LEFT JOIN ContactTable1 ON ContactTable.RefContact = ContactTable1.NDX
> WHERE (ProjectMembers.ProjectNDX=4)
> and get this error:
> ODBC Error: SQLSTATE = 37000, Native error code = 0
> Table reference [ContactTable] must be unique.
> Now I'm a stuck newbie....
>
> What I want...
> I want to get a list of all contacts and ref. contacts to that contacts
> (nested contacts) that
> is connected to a project listed in the ProjectMembers table.
> The original scripts works just fine, but I see a problem when a contact
> is deleted
> (and the ProjectMembers table is not updated) or/and when a contact don't
> have
> any refContact. Then the whole scrips fails.
> If that happen I just want the xx.Name1, xx.Name2 fields left blank/NULL
> in the script result
> but returns the RoleText and Description from the ProjectMembers table.
> Then "someone" can open the records and edit the correct contact.
> OK?
>|||One guy told me that top posting is perilous, so look at the bottom.

"David Rawheiser" <rawhide58@.hotmail.com> skrev i melding
news:KJQDd.82273$uM5.20030@.bgtnsc05-news.ops.worldnet.att.net...
> Do you mean that the table may be missing? or matching entries in the
> table?
> If you mean entries in the table - a left join will work for ya.
> if however the table is missing you will need to use dynamic sql to modify
> your query,
> or in a seperate step prior to your query make sure its there by creating
> it if not.
> "GTi" <bill@.gates.com> wrote in message
> news:3nFDd.668$VR2.22@.amstwist00...
>> "Ross Presser" <rpresser@.imtek.com> skrev i melding
>> news:uw58xnrbmdjl$.dlg@.rpresser.invalid...
>>> On Fri, 7 Jan 2005 20:34:02 +0100, GTi wrote:
>>>
>>>> I have a query like:
>>>>
>>>> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>>>> "ContactTable"."Name1", "ContactTable"."Name2",
>>>> "ContactTable1"."Name1",
>>>> "ContactTable1"."Name2" FROM
>>>> "ContactTable","ProjectMembers","ContactTable"
>>>> "ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
>>>> ("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
>>>> ("ContactTable"."RefContact" = "ContactTable1"."NDX")
>>>>
>>>> This works as expected.
>>>> But I am concerned about this if the
>>>> "ContactTable"."RefContact" = "ContactTable1"."NDX"
>>>> is FALSE. (the RefContact is missing or broked)
>>>> The whole query fails.
>>>>
>>>> Is there any way to prevent that the whole quey fails and only return
>>>> NULL
>>>> fields?
>>>>
>>>> Regards
>>>> GTi
>>>
>>> You want LEFT JOIN. While you're at it, change the other table-joining
>>> phrase into an INNER JOIN. It makes it easier to understand which
>>> conditions join tables together and which conditions filter the rows.
>>>
>>> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>>> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
>>> "ContactTable1"."Name2"
>>> FROM "ProjectMembers"
>>> INNER JOIN "ContactTable"
>>> ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
>>> LEFT JOIN "ContactTable1"
>>> ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
>>> WHERE ("ProjectMembers"."ProjectNDX"=4)
>>
>> At first I get this error:
>> ODBC Error: SQLSTATE = S1000, Native error code = 0
>> Unable to open table: ContactTable1.
>> No such table or object.
>>
>> Then I changed it to:
>> SELECT ProjectMembers.RoleText, ProjectMembers.Description,
>> ContactTable.Name1, ContactTable.Name2, ContactTable1.Name1,
>> ContactTable1.Name2
>> FROM ProjectMembers, ContactTable, ContactTable1 as ContactTable
>> INNER JOIN ContactTable ON ProjectMembers.ContactNDX = ContactTable.NDX
>> LEFT JOIN ContactTable1 ON ContactTable.RefContact = ContactTable1.NDX
>> WHERE (ProjectMembers.ProjectNDX=4)
>>
>> and get this error:
>> ODBC Error: SQLSTATE = 37000, Native error code = 0
>> Table reference [ContactTable] must be unique.
>>
>> Now I'm a stuck newbie....
>>
>>
>> What I want...
>> I want to get a list of all contacts and ref. contacts to that contacts
>> (nested contacts) that
>> is connected to a project listed in the ProjectMembers table.
>> The original scripts works just fine, but I see a problem when a contact
>> is deleted
>> (and the ProjectMembers table is not updated) or/and when a contact don't
>> have
>> any refContact. Then the whole scrips fails.
>> If that happen I just want the xx.Name1, xx.Name2 fields left blank/NULL
>> in the script result
>> but returns the RoleText and Description from the ProjectMembers table.
>> Then "someone" can open the records and edit the correct contact.
>>
>> OK?
>>
>>
>>
>>

I mean that if the matching entries in the table is missing (not the whole
table).
Example:
ContactTable.RefContact = ContactTable1.NDX

if ContactTable.RefContact IS 0 / NULL
or ContactTable.RefContact have a value to a non existing record (NDX).|||On Sat, 8 Jan 2005 00:58:59 +0100, GTi wrote:

>At first I get this error:
>ODBC Error: SQLSTATE = S1000, Native error code = 0
>Unable to open table: ContactTable1.
>No such table or object.
(snip)

Hi GTi,

Your original query was very hard to read, due to formatting, over-using
of double-quotes and omitting the AS keyword (I know it's optional, but
including it just makes your query so much better to read!)

Try modifying Ross' suggestion to:

SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
"ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
"ContactTable1"."Name2"
FROM "ProjectMembers"
INNER JOIN "ContactTable"
ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
LEFT JOIN "ContactTable" AS "ContactTable1"
ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
WHERE ("ProjectMembers"."ProjectNDX"=4)

Or, better yet:

SELECT ProjectMembers.RoleText, ProjectMembers.Description,
ContactTable.Name1, ContactTable.Name2,
ContactTable1.Name1, ContactTable1.Name2
FROM ProjectMembers
INNER JOIN ContactTable
ON ProjectMembers.ContactNDX = ContactTable.NDX
LEFT JOIN ContactTable AS ContactTable1
ON ContactTable.RefContact = ContactTable1.NDX
WHERE ProjectMembers.ProjectNDX = 4

Or (even better in my opinion, but some would disagree):

SELECT p.RoleText, p.Description,
c.Name1, c.Name2,
c1.Name1, c1.Name2
FROM ProjectMembers AS p
INNER JOIN ContactTable AS c
ON c.NDX = p.ContactNDX
LEFT JOIN ContactTable AS c1
ON c1.NDX = c.RefContact
WHERE p.ProjectNDX = 4

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> skrev i melding
news:5sj0u092kva6ste7jffrf6m63ojgva248n@.4ax.com...
> On Sat, 8 Jan 2005 00:58:59 +0100, GTi wrote:
>>At first I get this error:
>>ODBC Error: SQLSTATE = S1000, Native error code = 0
>>Unable to open table: ContactTable1.
>>No such table or object.
> (snip)
> Hi GTi,
> Your original query was very hard to read, due to formatting, over-using
> of double-quotes and omitting the AS keyword (I know it's optional, but
> including it just makes your query so much better to read!)
> Try modifying Ross' suggestion to:
> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
> "ContactTable1"."Name2"
> FROM "ProjectMembers"
> INNER JOIN "ContactTable"
> ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
> LEFT JOIN "ContactTable" AS "ContactTable1"
> ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
> WHERE ("ProjectMembers"."ProjectNDX"=4)
> Or, better yet:
> SELECT ProjectMembers.RoleText, ProjectMembers.Description,
> ContactTable.Name1, ContactTable.Name2,
> ContactTable1.Name1, ContactTable1.Name2
> FROM ProjectMembers
> INNER JOIN ContactTable
> ON ProjectMembers.ContactNDX = ContactTable.NDX
> LEFT JOIN ContactTable AS ContactTable1
> ON ContactTable.RefContact = ContactTable1.NDX
> WHERE ProjectMembers.ProjectNDX = 4
> Or (even better in my opinion, but some would disagree):
> SELECT p.RoleText, p.Description,
> c.Name1, c.Name2,
> c1.Name1, c1.Name2
> FROM ProjectMembers AS p
> INNER JOIN ContactTable AS c
> ON c.NDX = p.ContactNDX
> LEFT JOIN ContactTable AS c1
> ON c1.NDX = c.RefContact
> WHERE p.ProjectNDX = 4
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

I love the last one, It look nice and it is easier to read.
And it give me the result I wanted.
If I change the line
ON c1.NDX = c.RefContact
to
ON c1.NDX = 9999999 // or 0

Is still gives me the records but with no refContact.

Love it.
Thanks Hugo!|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> skrev i melding
news:5sj0u092kva6ste7jffrf6m63ojgva248n@.4ax.com...
> On Sat, 8 Jan 2005 00:58:59 +0100, GTi wrote:
>>At first I get this error:
>>ODBC Error: SQLSTATE = S1000, Native error code = 0
>>Unable to open table: ContactTable1.
>>No such table or object.
> (snip)
> Hi GTi,
> Your original query was very hard to read, due to formatting, over-using
> of double-quotes and omitting the AS keyword (I know it's optional, but
> including it just makes your query so much better to read!)
> Try modifying Ross' suggestion to:
> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
> "ContactTable1"."Name2"
> FROM "ProjectMembers"
> INNER JOIN "ContactTable"
> ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
> LEFT JOIN "ContactTable" AS "ContactTable1"
> ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
> WHERE ("ProjectMembers"."ProjectNDX"=4)
> Or, better yet:
> SELECT ProjectMembers.RoleText, ProjectMembers.Description,
> ContactTable.Name1, ContactTable.Name2,
> ContactTable1.Name1, ContactTable1.Name2
> FROM ProjectMembers
> INNER JOIN ContactTable
> ON ProjectMembers.ContactNDX = ContactTable.NDX
> LEFT JOIN ContactTable AS ContactTable1
> ON ContactTable.RefContact = ContactTable1.NDX
> WHERE ProjectMembers.ProjectNDX = 4
> Or (even better in my opinion, but some would disagree):
> SELECT p.RoleText, p.Description,
> c.Name1, c.Name2,
> c1.Name1, c1.Name2
> FROM ProjectMembers AS p
> INNER JOIN ContactTable AS c
> ON c.NDX = p.ContactNDX
> LEFT JOIN ContactTable AS c1
> ON c1.NDX = c.RefContact
> WHERE p.ProjectNDX = 4
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo,
I can improve it if I only use LEFT JOIN:
LEFT JOIN ContactTable AS c
LEFT JOIN ContactTable AS c1

LEFT JOIN:
"The LEFT JOIN returns all the rows from the first table , even if there are
*no matches* in the other tables"

INNER JOIN:
"The INNER JOIN returns all rows from both tables where there is a match. If
there are rows in ProjectMembers that do not have matches in ContactTable,
those rows will *not* be listed. "

http://www.w3schools.com/sql/sql_join.asp

If so, I think I got the understanding of the JOINING stuff...
Or?|||On Sat, 8 Jan 2005 23:32:47 +0100, GTi wrote:

(snip)
>Hugo,
>I can improve it if I only use LEFT JOIN:
>LEFT JOIN ContactTable AS c
>LEFT JOIN ContactTable AS c1

Hi GTi,

Whether that's an improvement or not depends on your requirements - if one
of the join types would always be better, the other wouldn't have been
implemented. :-)

The first question is: does your business allow ProjectMembers not to have
a ContactNDX (whatever that may be)? If the answer is no, then the second
question is: are you concerned that your table might have ProjectMembers
without ContactNDX? If the answer to this question is yes, then your table
design is failing to enforce a business rule. You should first take steps
to find and correct any rows with data in violation of the business rule,
then set a NOT NULL constraint on the ContactNDX column to make sure that
the business rule is enforced henceforth.

The first question is: does your business allow ProjectMembers to have a
ContactNDX that is not present in the ContactTable table? If the answer is
no, then the second question is: are you concerned that your table might
have ProjectMembers with a ContactNDX that's missing from the ContactTable
table? If the answer to this question is yes, then your table design is
failing to enforce a business rule. You should first take steps to find
and correct any rows with data in violation of the business rule, then set
a FOREIGN KEY constraint on the ContactNDX column to make sure that the
business rule is enforced henceforth.

You now have ensured that your business rules are properly enforced by
database constraints. Now, you can use these business rules to decide how
to write your query. Again, we start with a question: Will every row in
the ProjectMembers table always have a value for ContactNDX, AND will this
value always be present in the ContactTable table? If the answer is yes,
then there is no need to use a LEFT JOIN here. The results will not be any
different from those of an INNER JOIN, but the query will be harder to
process for SQL Server.
However, if the answer is that there can be rows in ProjectMembers with no
matching row in ContactTable (either because ContactMDX is NULL or because
it is not present in ContactTable), then the next question would be if
these rows need to be included in the results of your query. If they
should appear (with NULLS in the columns from ContactTable), then you need
a LEFT JOIN; if they should not appear, then you must use INNER JOIN.

</lecture
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> skrev i melding
news:ono0u09b83iuknh5voi6bk5pc8sa9sjhbe@.4ax.com...
> On Sat, 8 Jan 2005 23:32:47 +0100, GTi wrote:
> (snip)
>>
>>Hugo,
>>I can improve it if I only use LEFT JOIN:
>>LEFT JOIN ContactTable AS c
>>LEFT JOIN ContactTable AS c1
> Hi GTi,
> Whether that's an improvement or not depends on your requirements - if one
> of the join types would always be better, the other wouldn't have been
> implemented. :-)
> The first question is: does your business allow ProjectMembers not to have
> a ContactNDX (whatever that may be)? If the answer is no, then the second
> question is: are you concerned that your table might have ProjectMembers
> without ContactNDX? If the answer to this question is yes, then your table
> design is failing to enforce a business rule. You should first take steps
> to find and correct any rows with data in violation of the business rule,
> then set a NOT NULL constraint on the ContactNDX column to make sure that
> the business rule is enforced henceforth.
> The first question is: does your business allow ProjectMembers to have a
> ContactNDX that is not present in the ContactTable table? If the answer is
> no, then the second question is: are you concerned that your table might
> have ProjectMembers with a ContactNDX that's missing from the ContactTable
> table? If the answer to this question is yes, then your table design is
> failing to enforce a business rule. You should first take steps to find
> and correct any rows with data in violation of the business rule, then set
> a FOREIGN KEY constraint on the ContactNDX column to make sure that the
> business rule is enforced henceforth.
> You now have ensured that your business rules are properly enforced by
> database constraints. Now, you can use these business rules to decide how
> to write your query. Again, we start with a question: Will every row in
> the ProjectMembers table always have a value for ContactNDX, AND will this
> value always be present in the ContactTable table? If the answer is yes,
> then there is no need to use a LEFT JOIN here. The results will not be any
> different from those of an INNER JOIN, but the query will be harder to
> process for SQL Server.
> However, if the answer is that there can be rows in ProjectMembers with no
> matching row in ContactTable (either because ContactMDX is NULL or because
> it is not present in ContactTable), then the next question would be if
> these rows need to be included in the results of your query. If they
> should appear (with NULLS in the columns from ContactTable), then you need
> a LEFT JOIN; if they should not appear, then you must use INNER JOIN.
> </lecture>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Hi Hugo,

ProjectMembers.ContactNDX always have a pointer to Contact.NDX

FOREIGN KEY Definition:
"A foreign key is a field in a relational table that matches the primary key
column of another table. The foreign key can be used to cross-reference
tables."

I have to learn about what FOREIGN KEY is and what it actual does.
Do it prevent deleting a record in the ContactTable.NDX if it have a
reference in ProjectMembers.ContactNDX ?
If it does, how does it alarm the user.
Correction: How can my program (C++) handle it and give the end user a well
explained error message?
My test project will give the end user full access to the database,
creating, modifying and deleting tables and records as needed.
So the possibility for the end user to delete a contacts is present.
So how can my program handle it?
My thought is to handle it so flexible as possible, broken links is
displayed as broken (in my program).
I'm sure there is SQL functions for it already built in, but it will be
captured in my "learning curve"...

Best regards,
GTi|||GTi (bill@.gates.com) writes:
> FOREIGN KEY Definition:
> "A foreign key is a field in a relational table that matches the primary
> key column of another table. The foreign key can be used to
> cross-reference tables."
> I have to learn about what FOREIGN KEY is and what it actual does.
> Do it prevent deleting a record in the ContactTable.NDX if it have a
> reference in ProjectMembers.ContactNDX ?

Yes. Here is a simple example:

CREATE TABLE customers (
customerid int NOT NULL,
customername nvarchar(50) NOT NULL,
CONSTRAINT pk_customers PRIMARY KEY (customerid))
go
CREATE TABLE orders (
orderid int NOT NULL,
customerid int NOT NULL,
orderdate datetime NOT NULL,
CONSTRAINT pk_orders PRIMARY KEY (orderid),
CONSTRAINT fk_order_customer FOREIGN KEY (customerid)
REFERENCES customers(customerid))
go
INSERT customers (customerid, customername)
VALUES (1, 'Don Preston')
INSERT customers (customerid, customername)
VALUES (2, 'Jimmy Carl Black')
INSERT customers (customerid, customername)
VALUES (4, 'Bunk Gardner')
go
INSERT orders (orderid, customerid, orderdate)
VALUES (1, 1, '20041212') -- Runs fine
INSERT orders (orderid, customerid, orderdate)
VALUES (2, 3, '20050106') -- Fails, no customerid = 3
go
DELETE customers WHERE customerid = 4 -- Runs fine
DELETE customers WHERE customerid = 1 -- Fails, since there is an order
go
DROP TABLE orders
DROP TABLE customers

> If it does, how does it alarm the user.

If you run the above, you will see that the error message is fairly
generic.

> Correction: How can my program (C++) handle it and give the end user a
> well explained error message?

In the above example, your order registration form should only let
you enter customer in the database. Most business have to many customers
to fit all in a drop-down box, but you get the idea. Basically, the
user never the enters the customerid, or if he does, he first gets to
see all details about the customer, to see that he has the right guy.

For deletion, your app would have to check for existing orders when
the user presses the delete buttom. Or your GUI would be even slicker
and not enable the delete button if there are orders.

Thus, the error message from SQL Server should never hit the user in
the face; the constraint is there to protect the database against a
fauly application.

> My test project will give the end user full access to the database,
> creating, modifying and deleting tables and records as needed.
> So the possibility for the end user to delete a contacts is present.
> So how can my program handle it?

Many applications give the users a sheltered environment where they
don't see tables as such, even less can create any.

If you plan to give your users such flexibility, they will also get a
rougher environment, and it is inevitable that they will see error
messages from SQL Server, that not always are that comprehensible.
The particular message about constraint violation is very generic, and
assumes that the person who reads it have full knowledge of the data model.
Your program could trap the message and then try to interpret what it
means, but that's a quite complex task.

An alternative in this case, is not use DRI (Declarative Referential
Integrity) like above, but instead have all checks in triggers. This
requires more programming, is more prone to errors, takes more
resources, but permits for customised error messages like
"Cannot delete customer, there are orders".

--
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 Sun, 9 Jan 2005 00:57:47 +0100, GTi wrote:

>FOREIGN KEY Definition:
>"A foreign key is a field in a relational table that matches the primary key
>column of another table. The foreign key can be used to cross-reference
>tables."

Hi GTi,

May I ask you where you found this definition?

Personally, I'd be very wary of any text book that uses the term "field"
instead of "column" in descriptions of the relational model.
You'll find fileds on flat files and in some non-relational databases; in
a relational database, "column" is THE ONLY correct term. I usually don't
comment the mis-use of this terminology when people ask questions, but if
this is indeed a straight quote from a text-book, then it shouldn't go
uncommented!!

Even worse is that this definition seems to imply that a foreign key can't
span columns, but they definitely can:

CREATE TABLE Employees
(EmpID int NOT NULL,
EmpName varchar(50) NOT NULL,
-- more columns
PRIMARY KEY (EmpID)
)
go
CREATE TABLE Projects
(ProjID int NOT NULL,
ProjName varchar(25) NOT NULL,
-- more columns
PRIMARY KEY (ProjID)
)
go
CREATE TABLE Assignments
(EmpID int NOT NULL,
ProjID int NOT NULL,
AssStart smalldatetime NOT NULL,
AssEnd smalldatetime DEFAULT NULL,
PRIMARY KEY (EmpID, ProjID),
FOREIGN KEY (EmpID) REFERENCES Employees,
FOREIGN KEY (ProjID) REFERENCES Projects,
CHECK (AssEnd > AssStart)
)
go
CREATE TABLE TimeSheet
(EmpID int NOT NULL,
TSStart smalldatetime NOT NULL,
TSEnd smalldatetime NOT NULL,
TimeSpent AS CAST(DATEDIFF(minute, TSStart, TSEnd) / 60.0 AS
decimal (3,2)),
ProjID int NOT NULL,
PRIMARY KEY (EmpID, TSStart),
UNIQUE (EmpID, TSEnd),
-- The line below is the proof the foreign keys can span multiple columns
FOREIGN KEY (EmpID, ProjID) REFERENCES Assignments,
CHECK (TSEnd > TSStart),
CHECK (DATEDIFF(day, TSStart, TSEnd) = 0)
)
go

I realise that the above doesn't answer any of the questions you asked,
but I think that has alrteady been taken care of by Erland (thanks,
Erland!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> skrev i melding
news:iv63u0hil9rshu7oa1opovknephvaljj5k@.4ax.com...
> On Sun, 9 Jan 2005 00:57:47 +0100, GTi wrote:
>>FOREIGN KEY Definition:
>>"A foreign key is a field in a relational table that matches the primary
>>key
>>column of another table. The foreign key can be used to cross-reference
>>tables."
> Hi GTi,
> May I ask you where you found this definition?
> Personally, I'd be very wary of any text book that uses the term "field"
> instead of "column" in descriptions of the relational model.
> You'll find fileds on flat files and in some non-relational databases; in
> a relational database, "column" is THE ONLY correct term. I usually don't
> comment the mis-use of this terminology when people ask questions, but if
> this is indeed a straight quote from a text-book, then it shouldn't go
> uncommented!!
> Even worse is that this definition seems to imply that a foreign key can't
> span columns, but they definitely can:
> CREATE TABLE Employees
> (EmpID int NOT NULL,
> EmpName varchar(50) NOT NULL,
> -- more columns
> PRIMARY KEY (EmpID)
> )
> go
> CREATE TABLE Projects
> (ProjID int NOT NULL,
> ProjName varchar(25) NOT NULL,
> -- more columns
> PRIMARY KEY (ProjID)
> )
> go
> CREATE TABLE Assignments
> (EmpID int NOT NULL,
> ProjID int NOT NULL,
> AssStart smalldatetime NOT NULL,
> AssEnd smalldatetime DEFAULT NULL,
> PRIMARY KEY (EmpID, ProjID),
> FOREIGN KEY (EmpID) REFERENCES Employees,
> FOREIGN KEY (ProjID) REFERENCES Projects,
> CHECK (AssEnd > AssStart)
> )
> go
> CREATE TABLE TimeSheet
> (EmpID int NOT NULL,
> TSStart smalldatetime NOT NULL,
> TSEnd smalldatetime NOT NULL,
> TimeSpent AS CAST(DATEDIFF(minute, TSStart, TSEnd) / 60.0 AS
> decimal (3,2)),
> ProjID int NOT NULL,
> PRIMARY KEY (EmpID, TSStart),
> UNIQUE (EmpID, TSEnd),
> -- The line below is the proof the foreign keys can span multiple columns
> FOREIGN KEY (EmpID, ProjID) REFERENCES Assignments,
> CHECK (TSEnd > TSStart),
> CHECK (DATEDIFF(day, TSStart, TSEnd) = 0)
> )
> go
>
> I realise that the above doesn't answer any of the questions you asked,
> but I think that has alrteady been taken care of by Erland (thanks,
> Erland!)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

> May I ask you where you found this definition?
http://databases.about.com/cs/speci.../foreignkey.htm
(sorry - should mention the source)
Google is always a good start to begin.

Thanks for pointing that out.|||On Sun, 9 Jan 2005 22:56:48 +0100, GTi wrote:

(snip)
>> May I ask you where you found this definition?
>http://databases.about.com/cs/speci.../foreignkey.htm
>(sorry - should mention the source)
>Google is always a good start to begin.

Hi GTi,

Yes - just as long as you're aware that there's no quality control on the
internet. Always double-check, never take anything for granted!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Personally, I'd be very wary of any text book that uses the term "field"
> instead of "column" in descriptions of the relational model.
> You'll find fileds on flat files and in some non-relational databases; in
> a relational database, "column" is THE ONLY correct term. I usually don't
> comment the mis-use of this terminology when people ask questions, but if
> this is indeed a straight quote from a text-book, then it shouldn't go
> uncommented!!

Yeah, I know we are supposed to be snobby and not say "field" or "record",
but "column" and "row" are just different names for the same thing.

Recently I had all reason to damn my own usage. I was writing about how
to use a data-modelling tool, and there is one dialog where you enter
columns for a table. The dialog is laid out with a grid, where you
enter one column one each row, and then there are differnt columns
in that grid which describe different properties - name, domain,
nullability, description etc - that the table columns have.

I really should have written "fields" throughout.

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

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

bad query plan when using parameterised queries on partitioned vie

Recently I found rather a nasty consequence by using parameterised queries o
n
a partitioned view.
I have a .net app that sends parameterised queries to SQL Server 2000. SQL
Server obliges by caching the query plan to increase efficiency of subsequen
t
queries.
In my case the query is running against a partitioned view, partitioned by a
date constraint.
A query is run when the cache is cleared and the query optimiser correctly
creates a query plan specifying only the correct partition and ignoring the
other partitions, so far so good…
Subsequent queries run fine and use the cached query plan until we get a
query where the date range selected now relates to a different underlying
partition – blam, full table scan against all partitions! As you might gue
ss
the query execution times blow out by several orders of magnitude! It seems
it tries to use a bad query plan from the cache and when it realises it can
t
get the data it needs it is not smart enough to re-evaluate the query plan
properly.
Currently I am forcing a recompile on these guys go make sure the cached
plan is not used as it obviously cannot be trusted! This workaround does the
trick but I’m wondering if anyone has experienced something similar before
?
Or is there a better way to deal with this? It seems to me to be in fact a
bug as the optimiser should handle this a lot better than it does.
Thoughts anyone?
Cheers,
Chrissounds like parameter sniffing problem. Sure there are ways better than
WITH RECOMPILE. Search for "parameter sniffing" in this group or google.

bad query executionplan

Hallo!
I'm having a problem with bad query executionplans. The optimizer won't
get the right plan to execute the query fast.
The optimizer perform cluster index scan instead of using a index s
an also using a hash join instead of a loop join.
Now, when I set a join hint to use the loop join, the optimizer uses
the right index to.
Another methode i have found is to set forceplan to on. Then the
optimzer will also uses loop join and the correct indexes.
Anyone have an idea?
Server:
Language German
SQL 2000 SP3a and SP4
Windows 2003 SP1 and without
I already have reindexed all participating indexes (drop & create) an
drop an recreate all statistics.
Regards
LutzPlease post your DDL (including constraints and indexes) and problem query
or stored procedure so that we can help.
Hope this helps.
Dan Guzman
SQL Server MVP
<lutz.jahnke@.nord-com.net> wrote in message
news:1125053676.823090.8480@.g49g2000cwa.googlegroups.com...
> Hallo!
> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
> The optimizer perform cluster index scan instead of using a index s
> an also using a hash join instead of a loop join.
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
> Anyone have an idea?
> Server:
> Language German
> SQL 2000 SP3a and SP4
> Windows 2003 SP1 and without
> I already have reindexed all participating indexes (drop & create) an
> drop an recreate all statistics.
> Regards
> Lutz
>|||In addition to Dan's reply: please specify if you are using a stored
procedure with parameters (which can benefit from parameter sniffing) or
running an ad-hoc query or a stored procedure with variables. Do you get
a 'good' query plan if you use literals instead of variables/parameters?
Gert-Jan
lutz.jahnke@.nord-com.net wrote:
> Hallo!
> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
> The optimizer perform cluster index scan instead of using a index s
> an also using a hash join instead of a loop join.
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
> Anyone have an idea?
> Server:
> Language German
> SQL 2000 SP3a and SP4
> Windows 2003 SP1 and without
> I already have reindexed all participating indexes (drop & create) an
> drop an recreate all statistics.
> Regards
> Lutz|||(lutz.jahnke@.nord-com.net) writes:
> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
> The optimizer perform cluster index scan instead of using a index s
> an also using a hash join instead of a loop join.
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
> Anyone have an idea?
In additions to Dan's and Gert-Jan's suggestions, try running UPDATE
STATISTICS on the involved tables. Statistics may be out of date. SQL
Server updates statistics automatically, but there is a lag and if
your search condition includes a column with monotonically growing
values, the optimizer will make incorrect estimates.
For more heavy-duty, you can add WITH FULLSCAN to the UPDATE STATISTICS
statement.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Bad ordering when ordering by varchar field

Hi guys,
did anyone come across this problem?
I have a query where I ORDER BY Part_Number field which is varchar but
the ordered result is
10169-G
10169-MVS
10169
which is mess because normal person would put first the 10169 and then
the others?
Thanks for any help Milan
hi Milan,
Milan Reznicek wrote:
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar
> but the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and
> then the others?
> Thanks for any help Milan
can you please post your actual code, as simple repro are correct, on my
instance..
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number
--<--
Part_Number
10169
10169-G
10169-MVS
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What collation is your database using?
"Milan Reznicek" <reznicek@.rezna.info> wrote in message
news:uZCQw9ZSFHA.356@.TK2MSFTNGP14.phx.gbl...
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar but
> the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and then
> the others?
> Thanks for any help Milan
>
|||Thanks both, you and JJ. Your code is working all right until I add my
collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and which
completely messes up the ordering.
Milan
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> pe v diskusnm pspvku
news:3d4df0F6opoh9U1@.individual.net...
> hi Milan,
> Milan Reznicek wrote:
> can you please post your actual code, as simple repro are correct, on my
> instance..
> SET NOCOUNT ON
> DECLARE @.t TABLE ( Part_Number varchar(10) )
> INSERT INTO @.t VALUES ( '10169-G' )
> INSERT INTO @.t VALUES ( '10169-MVS' )
> INSERT INTO @.t VALUES ( '10169' )
> SELECT t.Part_Number
> FROM @.t t
> ORDER BY t.Part_Number
> --<--
> Part_Number
> --
> 10169
> 10169-G
> 10169-MVS
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Milan,
Milan Reznicek wrote:
> Thanks both, you and JJ. Your code is working all right until I add my
> collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and
> which completely messes up the ordering.
of course collation change the way the sort order is performed...
you can, if you are allowed to from your app design, change the sort order
rules for queries like that like
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number COLLATE Latin1_General_BIN
you specify that way a different sort order and rule the order by clause
must conform with... but verify this do not comprimise other app designs and
constraints...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Bad executed Plan and wrong Result by SQL

I have one query that executes many times in a week.
I created one Maintenances plan that Rebuild all index in my Database that
has been executed at 23:40 Saturday until stop finished at Sunday.

However at middle of week (Wednesday or Thursday), that query dont return
result like that must be. The time exceeded and the result are total wrong.

I compare the normal executed plan and the crazy one that SQL create to
mount result.

The normal is nested with index seek (very fast, the wrong is Merger with
hash aggregate (very slow). After Index Rebuild, the executed plan bring
result that must be, but when the merge plan are executed with many updates
on that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of week, the
result are total wrong, with many rows back.

I recommended Index Seek force by coalesce function on one column
aggregate, but everyone here were very panic with that behavior of SQL
Server.

Please , anyone help me to explain that!

Krisnamourt!

P.S: Attachments :

--Force Index Query with coalesce
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=coalesce(G.HANDLE,G.HANDLE) AND E.CLASSEGERENCIALPAGTO is NULL

--Normal Query
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL

--
Message posted via http://www.sqlmonster.comStmtText
-----------------------
-----------------------
------------
--Normal Query
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
option(merge join)

(1 row(s) affected)

StmtText
-----------------------
-----------------------
----------
|--Compute Scalar(DEFINE:([Expr1002]=Convert([globalagg1004])))
|--Stream Aggregate(DEFINE:([globalagg1004]=SUM([partialagg1003])))
|--Parallelism(Gather Streams)
|--Merge Join(Inner Join, MERGE:([G].[HANDLE])=([E].[GUIA])
, RESIDUAL:([G].[HANDLE]=[E].[GUIA]))
|--Parallelism(Distribute Streams, PARTITION COLUMNS:
([G].[HANDLE]))
| |--Index Seek(OBJECT:([Saude].[dbo].[SAM_GUIA].
[AX_1603PEG] AS [G]), SEEK:([G].[PEG]=736740) ORDERED FORWARD)
|--Sort(ORDER BY:([E].[GUIA] ASC))
|--Hash Match(Aggregate, HASH:([E].[GUIA]),
RESIDUAL:([E].[GUIA]=[E].[GUIA]) DEFINE:([partialagg1003]=COUNT(*)))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS:([E].[GUIA]))
|--Clustered Index Scan(OBJECT:([Saude]
..[dbo].[SAM_GUIA_EVENTOS].[PK__SAM_GUIA_EVENTOS__68736660] AS [E]), WHERE:(
[E].[CLASSEGERENCIALPAGTO]=NULL))

(10 row(s) affected)

--
Message posted via http://www.sqlmonster.com|||I Mean...the wrong result bring back many row with E.CLASSEGERENCIALPAGTO
not null(this column shows many data )...CRAZY!!!

Anyone help me to explain that!!

Kris

--
Message posted via http://www.sqlmonster.com|||Krisnamourt Correia via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I have one query that executes many times in a week.
> I created one Maintenances plan that Rebuild all index in my Database that
> has been executed at 23:40 Saturday until stop finished at Sunday.
> However at middle of week (Wednesday or Thursday), that query don't
> return result like that must be. The time exceeded and the result are
> total wrong.
> I compare the normal executed plan and the "crazy" one that SQL create to
> mount result.
> The normal is nested with index seek (very fast, the wrong is Merger
> with hash aggregate (very slow). After Index Rebuild, the executed plan
> bring result that must be, but when the merge plan are executed with
> many updates on that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of
> week, the result are total wrong, with many rows back.
> I recommended Index Seek force by coalesce function on one column
> aggregate, but everyone here were very panic with that behavior of SQL
> Server.

Do I understand you clarifiation in the other article correctly, that
when you say "results are total wrong", you do in fact mean the query
plan? If you really get incorrect resuls from the query, this is a
serious bug, and you should definitely open a case with Microsoft to
have it investigate.

If the problem is "only" the incorrect query plan, and the slow execution
time, this is more "normal" behaviour.

Recall that SQL Server uses a cost-based optimizer that estimates the
cost of various query plans from statistics about the data. A small
error in the estimate can have serious consequences.

Since you have good performance after index rebuild, it might be a good
idea to schedule index rebuild on these two tables daily.

I also notice that the bad plan involves parallelism. If you add
OPTION (MAXDOP 1), you tell SQL Server not to use parallelism. This
is often enough to get a good plan.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The real problem is incorret result. I cant rebuild index on these two
table , because our scenario works 24 hours by day. These table are too big
(17 Gbytes one and 4 Gbytes other), with many Index. The Index Rebuild only
can do at weekends. I intend to eliminated some Index that are redundant(I
just begun), but that bug is very crazy. That became SQL Server not a good
solution for OLTP that grows up strongly. I saw many scenarios like
that...bad performance when the Database became too large.

--
Message posted via http://www.sqlmonster.com|||Krisnamourt Correia via SQLMonster.com (forum@.SQLMonster.com) writes:
> The real problem is incorret result. I cant rebuild index on these two
> table , because our scenario works 24 hours by day. These table are too
> big (17 Gbytes one and 4 Gbytes other), with many Index. The Index
> Rebuild only can do at weekends. I intend to eliminated some Index that
> are redundant(I just begun), but that bug is very crazy. That became SQL
> Server not a good solution for OLTP that grows up strongly. I saw many
> scenarios like that...bad performance when the Database became too
> large.

Looking at your query, the incorrect results may be a known issue.
I think I recognize the type of query. I would suggest that you open
a case with Microsoft to investigate this.

If there is a fix, it is likely to be available in SP4 which was recently.
Unfortunate there is an issue which concerns AWE which I would expect to
concern you, given your table sizes. I would expect Microsoft to have a fix
for this issue soon, though. See further
http://www.microsoft.com/sql/downloads/2000/sp4.asp.

Note that SP4 is only likely to address the incorrect result. The query
plan and the fragmentation is less likely to improve.

Some questions:
o Do you have autostats enabled on these tables? (Maybe you should turn
them off)
o What actual fragmentation do you have by the middle of the week?

If the fragmentation increases rapidly, maybe you should look at changing
the clustered index to one that is less prone to fragmentation given
the update pattern. But here is of course a tradeoff with queries
that may depend on the clustered index.

Could you post the CREATE TABLE and CREATE INDEX statements for the
two tables?

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

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