Help with SQL query please

I have a table that have 4 fields of interest, A, B, C and D (plus others
that are not of interest).
I am not interested in completely unique rows (based on the fields A,B,C &
D).
What I am interested in rows where the A, B & C fields are identical, but
within these identical "groups" the D field is different.
So, I though I'd write an SQL statement that uses a sub query.
The inner query would bring back all rows that are not unique (by using a
count > 1 statement) and the outer query would then get the details
The SQL I came up with is as follows.
The query is as follows:
---
select A, B, C, D
from X
where exists
(select A, B, C, count(D)
from X
group by A, B, C
having count(D) > 1)
order by A, B, C
---
However...when running the sub query in isolation and ordering the results,
I found that the lowest value of "A" returned was 3. But, when I run the
query as a whole, the first set of results returned have data for rows that
contain A values of 1 & 2.
Puzzled over this, but now need some help.
Thanks in advance if you can provide this...
GriffPlease ALWAYS post DDL with questions like this so that we don't have
to guess what your table looks like. It also helps to include some
sample data and show your required end result.
The problems with the query you posted seem to be twofold. First the
subquery isn't correlated. Second COUNT(D) will count >1 if there is
more than one row even if D is the same (non-null) in each case. That
would make sense if (A,B,C,D) is a key of this table but you didn't
actually specify a key (did I mention about the importance of including
DDL?).
Assuming (A,B,C,D) is not nullable (yes, the DDL would have told us
that too) you can do it with a correlated subquery:
SELECT x.a, x.b, x.c, x.d
FROM x
WHERE EXISTS
(SELECT *
FROM x AS z
WHERE x.a = z.a
AND x.b = z.b
AND x.c = x.c
AND x.d <> z.d)
ORDER BY x.a, x.b, x.c, x.d ;
or with a derived table:
SELECT x.a, x.b, x.c, x.d
FROM
(SELECT a, b, c
FROM x
GROUP BY a, b, c
HAVING MIN(d)<MAX(d)) AS z
JOIN x
ON x.a = z.a
AND x.b = z.b
AND x.c = x.c
ORDER BY x.a, x.b, x.c, x.d ;
(both untested)
Essential reading on the best way to post a problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--

Help with SQL Query

I have 2 tables:

- Users, with fields id and fullName
- Accounts, with accntID, userID, accntName, Active

I need to write a query which lists all the users, along with the
number of accounts they have.

I am using the following query -

SELECT U.id, U.fullName, Count(A.accntID) AS CountOfaccntID
FROM users AS U LEFT JOIN accounts AS A ON U.id = A.userID
WHERE A.active=Yes
GROUP BY U.id, U.fullName;

My data is as follows:

Users
=====
1, User1
2, User2

Accounts
========
1,1,User1_Accnt1,true
2,1,User1_Accnt2,true
3,2,User2_Accnt1,false

The expected output is :

1, User1, 2
2, User2, 0

But I get,

1, User1, 2

What do I need to change in the query?> What do I need to change in the query?

Only one word: instead of "WHERE" use "AND".

Razvan|||(b_naick@.yahoo.ca) writes:
> I have 2 tables:
> - Users, with fields id and fullName
> - Accounts, with accntID, userID, accntName, Active
> I need to write a query which lists all the users, along with the
> number of accounts they have.
> I am using the following query -
> SELECT U.id, U.fullName, Count(A.accntID) AS CountOfaccntID
> FROM users AS U LEFT JOIN accounts AS A ON U.id = A.userID
> WHERE A.active=Yes
> GROUP BY U.id, U.fullName;

To explain Razvan's answer a little more, this is what is happening:

Logically, in an SQL query, you start with the table in the FROM
clause, and then you build a new table every time you tack on a
new table with a JOIN operator (this can be changed with parentheses).
Eventually, the resulting table is filtered by the WHERE clause.

So you start with Users, and left-join it to Accounts. You now have
a table which has all the original rows in Users. For the matching
columns in Accounts, the columns from Accounts have the value from
that table. From the non-matching rows, you have NULL. Then comes the
WHERE clause, which says "A.Active=Yes". Which means that all rows
with NULL in A.Active are filtered away. That is, all those rows
from Users with no matching accounts are no longer in the result set.

When you change WHERE to AND, the condition A.Active=Yes moves to
the JOIN operation. This means that only the rows from Accounts
with Active=Yes are brought in, and remaining rows have NULL in
all columns. In your original query, the rows with Active=No had
values in Accounts in that intermediate table (which is only locigal).

This is indeed a common error to make, and it took me sometime as well
to understand how the FROM-JOIN co-operates with WHERE, when I started
to use this syntax.

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

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

May this solve your problem

create Table Users(UserId int primary key,Username varchar(20))

insert into Users(Userid,UserName) values(1,'User1')
insert into Users(Userid,UserName) values(2,'User2')

Create Table Accounts(AccountNo int primary key,UserId int references
Users,AccountName varchar(20),Active varchar(20))

insert into Accounts VALUES(1,1,'User1_Accnt1','true')
insert into Accounts VALUES(2,1,'User1_Accnt2','true')
insert into Accounts VALUES(3,2,'User2_Accnt1','false')

select U.*,count(case when A.Active='true' then 1 else null end) from
Users U, Accounts A
where U.userid = A.userid
group by U.Userid,U.UserName

Drop Table Accounts
Drop Table Users
Please do post DDL , DML as it become easy for others to test their
queries

With warm regards
Jatinder Singh

b_naick@.yahoo.ca wrote:
> I have 2 tables:
> - Users, with fields id and fullName
> - Accounts, with accntID, userID, accntName, Active
> I need to write a query which lists all the users, along with the
> number of accounts they have.
> I am using the following query -
> SELECT U.id, U.fullName, Count(A.accntID) AS CountOfaccntID
> FROM users AS U LEFT JOIN accounts AS A ON U.id = A.userID
> WHERE A.active=Yes
> GROUP BY U.id, U.fullName;
>
> My data is as follows:
> Users
> =====
> 1, User1
> 2, User2
> Accounts
> ========
> 1,1,User1_Accnt1,true
> 2,1,User1_Accnt2,true
> 3,2,User2_Accnt1,false
> The expected output is :
> 1, User1, 2
> 2, User2, 0
> But I get,
> 1, User1, 2
> What do I need to change in the query?

Help with SQL if statement and adding fields together

I have a query that I need a hand on. I am trying to add togther some
fiends based on values of another.

What I would like to add a billing total by saying more or less the
following:

SELECT labor_hours, labor_cost, expidite_fee, flat_rate,
include_repair_cost, include_cal, include_flat_rate, include_parts,
cur_bill,
(labor_hours * labor_cost) AS labor_total,
(ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0)) AS cal_total,
(
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS parts_total,
(
(labor_hours * labor_cost) + expidite_fee + flat_rate +
ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0) +
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS actual_total,
(
expidite_fee
IF include_repair_cost = 1
+ (labor_hours * labor_cost)
IF include_flat_rate = 1
+ flat_rate
IF include_cal = 1
+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER
BY dateCAL DESC),0)
IF include_parts = 1
+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS billing_total
FROM view_inventory
WHERE orderID=79559

I know the IF part is whacked, that's where I need the help. Is this
type of thing even possible? Or even efficent? Is it wise to subquery
for totals (not like I have a choice based on the application
requirements)? help.On 18 Mar 2005 07:56:07 -0800, Rob Kopp wrote:
(snip)
>(
>expidite_fee
>IF include_repair_cost = 1
>+ (labor_hours * labor_cost)
>IF include_flat_rate = 1
>+ flat_rate
>IF include_cal = 1
>+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER
>BY dateCAL DESC),0)
>IF include_parts = 1
>+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
>repair_partsID WHERE orderID=79559),0) +
>ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
>misc_part_assocID WHERE orderID=79559),0)
>) AS billing_total
>FROM view_inventory
>WHERE orderID=79559
>I know the IF part is whacked, that's where I need the help. Is this
>type of thing even possible? Or even efficent?

Hi Rob,

You'll need to use CASE:

(
expidite_fee +
CASE WHEN include_repair_cost = 1
THEN (labor_hours * labor_cost)
ELSE 0 END +
CASE WHEN include_flat_rate = 1
THEN flat_rate
ELSE 0 END +
CASE WHEN include_cal = 1
THEN ISNULL((subquery cal_cost), 0)
ELSE 0 END +
CASE WHEN include_parts = 1
THEN ISNULL((subquery gptotal), 0) +
ISNULL((subquery gnptotal), 0)
ELSE 0 END
) AS billing_total

> Is it wise to subquery
>for totals (not like I have a choice based on the application
>requirements)? help.

Well, you can do some things to speed up the query.

Since you use the same subquery in two places, you could use a derived
table. Like this:

SELECT a, b, c, a + b + c AS GrandTotal
FROM (SELECT complicated_expression AS a,
complicated_expression AS b,
complicated expression AS c
FROM YourTable
WHERE ...) AS x

Another possibility is to use a join between your inventory table and
derived tables where the grouping has already been done:

SELECT ...,
gptotal,
...,
complicated expression using gptotal,
...
FROM view_inventory AS vi
LEFT OUTER JOIN (SELECT orderID,
SUM((qty * cost) + premium_charge) AS gptotal
FROM repair_partsID
GROUP BY orderID) AS a
ON a.orderID = vi.orderID
LEFT OUTER JOIN (...) AS b
ON b.orderID = vi.orderID
(etc)
WHERE vi.orderID = 79559

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||You are the man, Hugo. I bow to your majesty.sql

Help with sql

Hi,
This might be a simple one .. I have a table with two fields (There are more
but Ill concentrate on this ones).
One field is 'address', the other is 'address2', both are nvarchar
If I want to select both into another table but 'concatenate them' how can I
do this ?
At the moment this is what I have
Select address, address2
into table2
from table1
How can I have something like
Select address + address2 as BIGaddress
into table2
from table1
AleksYour statement seems to be ok...or I haven't understood your question...
Select address + address2 as BIGaddress
into table2
from table1
Francesco Anti
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:%23vZHbeDbFHA.3328@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This might be a simple one .. I have a table with two fields (There are
> more but Ill concentrate on this ones).
> One field is 'address', the other is 'address2', both are nvarchar
> If I want to select both into another table but 'concatenate them' how can
> I do this ?
> At the moment this is what I have
> Select address, address2
> into table2
> from table1
> How can I have something like
> Select address + address2 as BIGaddress
> into table2
> from table1
> Aleks
>|||What you have should work fine
Select address + address2 as BIGaddress
into table2
from table1
The SELECT INTO will create a new table called table2 with one column called
BIGaddress
If table2 already exists then change the SELECT INTO into an INSERT
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:#vZHbeDbFHA.3328@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This might be a simple one .. I have a table with two fields (There are
more
> but Ill concentrate on this ones).
> One field is 'address', the other is 'address2', both are nvarchar
> If I want to select both into another table but 'concatenate them' how can
I
> do this ?
> At the moment this is what I have
> Select address, address2
> into table2
> from table1
> How can I have something like
> Select address + address2 as BIGaddress
> into table2
> from table1
> Aleks
>|||The query which you have given will work Right ?
Regards,
Peri
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:#vZHbeDbFHA.3328@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This might be a simple one .. I have a table with two fields (There are
more
> but Ill concentrate on this ones).
> One field is 'address', the other is 'address2', both are nvarchar
> If I want to select both into another table but 'concatenate them' how can
I
> do this ?
> At the moment this is what I have
> Select address, address2
> into table2
> from table1
> How can I have something like
> Select address + address2 as BIGaddress
> into table2
> from table1
> Aleks
>|||Just watch out for two things:
1. If either address or address2 is NULL, the concatenation will be NULL.
You can use this instead: coalesce(address,N'') + coalesce(address2,N'')
2. If address+address2 exceeds 4000 characters, you will lose information.
Steve Kass
Drew University
Aleks wrote:

>Hi,
>This might be a simple one .. I have a table with two fields (There are mor
e
>but Ill concentrate on this ones).
>One field is 'address', the other is 'address2', both are nvarchar
>If I want to select both into another table but 'concatenate them' how can
I
>do this ?
>At the moment this is what I have
>Select address, address2
>into table2
>from table1
>How can I have something like
>Select address + address2 as BIGaddress
>into table2
>from table1
>Aleks
>
>|||Jaja .. I didnt even tested and worked fine ... how silly
A
"Steve Kass" <skass@.drew.edu> wrote in message
news:u27rGxDbFHA.2440@.TK2MSFTNGP10.phx.gbl...
> Just watch out for two things:
> 1. If either address or address2 is NULL, the concatenation will be NULL.
> You can use this instead: coalesce(address,N'') + coalesce(address2,N'')
> 2. If address+address2 exceeds 4000 characters, you will lose information.
> Steve Kass
> Drew University
>
> Aleks wrote:
>

Help with SP

I have a webform that I need to insert data into a db. I would like to have a stored procedure handle the insert for me.

Here are the data fields from the form:

<td><asp:textbox id="f_name" runat="server"></asp:textbox></td>
<td><asp:textbox id="l_name" runat="server"></asp:textbox></td>
<td><asp:textbox id="emp_num" runat="server"></asp:textbox></td>
<td><asp:textbox id="email" runat="server"></asp:textbox></td>
<td><asp:dropdownlist id=dd1 runat="server" DataMember="Line_Item" DataSource="<%# dsLineItem1 %>" DataTextField="LI_ID" DataValueField="ID"></asp:dropdownlist></td>
<td><asp:dropdownlist id=dd2 runat="server" DataMember="Component" DataSource="<%# dsComponent1 %>" DataTextField="Component" DataValueField="ID"></asp:dropdownlist></td>
<td><asp:dropdownlist id=dd3 runat="server" DataMember="Activity" DataSource="<%# dsActivity1 %>" DataTextField="Abbrev" DataValueField="ID"></asp:dropdownlist></td>


Needed Solution:
I need to create a stored procedure that will insert the data from the above fields into the following database:

ProfileDB
Table columns:
id
f_name
l_name
emp_num
email
line_item
component
activity

I am sure that there are some rules for this I am unaware of (such as field names must match table column names etc.).

Thank you for your help.

BTW,

My version of SQL is 2000.

Thank you again.

Sincerely,

TimI have constucted a stored procedure that is at the least error free:
If you have any comments or suggestions regarding this sp I would greatly appreciate your input since I am new to SQL.

CREATE PROCEDURE dbo.InsertProfile
(
@.F_Name [varchar] (100),
@.L_Name [varchar] (100),
@.Emp_Num [numeric] (9),
@.Email [varchar] (250),
@.Line_Item [varchar] (250),
@.Component [varchar] (250),
@.Activity [varchar] (250)
)

AS

Insert into [dbo.InsertProfile]
(
[F_Name],
[L_Name],
[Emp_Num],
[Email],
[Line_Item],
[Component],
[Activity]
)

Values

(@.F_Name, @.L_Name, @.Emp_Num, @.Email, @.Line_Item, @.Component, @.Activity)

GO

Thank you.|||OK

You might want to check for errors

DECLARE @.Error, @.Rowcount

...sql statement

SELECT @.Error = @.@.ERROR, @.RowCount = @.@.Rowcount

Then interogate thos values...if @.Error is other than 0, then you have a problem...

And if @.Rowcount doesn't = 1 the that's a problem as well (don't know how that would ever happen, but it's a check)

Help with simple SQL query

Hi All

Can anyone tell me what this simple SQL query would be:

Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".

Many thanks

accelerator

try something like this

select * from table where product like '%black%' or product like '%dvd%' or product like '%player%'

the above would search the product field...continue the same with your other fields.

|||Use ofFreeTextTable might be appropriate in this case. It's not the simple query you were looking for, but may provide more meaningful results.

HELP with Running value totals

HI,
I have a table created. I need to have static fields.
The table has one group, where I use an IIF statement to point the values
into one of the three static fields. The reason I am using a table is that I
have to show months/loan programs with zero as well. The fixed static fields
are The loan types. I need to add a sum to the group footer so for each
month I can show the grand total of the three loan programs. Please help me
with this...I have been working on this for two days now and just can not
figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
an error
that states "The value expression for the textbox â'textbox31â' refers to the
report item â'Textbox21â'. Report item expressions can only refer to other
report items within the same grouping scope or a containing grouping scope."
Okay so here it is:
FY2003 FY2004 FY2005
Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
Sum, Nothing)
Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)
Loan3 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)All of the texboxes have to be in the same scope... (the same level in the
table etc...), and you probably need to supply the scope name ie the group
name etc.. You may even have to split up the sums ie
=Sum(Reportitems!textbox1.Value,"mygroup") +
sum(ReportItems!Textbox2.Value,"mygroup") ...etc
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Susan" wrote:
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is that I
> have to show months/loan programs with zero as well. The fixed static fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
> an error
> that states "The value expression for the textbox â'textbox31â' refers to the
> report item â'Textbox21â'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)|||This is exactly the problem that I'm having. I notice that there has been
no reply to this request in a month. Is that because there's no way to make
it work?
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:817F9F2D-3BE0-489A-8575-325DFE42CC20@.microsoft.com...
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is
> that I
> have to show months/loan programs with zero as well. The fixed static
> fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help
> me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but
> receive
> an error
> that states "The value expression for the textbox 'textbox31' refers to
> the
> report item 'Textbox21'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping
> scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1"
> and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002,
> CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)

Help with query formatting

Hi all,
I have one table that contains, among other fields, a NAME and 14 SERVICE
fields. The ID number is obvious and the SERVICE fields contain a "Y" if
that particular person (NAME) carries out the service. A person can carry
out 1 or more services or the person can carry no service.
I am trying to write a search screen where the user will enter a partial
name and select 1 or more services to be included. The query will then
return and person who's name is LIKE the entry AND who has at least 1 of the
entered services.
I have the following: "SELECT * FROM PROVIDERS WHERE [first name] LIKE
'%TOM%' AND srv1 = 'Y' OR srv2 = 'Y' OR srv3='Y'.
What I am getting from the above is all people with TOM in their name AND
service 1. I am also getting people - no matter what their name is - with
either service 2 or service 3.
I need all people with TOM in their name and EITHER service 1, service 2 or
service 3.
Any help is appreciated.
George
George,
You need to add parentheses around the (this OR that OR other...).
The priority of boolean operators is that AND is evaluated before OR.
SELECT *
FROM PROVIDERS
WHERE [first name] LIKE '%TOM%'
AND (
srv1 = 'Y' OR srv2 = 'Y' OR srv3='Y'
)
(Ultimately, you will probably have more luck with any sizeable
application if you do not use 14 different Y/N columns. Almost
always, it is a better design to maintain a separate table to record
that information. My suggestion should get you moving forward,
but there is always the risk that it moves you forward closer to the
quicksand.)
Steve Kass
Drew University
George wrote:

>Hi all,
>I have one table that contains, among other fields, a NAME and 14 SERVICE
>fields. The ID number is obvious and the SERVICE fields contain a "Y" if
>that particular person (NAME) carries out the service. A person can carry
>out 1 or more services or the person can carry no service.
>I am trying to write a search screen where the user will enter a partial
>name and select 1 or more services to be included. The query will then
>return and person who's name is LIKE the entry AND who has at least 1 of the
>entered services.
>I have the following: "SELECT * FROM PROVIDERS WHERE [first name] LIKE
>'%TOM%' AND srv1 = 'Y' OR srv2 = 'Y' OR srv3='Y'.
>What I am getting from the above is all people with TOM in their name AND
>service 1. I am also getting people - no matter what their name is - with
>either service 2 or service 3.
>I need all people with TOM in their name and EITHER service 1, service 2 or
>service 3.
>Any help is appreciated.
>George
>

Help with query

I have a table in my database that has two fields. The registers on this
table look like the following:
Field 1 Field2
1 a
2 a
3 b
4 b
5 a
6 a
I would like to perform a query in the database in order to group sequences
of data, for the table above, I expect the following result:
Field1 Field2
1,2 a
3,4 b
5,6 a
I know I can group the data by using my Field2, but I do not know how I
would separate the different intervals.
Thank you in advance for your help.
Loureno.
Hi, maybe this example helps you
/* BEGIN SCRIPT */
drop table tmp_group
go
drop table tmp_result
go
set nocount on
declare @.field1 int,
@.field2 char(1),
@.field2_aux char(1),
@.line varchar(50)
create table tmp_group(
field1int,
field2char(1) )
insert tmp_group values ( 1, 'a')
insert tmp_group values ( 2, 'a')
insert tmp_group values ( 3, 'b')
insert tmp_group values ( 4, 'b')
insert tmp_group values ( 5, 'a')
insert tmp_group values ( 6, 'a')
create table tmp_result(
linevarchar(50),
field2char(1))
declare cu_group cursor for
select field1, field2 from tmp_group (nolock)
open cu_group
fetch next from cu_group into @.field1, @.field2
-- select @.@.fetch_status, @.field1, @.field2
if @.@.fetch_status = 0
begin
while @.@.fetch_status = 0
begin
set @.field2_aux = @.field2
set @.line = ''
while @.field2_aux = @.field2 and @.@.fetch_status = 0
begin
set @.line = @.line + convert(varchar(3), @.field1) + ','
fetch next from cu_group into @.field1, @.field2
-- select @.@.fetch_status, @.field1, @.field2
end
insert tmp_result values ( left(@.line, len(@.line) - 1), @.field2_aux )
end
end
close cu_group
deallocate cu_group
select * from tmp_result
select * from tmp_group
/* END SCRIPT */
"news.microsoft.com" wrote:

> I have a table in my database that has two fields. The registers on this
> table look like the following:
> Field 1 Field2
> 1 a
> 2 a
> 3 b
> 4 b
> 5 a
> 6 a
> I would like to perform a query in the database in order to group sequences
> of data, for the table above, I expect the following result:
> Field1 Field2
> 1,2 a
> 3,4 b
> 5,6 a
> I know I can group the data by using my Field2, but I do not know how I
> would separate the different intervals.
> Thank you in advance for your help.
> Louren?o.
>
>

Help with query

I have a table in my database that has two fields. The registers on this
table look like the following:
Field 1 Field2
1 a
2 a
3 b
4 b
5 a
6 a
I would like to perform a query in the database in order to group sequences
of data, for the table above, I expect the following result:
Field1 Field2
1,2 a
3,4 b
5,6 a
I know I can group the data by using my Field2, but I do not know how I
would separate the different intervals.
Thank you in advance for your help.
Lourenço.Hi, maybe this example helps you
/* BEGIN SCRIPT */
drop table tmp_group
go
drop table tmp_result
go
set nocount on
declare @.field1 int,
@.field2 char(1),
@.field2_aux char(1),
@.line varchar(50)
create table tmp_group(
field1 int,
field2 char(1) )
insert tmp_group values ( 1, 'a')
insert tmp_group values ( 2, 'a')
insert tmp_group values ( 3, 'b')
insert tmp_group values ( 4, 'b')
insert tmp_group values ( 5, 'a')
insert tmp_group values ( 6, 'a')
create table tmp_result(
line varchar(50),
field2 char(1) )
declare cu_group cursor for
select field1, field2 from tmp_group (nolock)
open cu_group
fetch next from cu_group into @.field1, @.field2
-- select @.@.fetch_status, @.field1, @.field2
if @.@.fetch_status = 0
begin
while @.@.fetch_status = 0
begin
set @.field2_aux = @.field2
set @.line = ''
while @.field2_aux = @.field2 and @.@.fetch_status = 0
begin
set @.line = @.line + convert(varchar(3), @.field1) + ','
fetch next from cu_group into @.field1, @.field2
-- select @.@.fetch_status, @.field1, @.field2
end
insert tmp_result values ( left(@.line, len(@.line) - 1), @.field2_aux )
end
end
close cu_group
deallocate cu_group
select * from tmp_result
select * from tmp_group
/* END SCRIPT */
"news.microsoft.com" wrote:
> I have a table in my database that has two fields. The registers on this
> table look like the following:
> Field 1 Field2
> 1 a
> 2 a
> 3 b
> 4 b
> 5 a
> 6 a
> I would like to perform a query in the database in order to group sequences
> of data, for the table above, I expect the following result:
> Field1 Field2
> 1,2 a
> 3,4 b
> 5,6 a
> I know I can group the data by using my Field2, but I do not know how I
> would separate the different intervals.
> Thank you in advance for your help.
> Lourenço.
>
>

Help with query

I have 3 tables

A table of users containing the fields always required for a user.
users:
userid
email
name
password
projectid

A table of extra properties for a user that can be different from each
project.
These properties can be "location", "department", and so on.
userproperties
userpropertyid
propertyname
projectid

A table of the value of each of the extra properties for each user
userpropertyvalues
userpropertyid
userid
userpropertyvalue

Now I want to do a select statement that returns

userid, email, name, password, propertyname[1], propertyname[2],
propertyname[3]

[userid],[email],[name],[password],[propertyvalue1],[propertyvalue2],propertyvalue[3]

(when I pass a projectid)

I just don't seem to be able to do that. Any ideas?

Thank you very much in advance.

FlemmingHi

You design does not look to be correct. If a user can only be in one project
then I would not expect projectid to be in userproperties but I would expect
userid to appear in it. If a user can have multiple projects then I would
expect both to be in userproperties and userpropertyvalues. It is also not
clear how you would rank these assuming propertyid is a numeric then the
first property has the the minimum propertyid, the second property is the
minimum propertyid greater then the first propertyid, the third property is
the minimum propertyid greater then the second propertyid. You can then join
to the users to userproperties/userpropertyvalues three times to get the
values you want.

Also check out how to post DDL and example data at
http://www.aspfaq.com/etiquett**e.asp?id=5006 and
example data as insert statements
http://vyaskn.tripod.com/code.**htm#inserts
It is also useful to post your current attempts at solving the problem.

John

<flemming.madsen@.gmail.com> wrote in message
news:1111790204.659952.289470@.z14g2000cwz.googlegr oups.com...
>I have 3 tables
> A table of users containing the fields always required for a user.
> users:
> userid
> email
> name
> password
> projectid
> A table of extra properties for a user that can be different from each
> project.
> These properties can be "location", "department", and so on.
> userproperties
> userpropertyid
> propertyname
> projectid
> A table of the value of each of the extra properties for each user
> userpropertyvalues
> userpropertyid
> userid
> userpropertyvalue
>
> Now I want to do a select statement that returns
> userid, email, name, password, propertyname[1], propertyname[2],
> propertyname[3]
> [userid],[email],[name],[password],[propertyvalue1],[propertyvalue2],propertyvalue[3]
> (when I pass a projectid)
> I just don't seem to be able to do that. Any ideas?
> Thank you very much in advance.
> Flemming|||(flemming.madsen@.gmail.com) writes:
> A table of users containing the fields always required for a user.
> users:
> userid
> email
> name
> password
> projectid
> A table of extra properties for a user that can be different from each
> project.
> These properties can be "location", "department", and so on.
> userproperties
> userpropertyid
> propertyname
> projectid
> A table of the value of each of the extra properties for each user
> userpropertyvalues
> userpropertyid
> userid
> userpropertyvalue
>
> Now I want to do a select statement that returns
> userid, email, name, password, propertyname[1], propertyname[2],
> propertyname[3]
>
[userid],[email],[name],[password],[propertyvalue1],[propertyvalue2],propert
yvalue[3]
> (when I pass a projectid)
> I just don't seem to be able to do that. Any ideas?

For this kind of questions, it always a good idea to include:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This permits anyone who answer to post a tested query.

In you case, there are several loose ends. For instance, in the
userpropertyvaules, I would expect a projectid, since I would
expect (projectid, userpropertyid) to be the primary key of
userproperties. It seems now that userpropertyid alone is the
key. Another loose end is how you now which propertyvalue is #1
and so on. Furthermore, do we know if all users have all properties
for a project?

So this query is very much just a sketch, but hopefully you can work
from it.

SELECT u.userid, u.email, u.name, u.password, upv1.userpropertyvalue,
upv2.userpropertyvalue, upv3.userpropertyvalue
FROM users u
JOIN userproperties up1 ON u.projectid = up1.projectid
JOIN userproperties up2 ON u.projectid = up2.projectid
JOIN userproperties up2 ON u.projectid = up2.projectid
LEFT JOIN userpropertyvalues upv1
ON up1.userpropertyid = upv1.userpropertyd
AND upv1.userid = u.userid
LEFT JOIN userpropertyvalues upv2
ON up2.userpropertyid = upv2.userpropertyd
AND upv2.userid = u.userid
LEFT JOIN userpropertyvalues upv3
ON up3.userpropertyid = upv3.userpropertyd
AND upv3.userid = u.userid
WHERE u.projectid = @.projectid
AND up1.projectid = @.projectid
AND up2.projectid = @.projectid
AND up3.projectid = @.projectid

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

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

Your query works. Thank you very much.

One issue though: I might not in advance be aware of the number of
properties for each user.

Any ideas on alterting the query (or splitting it up) so I can deal
with that?

Thank you very much,
Flemming|||Update:

Erland,

I modified your proposal slightly and I have now solved my problem.

Once again, thank you very much for your kind help.

Sincerely,
Flemming|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> SELECT u.userid, u.email, u.name, u.password, upv1.userpropertyvalue,
> upv2.userpropertyvalue, upv3.userpropertyvalue
> FROM users u
> JOIN userproperties up1 ON u.projectid = up1.projectid
> JOIN userproperties up2 ON u.projectid = up2.projectid
> JOIN userproperties up2 ON u.projectid = up2.projectid
> LEFT JOIN userpropertyvalues upv1
> ON up1.userpropertyid = upv1.userpropertyd
> AND upv1.userid = u.userid
> LEFT JOIN userpropertyvalues upv2
> ON up2.userpropertyid = upv2.userpropertyd
> AND upv2.userid = u.userid
> LEFT JOIN userpropertyvalues upv3
> ON up3.userpropertyid = upv3.userpropertyd
> AND upv3.userid = u.userid
> WHERE u.projectid = @.projectid
> AND up1.projectid = @.projectid
> AND up2.projectid = @.projectid
> AND up3.projectid = @.projectid

Flemming said that my query worked, which is sort of funny, because I
forgot there conditions in the WHERE clause, which I had intended to
read:

WHERE u.projectid = @.projectid
AND up1.projectid = @.projectid
AND up2.projectid = @.projectid
AND up3.projectid = @.projectid
AND up1.propertyname = 'propertyname1'
AND up2.propertyname = 'propertyname2'
AND up3.propertyname = 'propertyname3'

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

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

Help with query

I have a table in my database that has two fields. The registers on this
table look like the following:
Field 1 Field2
1 a
2 a
3 b
4 b
5 a
6 a
I would like to perform a query in the database in order to group sequences
of data, for the table above, I expect the following result:
Field1 Field2
1,2 a
3,4 b
5,6 a
I know I can group the data by using my Field2, but I do not know how I
would separate the different intervals.
Thank you in advance for your help.
Loureno.Hi, maybe this example helps you
/* BEGIN SCRIPT */
drop table tmp_group
go
drop table tmp_result
go
set nocount on
declare @.field1 int,
@.field2 char(1),
@.field2_aux char(1),
@.line varchar(50)
create table tmp_group(
field1 int,
field2 char(1) )
insert tmp_group values ( 1, 'a')
insert tmp_group values ( 2, 'a')
insert tmp_group values ( 3, 'b')
insert tmp_group values ( 4, 'b')
insert tmp_group values ( 5, 'a')
insert tmp_group values ( 6, 'a')
create table tmp_result(
line varchar(50),
field2 char(1) )
declare cu_group cursor for
select field1, field2 from tmp_group (nolock)
open cu_group
fetch next from cu_group into @.field1, @.field2
-- select @.@.fetch_status, @.field1, @.field2
if @.@.fetch_status = 0
begin
while @.@.fetch_status = 0
begin
set @.field2_aux = @.field2
set @.line = ''
while @.field2_aux = @.field2 and @.@.fetch_status = 0
begin
set @.line = @.line + convert(varchar(3), @.field1) + ','
fetch next from cu_group into @.field1, @.field2
-- select @.@.fetch_status, @.field1, @.field2
end
insert tmp_result values ( left(@.line, len(@.line) - 1), @.field2_aux )
end
end
close cu_group
deallocate cu_group
select * from tmp_result
select * from tmp_group
/* END SCRIPT */
"news.microsoft.com" wrote:

> I have a table in my database that has two fields. The registers on this
> table look like the following:
> Field 1 Field2
> 1 a
> 2 a
> 3 b
> 4 b
> 5 a
> 6 a
> I would like to perform a query in the database in order to group sequence
s
> of data, for the table above, I expect the following result:
> Field1 Field2
> 1,2 a
> 3,4 b
> 5,6 a
> I know I can group the data by using my Field2, but I do not know how I
> would separate the different intervals.
> Thank you in advance for your help.
> Louren?o.
>
>

Help with NOT EXISTS query

Newbie here...looking for help with a NOT EXISTS query or suggestions for a
better method.
I have 2 linked tables: CUSTOMERS and ADDRESSES
common fields are CUS_NO and ADR_CD
I need to find records where an address code (ADR_CD) entered into CUSTOMERS
does not have that same ADR_CD existing in the ADRESSES table.

Example:
CUS_NO = 12345
ADR_CD = Ohio01

If the combination of cus_no 12345 and Ohio01 does not exist in the
ADDRESSES table, I need to find them.

Thanks in advance.RDRaider wrote:
> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT <select list>
FROM Customers c
WHERE NOT EXISTS (SELECT *
FROM Addresses a
WHERE c.Cus_No = a.Cust_No
AND c.ADR_CD = a.ADR_CD)

Zach|||On Mon, 13 Dec 2004 20:17:45 GMT, RDRaider wrote:

> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
WHERE NOT EXISTS
(SELECT *
FROM ADDRESSES
WHERE Customers.cus_no = ADDRESSES.cus_no
AND Customers.adr_cd = Addresses.adr_cd )

Alternative method:

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
LEFT JOIN Addresses
ON Customers.cus_no = Addresses.cus_no
AND Customers.adr_cd = Addresses.adr_cd
WHERE Addresses.cus_no IS NULL|||Thanks for the quick reply. It works! I don't know why I couldn't get the
same results, need to hit the books I guess.

"nib" <individual_news@.nibsworld.com> wrote in message
news:326chiF3i7ns1U1@.individual.net...
> RDRaider wrote:
>> Newbie here...looking for help with a NOT EXISTS query or suggestions for
>> a better method.
>> I have 2 linked tables: CUSTOMERS and ADDRESSES
>> common fields are CUS_NO and ADR_CD
>> I need to find records where an address code (ADR_CD) entered into
>> CUSTOMERS does not have that same ADR_CD existing in the ADRESSES table.
>>
>> Example:
>> CUS_NO = 12345
>> ADR_CD = Ohio01
>>
>> If the combination of cus_no 12345 and Ohio01 does not exist in the
>> ADDRESSES table, I need to find them.
>>
>> Thanks in advance.
> SELECT <select list>
> FROM Customers c
> WHERE NOT EXISTS (SELECT *
> FROM Addresses a
> WHERE c.Cus_No = a.Cust_No
> AND c.ADR_CD = a.ADR_CD)
> Zach

Help with my SP please

Hey guys, I have a question.
I have a table with 2 decimal fields 10,2.
In my sp i get those fields and divid them by an number.
I get like 5 or 6 0's trailing the calculation.
Ex: 191.6/2 I get 95.800000
Why?
Here is my sp:
CREATE PROCEDURE sp_SummaryReport
(
@.startdate datetime,
@.enddate datetime
)

AS
BEGIN

SELECT tblLegendReportAbv.ReportType AS ReportType,
SUM(tblSummaryData.Volume) AS Volume,
SUM(tblSummaryData.NetEffect)/COUNT(tblSummaryData.DataID) AS NetEffect,
SUM(tblSummaryData.GrossEffect)/COUNT(tblSummaryData.DataID) AS GrossEffect
FROM tblSummaryData
INNER JOIN tblLegendReportAbv ON LTRIM(RTRIM(LOWER(tblSummaryData.ReportType))) = LTRIM(RTRIM(LOWER(tblLegendReportAbv.ReportAbv)))
WHERE tblSummaryData.WeekEndDate BETWEEN @.startdate AND @.enddate
GROUP BY tblSummaryData.ReportType,tblLegendReportAbv.Repor tType

END
GO

Please helpThat's beacause the implicit conversion made by SQL Server when dividing to different types. For more info see "Data Type Precedence" in your SQL help file.

But this only an aesthetic problem, it can be easily solved with an explicit conversion or with a cast like this:

cast(191.6/2 as decimal(10,2))

For more info on cast and convert see "CAST and CONVERT" in your SQL help file.

Best regards!|||Thank you.

Help with multiple IIFs, or need suggestion of better solution.

I am trying to check multiple fields from a db to see if they have either a 1 or 0 value, and if there is a 1, then write a value into a text box. I need to check multiple fields, and if all of them are checked then I have to insert the value for each into the text box. If it was just checking one condition it woudl be easy, because I could just nest IIF's until it was true.

So I can't do because once the truth clause is satisfied it will exit the loop: IIF(Fields!Fielda.Value = 1,"Fielda",IIF(Fields!Fieldb.Value=1,"Fieldb"....)

I also cannot do:
=IIfFields!Fielda.Value=1,"Fielda,"")
=IifFields!Fieldb.Value=1,"Fieldb,"")

Is there a way to have a whole bunch of IIF's, or can anyone think of another way to do this?

Much appreciated.

Use the "And" operator. It would look like this:

iif (Fields!Fielda.Value = 1 and Fields!Fieldb.Value=1 and Fields!Fieldc.Value=1, "Fielda", "")

|||Ryan, I appreciate the answer, but I think you misunderstood. I want it to say if Fielda = 1 then insert text, and if Fieldb = 1 then insert text, not if all of them = 1.

This would be the ideal situation:

=IIF(Fields!Fielda.Value=1,"Fielda","")
IIF(Fields!Fieldb.Value=1,"Fieldb","")
IIF(Fields!Fieldc.Value=1,"Fieldc","")
And so on for all the fields for this particular text box.

Or another example (that I've tried that did not work)
=IIF(Fields!Fielda.Value=1,"Fielda","") &
IIF(Fields!Fieldb.Value=1,"Fieldb","") &
IIF(Fields!Fieldc.Value=1,"Fieldc","") &

I can't use what you said because that would only evaluate one statement, and I need to evaluate 8 different statements. That's the problem. Is there a way to have mutliple seperate IIF's in an expression like I have above? If not, is there another solution?
|||

One question is what is the datatype on the database field? If it is boolean then you should be able to do:

=IIF(Fields!Fielda.Value,"Fielda","") + IIF(Fields!Fieldb.Value,"Fieldb","")...

The + should work for concatenation since all of the fields area string. Another thing that I have seen is that you may have to do CDec on the database fields to force a datatype match.

=IIF(CDec(Fields!Fielda.Value)=1,"Fielda","") + IIF(CDec(Fields!Fieldb.Value)=1,"Fieldb","") ...

|||

Use the Report Properties.Code.Custom Code feature.

1.Create a function in the CODE section

2. Pass all your field values to the function

3. The return value is used in the textbox.

You have a lot more coding power in the CODE section than you do with expressions.

Hope this helps.

|||

Can you do it in SQL using case statement ?

|||

=switch(Fields!FieldA.Value = 1, "A", Fields!FieldB.Value = 1, "B", true, "")

Thanks, Donovan.

Help with migrating jobs from SQL 2000 to SQL 2005

We need to migrate 500 jobs from SQL 2000 to SQL 2005 but we are getting
error hat one of the fields can’t be NULL. The field in question is OWNER_SID.
The question is how do we get the field from SQL 2000 ?
What we have try using Business Intelligent Studio to copy the jobs with out
success.
Jarek
You said that scripting out the jobs and the running them on SS2005 does
not work? In that case I think you are going to re-create them manually
"Jarek Gal" <JarekGal@.discussions.microsoft.com> wrote in message
news:DE8B79F8-FD96-4EDB-8BFE-742EC5379305@.microsoft.com...
> We need to migrate 500 jobs from SQL 2000 to SQL 2005 but we are getting
> error hat one of the fields cant be NULL. The field in question is
> OWNER_SID.
> The question is how do we get the field from SQL 2000 ?
> What we have try using Business Intelligent Studio to copy the jobs with
> out
> success.
>
|||Uri
Yes we try that and it's aking for OWNER_SID which it looks like was not in
the script.
Thanks
"Uri Dimant" wrote:

> Jarek
> You said that scripting out the jobs and the running them on SS2005 does
> not work? In that case I think you are going to re-create them manually
>
>
>
> "Jarek Gal" <JarekGal@.discussions.microsoft.com> wrote in message
> news:DE8B79F8-FD96-4EDB-8BFE-742EC5379305@.microsoft.com...
>
>

Help with migrating jobs from SQL 2000 to SQL 2005

We need to migrate 500 jobs from SQL 2000 to SQL 2005 but we are getting
error hat one of the fields can’t be NULL. The field in question is OWNER_
SID.
The question is how do we get the field from SQL 2000 ?
What we have try using Business Intelligent Studio to copy the jobs with out
success.Jarek
You said that scripting out the jobs and the running them on SS2005 does
not work? In that case I think you are going to re-create them manually
"Jarek Gal" <JarekGal@.discussions.microsoft.com> wrote in message
news:DE8B79F8-FD96-4EDB-8BFE-742EC5379305@.microsoft.com...
> We need to migrate 500 jobs from SQL 2000 to SQL 2005 but we are getting
> error hat one of the fields cant be NULL. The field in question is
> OWNER_SID.
> The question is how do we get the field from SQL 2000 ?
> What we have try using Business Intelligent Studio to copy the jobs with
> out
> success.
>|||Uri
Yes we try that and it's aking for OWNER_SID which it looks like was not in
the script.
Thanks
"Uri Dimant" wrote:

> Jarek
> You said that scripting out the jobs and the running them on SS2005 does
> not work? In that case I think you are going to re-create them manually
>
>
>
> "Jarek Gal" <JarekGal@.discussions.microsoft.com> wrote in message
> news:DE8B79F8-FD96-4EDB-8BFE-742EC5379305@.microsoft.com...
>
>

Help with large text fields please

I have the following problem (MS SQL 2000):
A modest table with several large fields (currently varchar(5000)), plus some datetime and integer fields recording who's done what and when.

Two problems - (1) I now realise that I'm limited to 8060 characters, and (2) users seem to think even 5000 chars might be too small on occasions (the table is for recording laboratory problems, so the amount of text depends on what the local quality manager finds!)

I thought I'd change my varchars to text. However, when I changed just one of them to text, the record set being returned by my stored procedure has lots of empty fields. The query ran OK before the datatype change and STILL runs OK in Enterprise Manager after the change.

The basic query is:

Code: ( text )

1. SELECT TechAnomalies.*,

2. lab1.LabRef AS labref1, lab1.LabName AS labname1, lab1.EULabRef AS EULabRef,

3. lab2.LabRef AS labref2, lab2.LabName AS labname2,

4. u1.UserFullName AS RaisedBy,

5. u2.UserFullName AS Inter,

6. u3.UserFullName AS SignOffBy,

7. u4.UserFullName AS LastEditor,

8. u5.UserFullName AS LQM

9. FROM dbo.TechAnomalies

10. INNER JOIN dbo.Labs AS lab1

11. ON lab1.LabID = TechAnomalies.TALabID

12. LEFT OUTER JOIN dbo.Labs AS lab2

13. ON lab2.LabID = TechAnomalies.TAIntermedLabID

14. LEFT OUTER JOIN dbo.Users AS u1

15. ON u1.UserID = TechAnomalies.TARaiserUserID

16. LEFT OUTER JOIN dbo.Users AS u2

17. ON u2.UserID = TechAnomalies.TAIntermedUserID

18. LEFT OUTER JOIN dbo.Users AS u3

19. ON u3.UserID = TechAnomalies.TASignedOffBy

20. LEFT OUTER JOIN dbo.Users AS u4

21. ON u4.UserID = TechAnomalies.TALastEditedBy

22. LEFT OUTER JOIN dbo.Users AS u5

23. ON u5.UserID = TechAnomalies.TALQMReviewBy

I use it with or without a WHERE clause (passed to the stored procedure as a varchar) to return either a recordset or the details of one record.

Any suggestions please?

You could try using nvarchar

Help with INSERT TRIGGER - fails with error.

I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.

Is there any alternative? I could write it in laborious code in the
application, but I'd rather not!

DDL for table and trigger below.

TIA

Edward

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblMyTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblMyTable]
GO

CREATE TABLE [dbo].[tblMyTable] (
[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable

FOR INSERT

AS

BEGIN

IF UPDATE(fldKBSubject)
BEGIN

UPDATE
tblMyTable
SET
fldSubject = i.fldKBSubject
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID

END

IF UPDATE (fldKBDescription)
BEGIN
UPDATE
tblMyTable
SET
fldDescription = i.fldKBDescription
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID
END
ENDOn 17 Mar 2006 06:24:01 -0800, teddysnips@.hotmail.com wrote:

>I need a trigger (well, I don't *need* one, but it would be optimal!)
>but I can't get it to work because it references ntext fields.
>Is there any alternative? I could write it in laborious code in the
>application, but I'd rather not!
>DDL for table and trigger below.

Hi Edward,

Thanks for providing the DDL!

I'll come to your problem later, but first some comments.

>CREATE TABLE [dbo].[tblMyTable] (
>[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
>[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO

You didn't declare any PRIMARY KEY in this database. I think you
intended to make the column fldCSID a PRIMARY KEY, but you didn't
declare it as such.

After that, you should also declare some other column (or combination of
columns) as UNIQUE. With this design, there's nothing to prevent you
from accidentally inserting the same data twice.

Does the fldSCID column really have to be uniqueidentifier? If you
choose to use surrogate keys, then IDENTITY should be the regular
choice; situations that call for uniqueidentifier are very rare.

Apart from the uniqueidentifier column, all your columns accept NULLs.
Do you really want to accept rows with just NULLs in your database?
Nullable columns should be the exception, not the rule.

Are you sure that all these columns need to be ntext? I can somewhat
imagine having descriptions of over 4,000 characters - but subjects? I
think that you should probably define Subject and KBSubject ar nvarchar
with an appropriate maximum length (hopefully less than 100, but I don;t
know your business of course). You might also want to rethiink the
choice of ntext/nvarchar over text/varchar - unless you really need to
store characters from non-Western alphabets or other characters that are
only available in unicode, there's no reason to use double the space
taken.

On to the trigger (I removed the empty lines for readability)

>CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable
>FOR INSERT
>AS
>BEGIN
>IF UPDATE(fldKBSubject)
>BEGIN
>UPDATE
>tblMyTable
>SET
>fldSubject = i.fldKBSubject
>FROM
>inserted i INNER JOIN
>tblMyTable ON i.fldCSID = tblMyTable.fldCSID
>END
>IF UPDATE (fldKBDescription)
>BEGIN
>UPDATE
>tblMyTable
>SET
>fldDescription = i.fldKBDescription
>FROM
>inserted i INNER JOIN
>tblMyTable ON i.fldCSID = tblMyTable.fldCSID
>END
>END

In an INSERT trigger, you don't need IF UPDATE(). It only makes sense in
an UPDATE trigger; for an INSERT, the IF UPDATE() will be true for each
column in the table.

There's also no need to use two seperate update statements. You can
combine these into one and gain some performance.

But the most important question, I think, is why you want to do this. If
the KBSubject and KBDescription are always a copy of the Subject and
Description columns, why have them?

Anyway, back to your question:

>I need a trigger (well, I don't *need* one, but it would be optimal!)
>but I can't get it to work because it references ntext fields.

You can't reference ntext columns in the inserted column. But you can
join to the base table and get the data from there. (Or you could
convert the trigger to an instead of trigger, in which case the ntext
data *WILL* be available in the inserted table - but that's not the
easiest solution in this case).

CREATE TRIGGER PrepopulateKBFieldsFromQuery
ON dbo.tblMyTable
FOR INSERT
AS
UPDATE MyTable
SET Subject = KBSubject,
Description = KBDescription
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.CSID = MyTable.CSID)
go

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> After that, you should also declare some other column (or combination of
> columns) as UNIQUE. With this design, there's nothing to prevent you
> from accidentally inserting the same data twice.

I would guess that one of the subjects are intended to be a key of some
sort, but since it's probably a free-text column, a PK/UNIQUE constraint
only gives you half protection, as it will not catch variations due to
typos and spaces.

> Does the fldSCID column really have to be uniqueidentifier? If you
> choose to use surrogate keys, then IDENTITY should be the regular
> choice; situations that call for uniqueidentifier are very rare.

Unless you are into replication. GUIDs are also popular among web
programmers, because they can save a roundtrip to get the key value.
I've seen more than one URL with GUIDs in them.

> You might also want to rethiink the choice of ntext/nvarchar over
> text/varchar - unless you really need to store characters from
> non-Western alphabets or other characters that are only available in
> unicode, there's no reason to use double the space taken.

Not sure I agree. The cost for a change when a requirement to support,
say, Japanese, comes can prove to be prohibitive.

> But the most important question, I think, is why you want to do this. If
> the KBSubject and KBDescription are always a copy of the Subject and
> Description columns, why have them?

The trigger name says "prepopulate". I guess Edward is setting an initial
default.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hugo Kornelis wrote:
> On 17 Mar 2006 06:24:01 -0800, teddysnips@.hotmail.com wrote:
[...]
> >CREATE TABLE [dbo].[tblMyTable] (
> >[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
> >[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >GO
> You didn't declare any PRIMARY KEY in this database. I think you
> intended to make the column fldCSID a PRIMARY KEY, but you didn't
> declare it as such.

Weird, it IS the PK! An error with the script, or maybe I was a bit
eager on the editing.

> Does the fldSCID column really have to be uniqueidentifier? If you
> choose to use surrogate keys, then IDENTITY should be the regular
> choice; situations that call for uniqueidentifier are very rare.

At one time the database was replicated.

> Apart from the uniqueidentifier column, all your columns accept NULLs.
> Do you really want to accept rows with just NULLs in your database?
> Nullable columns should be the exception, not the rule.

Couldn't agree more - not my DB design!

> Are you sure that all these columns need to be ntext? I can somewhat
> imagine having descriptions of over 4,000 characters - but subjects? I
> think that you should probably define Subject and KBSubject ar nvarchar
> with an appropriate maximum length (hopefully less than 100, but I don;t
> know your business of course). You might also want to rethiink the
> choice of ntext/nvarchar over text/varchar - unless you really need to
> store characters from non-Western alphabets or other characters that are
> only available in unicode, there's no reason to use double the space
> taken.

The ntext come from the Access upsizing wizard. The original designer
simply left the default values (it would have been memo columns in
Access)

> On to the trigger (I removed the empty lines for readability)
> >CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable
> >FOR INSERT
> >AS
> >BEGIN
> >IF UPDATE(fldKBSubject)
> >BEGIN
> >UPDATE
> >tblMyTable
> >SET
> >fldSubject = i.fldKBSubject
> >FROM
> >inserted i INNER JOIN
> >tblMyTable ON i.fldCSID = tblMyTable.fldCSID
> >END
> >IF UPDATE (fldKBDescription)
> >BEGIN
> >UPDATE
> >tblMyTable
> >SET
> >fldDescription = i.fldKBDescription
> >FROM
> >inserted i INNER JOIN
> >tblMyTable ON i.fldCSID = tblMyTable.fldCSID
> >END
> >END
> In an INSERT trigger, you don't need IF UPDATE(). It only makes sense in
> an UPDATE trigger; for an INSERT, the IF UPDATE() will be true for each
> column in the table.

Yes, I realise that now - thanks.

> There's also no need to use two seperate update statements. You can
> combine these into one and gain some performance.

I tend to be very "belt and braces" with my code.

> But the most important question, I think, is why you want to do this. If
> the KBSubject and KBDescription are always a copy of the Subject and
> Description columns, why have them?

I want to do it because the underlying application is a query system.
Some queries will form part of a Knowledge Base system. The client
wants the (QUERY)Subject and Description columns to be mirrored by the
KBSubject and KBDescription fields, at least initially. Only the KB
versions will be exposed to the customer.

> Anyway, back to your question:
> >I need a trigger (well, I don't *need* one, but it would be optimal!)
> >but I can't get it to work because it references ntext fields.
> You can't reference ntext columns in the inserted column. But you can
> join to the base table and get the data from there. (Or you could
> convert the trigger to an instead of trigger, in which case the ntext
> data *WILL* be available in the inserted table - but that's not the
> easiest solution in this case).
> CREATE TRIGGER PrepopulateKBFieldsFromQuery
> ON dbo.tblMyTable
> FOR INSERT
> AS
> UPDATE MyTable
> SET Subject = KBSubject,
> Description = KBDescription
> WHERE EXISTS
> (SELECT *
> FROM inserted AS i
> WHERE i.CSID = MyTable.CSID)
> go

And that is absolutely spot on! Many thanks.

Edward