Multiple Joins - Need Help

have the following code for ONE Inner Join, but I want to add another join for another Table and Fields... can you help me with the syntax:

SELECT DISTINCT

CTR.ReqID, CTR.SpecimenID, CTR.LabID, CTR.ProcedureID, CTR.TestID, CTR.Isolate, CTR.Problem,

CTR.ProblemComments, CP.ProcedureID, CP.Description AS CPProcedureDescription

FROM ClinicalTestsRequested CTR inner join ClinicalProcedures CP

ON CTR.ProcedureID=CP.ProcedureID

WHERE (CTR.SpecimenID = @.Accession)

I want to add another Join to the above where:

Table = ClinicalTests CT
Fields = CT.TestID, CT.Description AS CTTestDescription

and Compare = CTR.TestID to CT.TestID

Thanks !!

after your on clause for the first inner join add:

inner join clinicalTests ct on ctr.testid = ct.testid

add your columns to the list in the select.

Better yet, use sql server management studio express to design the query for you (it doensn't matter if your database is sql2005 or 2000). Create a new query for one of your tables and then use the query designer. This will allow you to drag and drop joins, make them into outer joins, add columns, etc, using a design tool.

--JJ

Multiple insert

Hi, I'm trying to insert multiple rows with the following statment and
am getting a syntax error on line 3. The datatypes are varchars except
for status which is numeric.

insert S (S#, SNAME, STATUS, CITY)
values
('S2', 'Jones', 10, 'Paris'),
('S3', 'Blake', 30, 'Paris'),
('S4', 'Clark', 20, 'London'),
('S5', 'Adams', 30, 'Athens');

What's wrong?
Thanks,
SashiYou have to use the INSERT... SELECT form to insert multiple rows.

INSERT INTO S (s#, sname, status, city)
SELECT 'S2', 'Jones', 10, 'Paris' UNION ALL
SELECT 'S3', 'Blake', 30, 'Paris' UNION ALL
SELECT 'S4', 'Clark', 20, 'London' UNION ALL
SELECT 'S5', 'Adams', 30, 'Athens' ;

--
David Portas
SQL Server MVP
--|||The real problem is that SQL Server does not yet support SQL-92 syntax,
in spite of having the power to do so. Your choices are:

1) use a series of INSERT INTO statements (notice that INSERT is a
proprietary shorthand, not Standard SQL).

2) use a proprietary SELECT ..UNION ALL chain to build a table the same
way that the VALUES table constructor would.

Multiple IF statements

I am getting syntax errors on the following SQL. Can anyone help? Thanks.
IF COALESCE(dbo.People.Address1,'') = ''
BEGIN
FullAddress = COALESCE(dbo.People.Address2,'') + Chr(13) +
COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
ELSE
IF COALESCE(dbo.People.Address2,'') = ''
BEGIN
FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
ELSE
BEGIN
FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo.People.City,'') +
N', ' + COALESCE(dbo.People.State,'') + N' ' +
COALESCE(dbo.People.ZipCode,'')
END,
DavidThere's no context for the IF statement. It refers to dbo.People.Address1, b
ut with not SELECT
statement. For which row do you want to perform these operations?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David C" <dlchase@.lifetimeinc.com> wrote in message news:eQ4GrdEGFHA.3336@.TK2MSFTNGP10.phx
.gbl...
>I am getting syntax errors on the following SQL. Can anyone help? Thanks.
> IF COALESCE(dbo.People.Address1,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address2,'') + Chr(13) + COALESCE(dbo
.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' ' + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> IF COALESCE(dbo.People.Address2,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo
.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' ' + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo
.People.Address1,'') +
> Chr(13) + COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State
,'') + N' ' +
> COALESCE(dbo.People.ZipCode,'')
> END,
> David
>|||David C wrote:
> I am getting syntax errors on the following SQL. Can anyone help? Thanks
.
> IF COALESCE(dbo.People.Address1,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address2,'') + Chr(13) +
> COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N'
'
> + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> IF COALESCE(dbo.People.Address2,'') = ''
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
> COALESCE(dbo.People.City,'') + N', ' + COALESCE(dbo.People.State,'') + N'
'
> + COALESCE(dbo.People.ZipCode,'')
> END
> ELSE
> BEGIN
> FullAddress = COALESCE(dbo.People.Address1,'') + Chr(13) +
> COALESCE(dbo.People.Address1,'') + Chr(13) + COALESCE(dbo.People.City,'')
+
> N', ' + COALESCE(dbo.People.State,'') + N' ' +
> COALESCE(dbo.People.ZipCode,'')
> END,
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
This is in the SELECT clause of a query? In that case use a CASE
instead of the If..Else:
CASE WHEN COALESCE(People.Address1,'') = ''
THEN FullAddress = COALESCE(People.Address2,'') + Chr(13)
+ COALESCE(People.City,'') + ', '
+ COALESCE(People.State,'') + ' '
+ COALESCE(People.ZipCode,'')
WHEN COALESCE(People.Address2,'') = ''
THEN FullAddress = COALESCE(People.Address1,'') + Chr(13)
+ COALESCE(People.City,'') + ', '
+ COALESCE(People.State,'') + ' '
+ COALESCE(People.ZipCode,'')
ELSE FullAddress = COALESCE(People.Address1,'') + Chr(13)
+ COALESCE(People.Address2,'') + Chr(13)
+ COALESCE(People.City,'') + ', '
+ COALESCE(People.State,'') + ' '
+ COALESCE(People.ZipCode,'')
END,
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQho7HIechKqOuFEgEQKg9wCfe3HzCvdQbhvK
e0SY8QfpdGvXj10AoKzV
QM1G41rQNsUIxqDkCYbAxIza
=J+7B
--END PGP SIGNATURE--|||I just wanted the IF statement to refer to a returned field named
FullAddress.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Your code failed but code below worked:
FullAddress = CASE WHEN COALESCE(dbo.People.Address1,'') = ''
THEN COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
WHEN COALESCE(dbo.People.Address2,'') = ''
THEN COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
ELSE COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||David wrote:

> FullAddress = CASE WHEN COALESCE(dbo.People.Address1,'') = ''
> THEN COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> WHEN COALESCE(dbo.People.Address2,'') = ''
> THEN COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> ELSE COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> END
or simply
FullAddress =
COALESCE(dbo.People.Address1 + Char(13) + Char(10),'')
+ COALESCE(dbo.People.Address2 + Char(13) + Char(10),'')
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
END
Dieter|||On Mon, 21 Feb 2005 13:00:35 -0800, David wrote:

>Your code failed but code below worked:
>FullAddress = CASE WHEN COALESCE(dbo.People.Address1,'') = ''
> THEN COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> WHEN COALESCE(dbo.People.Address2,'') = ''
> THEN COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
> ELSE COALESCE(dbo.People.Address1,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.Address2,'') + Char(13) + Char(10)
> + COALESCE(dbo.People.City,'') + N', '
> + COALESCE(dbo.People.State,'') + N' '
> + COALESCE(dbo.People.ZipCode,'')
>END
Hi David,
You can simplify this:
FullAddress = COALESCE(dbo.People.Address1 + Char(13) + Char(10), '')
+ COALESCE(dbo.People.Address2 + Char(13) + Char(10), '')
+ COALESCE(dbo.People.City,'') + N', '
+ COALESCE(dbo.People.State,'') + N' '
+ COALESCE(dbo.People.ZipCode,'')
By the way: if city is NULL, the last line will look like this:
", IL 12345"
If State is NULL, the last line will look like this:
"Smallville, 12345" (note the two spaces)
I'm not sure if that is really what you intend...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I don't think the single line will work because it will always bring
back something from 1st 2 lines and I don't want that. I think your
examples will always bring back Char(13) + Char(10) if either Address1
or Address2 is Null.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||On Mon, 21 Feb 2005 14:41:58 -0800, David wrote:

>I don't think the single line will work because it will always bring
>back something from 1st 2 lines and I don't want that. I think your
>examples will always bring back Char(13) + Char(10) if either Address1
>or Address2 is Null.
Hi David,
No, it won't (unless you have changed your settings to non-ANSI-standard
NULL handling). Did you try it?
There's a big difference between
(a) COALESCE (columnname + char(13) + char(10), '')
and
(b) COALESCE (columnname, '') + char(13) + char(10)
In (a), the CrLf (Carriage Return [char(13)] + Line Feed [char(10)]) will
be concatenated to the column's value first, then the result is checked
against NULL and if it is, it's replaced by an empty string. Since
concatenation of CrLf to a NULL string results in a NULL string, the end
result of (a) will be the empty string if the column holds a NULL.
In (b), the column's value is first checked against NULL and replaced by
the empty string, then CrLf gets added. Concatenation of CrLf to the empty
string will result in a string holding just CrLf.
This being said, I must add that there will be a difference if your data
actually holds rows where Address1 or Address2 is filled with an empty
string. If that's the case, I'd strongly suggest you to change that - you
should represent unknown or missing data in one consistent way, not mix up
various ways. Decide to use either the empty string or NULL if an address
line is missing, then clean up data and introduce either a NOT NULL
constraint or a CHECK (Addres1 <> '') constraint to prevent future entry
of malformed data.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Multiple Files

I have a job with a single step that executes a stored procedure that performs the following steps:

1. Checks for the existance of a file A in a folder A

2. If it exists,

a. executes the cmdshell to run a DTS package to drop a table, recreate it and load the data in the file A to table X

b. runs other stored procedures that use the data in table X to create other tables Y and Z

c. executes the cndshell to remove and rename the file A from Folder A into Folder B

What I'd like to do is use this same stored procedure if possible, but create a job or another store procedure that would loop thru and process multiple files in Folder A instead of just one.

Any suggestions would be greatly appreciated

My post in this thread should point you in a workable direction.

Multiple exists not functioning as expected?

I have the following query.
declare @.p0 nvarchar(6)
declare @.p1 nvarchar(2)
set @.p0 = 'Trish%'
set @.p1 = 'SH'
SELECT [t1].[DocumentId], [t1].[Version], [t1].[ClosedDate] AS
[ClosedDateUTC], [t1].[DueDate] AS [DueDateUTC], [t1].[DueDateLatest]
AS [DueDateLatestUTC], [t1].[PriceListId], [t1].[SoldToId], [t1].
[TaxRate], [t1].[IsLocked], [t1].[ShippingCost], [t1].
[DocumentNumber], [t1].[RevisionNumber], [t1].[CreatedDate] AS
[CreatedDateUTC], [t1].[CreatedById], [t1].[ModifiedDate] AS
[ModifiedDateUTC], [t1].[ModifiedById], [t1].[TermsId], [t1].
[ShipMethodId], [t1].[FOBId], [t1].[DocumentStatusId], [t1].
[DocumentType], [t1].[CustPO], [t1].[DeliveryWindowId], [t1].
[CategoryId], [t1].[SubCategoryId], [t1].[ShipDate] AS [ShipDateUTC],
[t1].[value] AS [Subtotal], [t1].[value2] AS [Tax]
FROM (
SELECT [t0].[DocumentId], [t0].[Version], [t0].[ClosedDate], [t0].
[DueDate], [t0].[DueDateLatest], [t0].[PriceListId], [t0].[SoldToId],
[t0].[TaxRate], [t0].[IsLocked], [t0].[ShippingCost], [t0].
[DocumentNumber], [t0].[RevisionNumber], [t0].[CreatedDate], [t0].
[CreatedById], [t0].[ModifiedDate], [t0].[ModifiedById], [t0].
[TermsId], [t0].[ShipMethodId], [t0].[FOBId], [t0].[DocumentStatusId],
[t0].[DocumentType], [t0].[CustPO], [t0].[DeliveryWindowId], [t0].
[CategoryId], [t0].[SubCategoryId], [t0].[ShipDate],
CONVERT(Decimal(29,4),[dbo].[GetDocumentSubTotal]([t0].[DocumentId]))
AS [value], CONVERT(Decimal(29,4),[dbo].[GetDocumentTaxTotal]([t0].
[DocumentId])) AS [value2]
FROM [dbo].[vDocument] AS [t0]
) AS [t1]
WHERE (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[vDocumentContact] AS [t2]
WHERE ([t2].[FirstName] LIKE @.p0) AND ([t2].[DocumentId] = [t1].
[DocumentId])
)) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[vDocumentContact] AS [t3]
WHERE ([t3].[ContactType] = @.p1) AND ([t3].[DocumentId] = [t1].
[DocumentId])
))
-- @.p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Trish%]
-- @.p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [SH]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.21022.8
The problem is that this query returns rows, and it doesn't seem that
it should (there are no DocumentContact rows that have a FirstName
like Trish and ContactType = 'SH').
I believe it's because the two Exists in the where clause.. I would
think that rows have to be returned from BOTH Exists clauses for any
rows to be returned... but it seems that if EITHER Exists returns
rows, the entire query returns rows.
Am I not understanding how EXISTS works?
Thanks
Andy
If I understand correctly, you only want to return the rows where at least
one row exists in vDocumentContact for that DocumentId where that one row
has both a FirstName like Trish and ContactType = 'SH'. Your query does not
do that. It returns the rows where at least one row exists in
vDocumentContact for that DocumentId which has a FirstName like Trish and at
least one row exists in vDocumentContact for that DocumentId which has
aContactType = 'SH', but, as you have written the query, they don't have to
be the same row. For example, suppose I have a table of orders,
Create Table #Orders (OrderID int, CustomerID int);
Insert #Orders (OrderID, CustomerID)
Select 1, 1
Union All Select 2, 20
Union All Select 3, 30;
and a table of order lines,
Create Table #OrderLines (OrderID int, ProductID int, Quantity int);
Insert #OrderLines (OrderID, ProductID, Quantity)
Select 1, 1, 5
Union All Select 1, 2, 20
Union All Select 2, 1, 15
Union All Select 2, 3, 10
Union All Select 1, 1, 3;
If I am looking for orders which have ordered more than 10 of ProductID 1,
the following query does not do what I want,
Select o.OrderID, CustomerID
From #Orders o
Where Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ProductID = 1)
And Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ol.Quantity > 10);
That query returns both Order 1 and Order 2 because they both meet the
condition that there is a row in #Orderlines with ProductID 1 and there is a
row in #Orderlines with Quantity > 10. But for Order 1, these aren't the
same row, so I don't want that order returned. Instead, the query should be
written as
Select o.OrderID, CustomerID
From #Orders o
Where Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ProductID = 1
And ol.Quantity > 10);
Which returns the desired result (only Order 2).
So if I understand what you want, you need to replace your two WHERE EXISTS
with one WHERE EXISTS which checks both conditions.
If that is not what you meant, please post sample tables and data and a
description of the results you want. See www.aspfaq.com/5006 for how to do
this or do something like I did above.
Tom
"Andy" <andyj@.med-associates.com> wrote in message
news:12be336f-baee-4501-90db-e1a05226fa9a@.d62g2000hsf.googlegroups.com...
>I have the following query.
> declare @.p0 nvarchar(6)
> declare @.p1 nvarchar(2)
> set @.p0 = 'Trish%'
> set @.p1 = 'SH'
> SELECT [t1].[DocumentId], [t1].[Version], [t1].[ClosedDate] AS
> [ClosedDateUTC], [t1].[DueDate] AS [DueDateUTC], [t1].[DueDateLatest]
> AS [DueDateLatestUTC], [t1].[PriceListId], [t1].[SoldToId], [t1].
> [TaxRate], [t1].[IsLocked], [t1].[ShippingCost], [t1].
> [DocumentNumber], [t1].[RevisionNumber], [t1].[CreatedDate] AS
> [CreatedDateUTC], [t1].[CreatedById], [t1].[ModifiedDate] AS
> [ModifiedDateUTC], [t1].[ModifiedById], [t1].[TermsId], [t1].
> [ShipMethodId], [t1].[FOBId], [t1].[DocumentStatusId], [t1].
> [DocumentType], [t1].[CustPO], [t1].[DeliveryWindowId], [t1].
> [CategoryId], [t1].[SubCategoryId], [t1].[ShipDate] AS [ShipDateUTC],
> [t1].[value] AS [Subtotal], [t1].[value2] AS [Tax]
> FROM (
> SELECT [t0].[DocumentId], [t0].[Version], [t0].[ClosedDate], [t0].
> [DueDate], [t0].[DueDateLatest], [t0].[PriceListId], [t0].[SoldToId],
> [t0].[TaxRate], [t0].[IsLocked], [t0].[ShippingCost], [t0].
> [DocumentNumber], [t0].[RevisionNumber], [t0].[CreatedDate], [t0].
> [CreatedById], [t0].[ModifiedDate], [t0].[ModifiedById], [t0].
> [TermsId], [t0].[ShipMethodId], [t0].[FOBId], [t0].[DocumentStatusId],
> [t0].[DocumentType], [t0].[CustPO], [t0].[DeliveryWindowId], [t0].
> [CategoryId], [t0].[SubCategoryId], [t0].[ShipDate],
> CONVERT(Decimal(29,4),[dbo].[GetDocumentSubTotal]([t0].[DocumentId]))
> AS [value], CONVERT(Decimal(29,4),[dbo].[GetDocumentTaxTotal]([t0].
> [DocumentId])) AS [value2]
> FROM [dbo].[vDocument] AS [t0]
> ) AS [t1]
> WHERE (EXISTS(
> SELECT NULL AS [EMPTY]
> FROM [dbo].[vDocumentContact] AS [t2]
> WHERE ([t2].[FirstName] LIKE @.p0) AND ([t2].[DocumentId] = [t1].
> [DocumentId])
> )) AND (EXISTS(
> SELECT NULL AS [EMPTY]
> FROM [dbo].[vDocumentContact] AS [t3]
> WHERE ([t3].[ContactType] = @.p1) AND ([t3].[DocumentId] = [t1].
> [DocumentId])
> ))
> -- @.p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Trish%]
> -- @.p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [SH]
> -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
> 3.5.21022.8
> The problem is that this query returns rows, and it doesn't seem that
> it should (there are no DocumentContact rows that have a FirstName
> like Trish and ContactType = 'SH').
> I believe it's because the two Exists in the where clause.. I would
> think that rows have to be returned from BOTH Exists clauses for any
> rows to be returned... but it seems that if EITHER Exists returns
> rows, the entire query returns rows.
> Am I not understanding how EXISTS works?
> Thanks
> Andy

Multiple exists not functioning as expected?

I have the following query.
declare @.p0 nvarchar(6)
declare @.p1 nvarchar(2)
set @.p0 = 'Trish%'
set @.p1 = 'SH'
SELECT [t1].[DocumentId], [t1].[Version], [t1].[ClosedDate] AS
[ClosedDateUTC], [t1].[DueDate] AS [DueDateUTC], [t1].[DueDateLatest]
AS [DueDateLatestUTC], [t1].[PriceListId], [t1].[SoldToId], [t1].
[TaxRate], [t1].[IsLocked], [t1].[ShippingCost], [t1].
[DocumentNumber], [t1].[RevisionNumber], [t1].[CreatedDate] AS
[CreatedDateUTC], [t1].[CreatedById], [t1].[ModifiedDate] AS
[ModifiedDateUTC], [t1].[ModifiedById], [t1].[TermsId], [t1].
[ShipMethodId], [t1].[FOBId], [t1].[DocumentStatusId], [t1].
[DocumentType], [t1].[CustPO], [t1].[DeliveryWindowId], [t1].
[CategoryId], [t1].[SubCategoryId], [t1].[ShipDate] AS [ShipDateUTC],
[t1].[value] AS [Subtotal], [t1].[value2] AS [Tax]
FROM (
SELECT [t0].[DocumentId], [t0].[Version], [t0].[ClosedDate], [t0].
[DueDate], [t0].[DueDateLatest], [t0].[PriceListId], [t0].[SoldToId],
[t0].[TaxRate], [t0].[IsLocked], [t0].[ShippingCost], [t0].
[DocumentNumber], [t0].[RevisionNumber], [t0].[CreatedDate], [t0].
[CreatedById], [t0].[ModifiedDate], [t0].[ModifiedById], [t0].
[TermsId], [t0].[ShipMethodId], [t0].[FOBId], [t0].[DocumentStatusId],
[t0].[DocumentType], [t0].[CustPO], [t0].[DeliveryWindowId], [t0].
[CategoryId], [t0].[SubCategoryId], [t0].[ShipDate],
CONVERT(Decimal(29,4),[dbo].[GetDocumentSubTotal]([t0].[DocumentId]))
AS [value], CONVERT(Decimal(29,4),[dbo].[GetDocumentTaxTotal]([t0].
[DocumentId])) AS [value2]
FROM [dbo].[vDocument] AS [t0]
) AS [t1]
WHERE (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[vDocumentContact] AS [t2]
WHERE ([t2].[FirstName] LIKE @.p0) AND ([t2].[DocumentId] = [t1].
[DocumentId])
)) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[vDocumentContact] AS [t3]
WHERE ([t3].[ContactType] = @.p1) AND ([t3].[DocumentId] = [t1].
[DocumentId])
))
-- @.p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Trish%]
-- @.p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [SH]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.21022.8
The problem is that this query returns rows, and it doesn't seem that
it should (there are no DocumentContact rows that have a FirstName
like Trish and ContactType = 'SH').
I believe it's because the two Exists in the where clause.. I would
think that rows have to be returned from BOTH Exists clauses for any
rows to be returned... but it seems that if EITHER Exists returns
rows, the entire query returns rows.
Am I not understanding how EXISTS works?
Thanks
AndyIf I understand correctly, you only want to return the rows where at least
one row exists in vDocumentContact for that DocumentId where that one row
has both a FirstName like Trish and ContactType = 'SH'. Your query does not
do that. It returns the rows where at least one row exists in
vDocumentContact for that DocumentId which has a FirstName like Trish and at
least one row exists in vDocumentContact for that DocumentId which has
aContactType = 'SH', but, as you have written the query, they don't have to
be the same row. For example, suppose I have a table of orders,
Create Table #Orders (OrderID int, CustomerID int);
Insert #Orders (OrderID, CustomerID)
Select 1, 1
Union All Select 2, 20
Union All Select 3, 30;
and a table of order lines,
Create Table #OrderLines (OrderID int, ProductID int, Quantity int);
Insert #OrderLines (OrderID, ProductID, Quantity)
Select 1, 1, 5
Union All Select 1, 2, 20
Union All Select 2, 1, 15
Union All Select 2, 3, 10
Union All Select 1, 1, 3;
If I am looking for orders which have ordered more than 10 of ProductID 1,
the following query does not do what I want,
Select o.OrderID, CustomerID
From #Orders o
Where Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ProductID = 1)
And Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ol.Quantity > 10);
That query returns both Order 1 and Order 2 because they both meet the
condition that there is a row in #Orderlines with ProductID 1 and there is a
row in #Orderlines with Quantity > 10. But for Order 1, these aren't the
same row, so I don't want that order returned. Instead, the query should be
written as
Select o.OrderID, CustomerID
From #Orders o
Where Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ProductID = 1
And ol.Quantity > 10);
Which returns the desired result (only Order 2).
So if I understand what you want, you need to replace your two WHERE EXISTS
with one WHERE EXISTS which checks both conditions.
If that is not what you meant, please post sample tables and data and a
description of the results you want. See www.aspfaq.com/5006 for how to do
this or do something like I did above.
Tom
"Andy" <andyj@.med-associates.com> wrote in message
news:12be336f-baee-4501-90db-e1a05226fa9a@.d62g2000hsf.googlegroups.com...
>I have the following query.
> declare @.p0 nvarchar(6)
> declare @.p1 nvarchar(2)
> set @.p0 = 'Trish%'
> set @.p1 = 'SH'
> SELECT [t1].[DocumentId], [t1].[Version], [t1].[ClosedDate] AS
> [ClosedDateUTC], [t1].[DueDate] AS [DueDateUTC], [t1].[DueDateLatest]
> AS [DueDateLatestUTC], [t1].[PriceListId], [t1].[SoldToId], [t1].
> [TaxRate], [t1].[IsLocked], [t1].[ShippingCost], [t1].
> [DocumentNumber], [t1].[RevisionNumber], [t1].[CreatedDate] AS
> [CreatedDateUTC], [t1].[CreatedById], [t1].[ModifiedDate] AS
> [ModifiedDateUTC], [t1].[ModifiedById], [t1].[TermsId], [t1].
> [ShipMethodId], [t1].[FOBId], [t1].[DocumentStatusId], [t1].
> [DocumentType], [t1].[CustPO], [t1].[DeliveryWindowId], [t1].
> [CategoryId], [t1].[SubCategoryId], [t1].[ShipDate] AS [ShipDateUTC],
> [t1].[value] AS [Subtotal], [t1].[value2] AS [Tax]
> FROM (
> SELECT [t0].[DocumentId], [t0].[Version], [t0].[ClosedDate], [t0].
> [DueDate], [t0].[DueDateLatest], [t0].[PriceListId], [t0].[SoldToId],
> [t0].[TaxRate], [t0].[IsLocked], [t0].[ShippingCost], [t0].
> [DocumentNumber], [t0].[RevisionNumber], [t0].[CreatedDate], [t0].
> [CreatedById], [t0].[ModifiedDate], [t0].[ModifiedById], [t0].
> [TermsId], [t0].[ShipMethodId], [t0].[FOBId], [t0].[DocumentStatusId],
> [t0].[DocumentType], [t0].[CustPO], [t0].[DeliveryWindowId], [t0].
> [CategoryId], [t0].[SubCategoryId], [t0].[ShipDate],
> CONVERT(Decimal(29,4),[dbo].[GetDocumentSubTotal]([t0].[DocumentId]))
> AS [value], CONVERT(Decimal(29,4),[dbo].[GetDocumentTaxTotal]([t0].
> [DocumentId])) AS [value2]
> FROM [dbo].[vDocument] AS [t0]
> ) AS [t1]
> WHERE (EXISTS(
> SELECT NULL AS [EMPTY]
> FROM [dbo].[vDocumentContact] AS [t2]
> WHERE ([t2].[FirstName] LIKE @.p0) AND ([t2].[DocumentId] = [t1].
> [DocumentId])
> )) AND (EXISTS(
> SELECT NULL AS [EMPTY]
> FROM [dbo].[vDocumentContact] AS [t3]
> WHERE ([t3].[ContactType] = @.p1) AND ([t3].[DocumentId] = [t1].
> [DocumentId])
> ))
> -- @.p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Trish%]
> -- @.p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [SH]
> -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
> 3.5.21022.8
> The problem is that this query returns rows, and it doesn't seem that
> it should (there are no DocumentContact rows that have a FirstName
> like Trish and ContactType = 'SH').
> I believe it's because the two Exists in the where clause.. I would
> think that rows have to be returned from BOTH Exists clauses for any
> rows to be returned... but it seems that if EITHER Exists returns
> rows, the entire query returns rows.
> Am I not understanding how EXISTS works?
> Thanks
> Andysql

multiple data controls in gridview

one of my webpages uses the following sql query to allow the user to search through the database and present the qualifying data in gridview:

SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + ? + '%')

how could i expand this so that the user can also search through the database but instead by searching through another column such as [type]?

thanks in advance

SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + ? + '%') OR ([Type] LIKE '%' + ? + '%')

|||

yeah i tried that but it didnt work...i'll have another go though...

p.s. speedy reply! cheers

|||

yeah again it didnt work i got this stack trace error which i dont have a clue what it means...

[OleDbException (0x80040e07): Data type mismatch in criteria expression.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +91
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1657
System.Web.UI.WebControls.AccessDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +58
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +13
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68
System.Web.UI.WebControls.GridView.DataBind() +5
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67
System.Web.UI.Control.EnsureChildControls() +97
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5731

|||

paste your aspx code also

|||

You should give your parameters names rather than using ?'s as placeholders. The problem is with the new query, it is expecting TWO parameters, one for each ? instead of one parameter used twice.

|||

for some reason it double posted so this post was the same as below but now its this until i find out how to delete my own post if its possible

|||

Motley: yeah dont worry i have always dont that, it would make no sense to keep on using ?s

<%@.PageLanguage="VB"AutoEventWireup="false"CodeFile="Login.aspx.vb"Inherits="Default2" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml"xmlns:spry="http://ns.adobe.com/spry">

<headid="Head1"runat="server">

<metahttp-equiv="Content-Type"content="text/html; charset=iso-8859-1"/>

<scriptsrc="SpryMenuBar.js"type="text/javascript"></script> <linkhref="SpryMenuBarHorizontal.css"rel="stylesheet"type="text/css"/>

<linkhref="screen.css"rel="stylesheet"type="text/css"/>

<title>View Products</title>

</head>

<body>

<ulid="MenuBar1"class="MenuBarHorizontal">

<li><ahref="Default.aspx">Home</a></li>

<li><ahref="Gallery.aspx">Gallery</a></li>

<li><ahref="Database.aspx">Database</a></li>

<li><ahref="Contact us.aspx">Contact us</a></li>

<li><ahref="Login.aspx">Login</a></li> </ul>

<scripttype="text/javascript">

<!--

var MenuBar1 =new Spry.Widget.MenuBar("MenuBar1", {imgDown:"SpryMenuBarDownHover.gif", imgRight:"SpryMenuBarRightHover.gif"});

var MenuBar2 =new Spry.Widget.MenuBar("MenuBar2", {imgRight:"SpryMenuBarRightHover.gif"});

//-->

</script>

<br/>

<br/>

<br/>

<br/>

<formid="form1"runat="server">

<div>

<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Text="Search"/><br/>

<asp:AccessDataSourceID="AccessDataSource1"runat="server"DataFile="~/App_Data/brakes.mdb"

SelectCommand="SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + ? + '%')">

<SelectParameters>

<asp:ControlParameterControlID="TextBox1"DefaultValue="%"Name="?"PropertyName="Text"/>

</SelectParameters>

</asp:AccessDataSource>

<asp:GridViewID="GridView1"runat="server"AllowPaging="True"AllowSorting="True"

AutoGenerateColumns="False"CellPadding="4"DataKeyNames="product code"DataSourceID="AccessDataSource1"

ForeColor="#333333"GridLines="None"PageSize="5">

<FooterStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>

<RowStyleBackColor="#F7F6F3"ForeColor="#333333"/>

<Columns>

<asp:BoundFieldDataField="product code"HeaderText="product code"ReadOnly="True"

SortExpression="product code"/>

<asp:BoundFieldDataField="Name"HeaderText="Name"SortExpression="Name"/>

<asp:BoundFieldDataField="Type"HeaderText="Type"SortExpression="Type"/>

<asp:BoundFieldDataField="Price"HeaderText="Price"SortExpression="Price"/>

<asp:BoundFieldDataField="Comments"HeaderText="Comments"SortExpression="Comments"/>

</Columns>

<PagerStyleBackColor="#284775"ForeColor="White"HorizontalAlign="Center"/>

<SelectedRowStyleBackColor="#E2DED6"Font-Bold="True"ForeColor="#333333"/>

<HeaderStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>

<EditRowStyleBackColor="#999999"/>

<AlternatingRowStyleBackColor="White"ForeColor="#284775"/>

</asp:GridView>

<br/>

</div>

</form> </body>

</html>

and theres my webpage

|||

<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox>

<asp:TextBoxID="TextBox2"runat="server"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Text="Search"/><br/>

<asp:AccessDataSourceID="AccessDataSource1"runat="server"DataFile="~/App_Data/brakes.mdb"

SelectCommand="SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + @.Comments + '%') OR ([Type] LIKE '%' + @.Type + '%') ">

<SelectParameters>

<asp:ControlParameterControlID="TextBox1"DefaultValue=""Name="comments"PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox2"DefaultValue=""Name="type"PropertyName="Text"/>

</SelectParameters>

</asp:AccessDataSource>

|||

no luck! it still doesnt work, its a step forward though, the page actually loads without a error message, this time i can enter data into the textboxes to search and it just returns all the data not only results that should be returned...

|||

btw how come the page loads when you use the @. in front of the controlD?

|||

something to consider, page loads but the query doesnt work when using '@.comments' (even with songle control),

page loads and query works when using '?' and one control

page doesnt load when using '@.?'

only difference is that comments is text whereas '?' isnt, so what other non-text character can i use? ive tried using '#' but that doesnt work so is '?' the only control name i can use?

multiple connection sql server express

HI
I have a win app (always running 24x7) (.net 2 c#) that uses sql server
express 2005.
I use the following connection string to attach it in the app.config
<connectionStrings>
<add name="Browser.Properties.Settings.DataConnectionSt ring"
connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data. mdf;Integrated
Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Every day a win service, using the same MDB updates/imports the data from
another source. This all works as expected. when the windows service has
completed its update, the win app is notified to refresh its data and show
the updates.
My issue, is that though the data has been refreshed and the win app
reconnects to the database to collect the updates (new datasets) it does not
reflect the updates, simply the older version. I think it is the way I have
attached the database (the win app starts first on sys reboot) is there a
better way of 2 apps using the same database without having to attach it.
Thanks
Richard
It doesn't work that way. You are either both using the same mdf or you are
not. If you are using the same one then any committed changes one user makes
are immediately available to the other user. Your app may require refreshing
if it caches the data but SQL Server only has 1 copy of the committed data.
The exception is if you are using one of the snapshot isolation levels. In
that case depending on the level and what you are doing you may see the
original versions. But you have had to explicitly turn this on and I don't
even think Express edition has this capability available in the first place.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Steele" <RichardSteele@.discussions.microsoft.com> wrote in message
news:48E10FFE-E1A2-4318-BFE0-F1E07E077D17@.microsoft.com...
> HI
> I have a win app (always running 24x7) (.net 2 c#) that uses sql server
> express 2005.
> I use the following connection string to attach it in the app.config
> <connectionStrings>
> <add name="Browser.Properties.Settings.DataConnectionSt ring"
> connectionString="Data
> Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data. mdf;Integrated
> Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
> providerName="System.Data.SqlClient" />
> </connectionStrings>
> Every day a win service, using the same MDB updates/imports the data from
> another source. This all works as expected. when the windows service has
> completed its update, the win app is notified to refresh its data and show
> the updates.
> My issue, is that though the data has been refreshed and the win app
> reconnects to the database to collect the updates (new datasets) it does
> not
> reflect the updates, simply the older version. I think it is the way I
> have
> attached the database (the win app starts first on sys reboot) is there a
> better way of 2 apps using the same database without having to attach it.
> --
> Thanks
> Richard

multiple connection sql server express

HI
I have a win app (always running 24x7) (.net 2 c#) that uses sql server
express 2005.
I use the following connection string to attach it in the app.config
<connectionStrings>
<add name="Browser.Properties.Settings.DataConnectionString"
connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data.mdf;Integrated
Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Every day a win service, using the same MDB updates/imports the data from
another source. This all works as expected. when the windows service has
completed its update, the win app is notified to refresh its data and show
the updates.
My issue, is that though the data has been refreshed and the win app
reconnects to the database to collect the updates (new datasets) it does not
reflect the updates, simply the older version. I think it is the way I have
attached the database (the win app starts first on sys reboot) is there a
better way of 2 apps using the same database without having to attach it.
--
Thanks
RichardIt doesn't work that way. You are either both using the same mdf or you are
not. If you are using the same one then any committed changes one user makes
are immediately available to the other user. Your app may require refreshing
if it caches the data but SQL Server only has 1 copy of the committed data.
The exception is if you are using one of the snapshot isolation levels. In
that case depending on the level and what you are doing you may see the
original versions. But you have had to explicitly turn this on and I don't
even think Express edition has this capability available in the first place.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Steele" <RichardSteele@.discussions.microsoft.com> wrote in message
news:48E10FFE-E1A2-4318-BFE0-F1E07E077D17@.microsoft.com...
> HI
> I have a win app (always running 24x7) (.net 2 c#) that uses sql server
> express 2005.
> I use the following connection string to attach it in the app.config
> <connectionStrings>
> <add name="Browser.Properties.Settings.DataConnectionString"
> connectionString="Data
> Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data.mdf;Integrated
> Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
> providerName="System.Data.SqlClient" />
> </connectionStrings>
> Every day a win service, using the same MDB updates/imports the data from
> another source. This all works as expected. when the windows service has
> completed its update, the win app is notified to refresh its data and show
> the updates.
> My issue, is that though the data has been refreshed and the win app
> reconnects to the database to collect the updates (new datasets) it does
> not
> reflect the updates, simply the older version. I think it is the way I
> have
> attached the database (the win app starts first on sys reboot) is there a
> better way of 2 apps using the same database without having to attach it.
> --
> Thanks
> Richard

Multiple columns search

Hi there,
I have a quite large products table, with the following fields:
Product_title nvarchar(100)
Product_summary nvarchar(1000)
Product_description text
Is it possible to run a FTS query throughtout all of this 3 fields?
For example, user searches for: "Printer AND Windows AND inkjet". all 3
words never appear on a single column, but they appear on a single ROW. I
would like to output rows that includes all 3 strings.
Obviouslly I can create another table that will attach data from all 3
fileds, but I'm looking for a more elegent solution.
Thanks!
use a freetext or freetextable search like this
select * from tableName where freetext(*, '"SearchPhrase"')
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:%23up%231AgIEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I have a quite large products table, with the following fields:
> Product_title nvarchar(100)
> Product_summary nvarchar(1000)
> Product_description text
> Is it possible to run a FTS query throughtout all of this 3 fields?
> For example, user searches for: "Printer AND Windows AND inkjet". all 3
> words never appear on a single column, but they appear on a single ROW. I
> would like to output rows that includes all 3 strings.
> Obviouslly I can create another table that will attach data from all 3
> fileds, but I'm looking for a more elegent solution.
> Thanks!
>

Multiple Columns in report Matrix?!

Hi there,

I'm trying to use a matrix to create report with the following format.

Location Carrier Period Total Total YTD

C1 C2 C3

Bangkok 1 2 3 6 5

CLT 1 1 1 3 5

Totals 9 10

I'm having trouble designing my report using a matrix. Any ideas how to add "total ytd" column and the totals below??

Thanks in advance,

Elias

Which version of SQL Server are you using?

With SQL2005 there is a PIVOT command that would allow you to produce a more conventional shape of report and then flip it.

|||

I'm using SQL reporting 2005. Could you please walk me through how to do that? Many thanks.

|||

Have you just got SQL2005 Reporting or have you got a backend SQL2005 database?

|||

Hello I had the same problem. I just adjusted the body to allow 2 text fields at the top of the colum and put my Sum expression in each of them for those colums. It gives the totals at the top but that can be a good thing if some one is looking for the totals and doesn't want to scroll to the bottom of your report.

|||

using both SSRS and SQL Server 2005

|||

Could you please give more details on how to do this? Still no able to solve my problem. Thanks

|||

The solution to creating the required matrix, will require some different thinking.

Location Carrier Period Total
C1 C2 C3 Total YTD
Bangkok 1 2 3 6 5
CLT 1 1 1 3 5
Totals 9 10

Is the number of carriers fixed? If yes then the problem simplifies enormously, as there besides using a dynamic temporary table (dynamic in the sense that when an extra carrier appears an additional column will be created), a new table can be created as an intermediate in the analysis. This can be either a table within the database or an ordinary temporary table. The SELECT to read this can include a UNION to a SUM on PeriodTotal and TotalYTD as in
SELECT 'A', Location, CONVERT(VARCHAR(10), C1) AS C1,
CONVERT(VARCHAR(10), C2) AS C2,
CONVERT(VARCHAR(10), C3) AS C3, PeriodTotal, TotalYTD FROM Fred
UNION
SELECT 'B', '' AS LOCATION, '' AS C1, '' AS C2, 'Totals' AS C3, SUM(PeriodTotal), SUM(TotalYTD) FROM FRED
ORDER BY 1, 2

|||

Neither the location nor carrier are fixed. Thanks!

|||

>Neither the location nor carrier are fixed.

Since their numbers are not fixed. you will need to adopt an array of arrays approach to accumulate the data and then generate the HTML yourself. The generation of the HTML sounds complicated, but once you make a mockup of what you want to generate, it becomes quite simple.

Multiple columns full text search doesn't seems to work properly

Hi all !
I have a full text search index on a table with several fields
indexed.
I tried the following query :
SELECT MyIndexedTable.MyIndexedTableID
MyIndexedTable.Title,
KEY_TBL.RANK RANK_Total
FROM
MyIndexedTable
INNER JOIN
CONTAINSTABLE(MyIndexedTable,*,'"sport" AND "news"') AS KEY_TBL
ORDER BY KEY_TBL.RANK DESC
There are some records that are not returned by the query but they
have the words "sport" and "news" in their fields.
Any idea what could be happening?
Thanks in advance.
Xavi
This should work. Note that sports and news will have to be in the same
column to get a hit form this row.
"Xavi" <xaspas@.gmail.com> wrote in message
news:1177326871.889457.41300@.e65g2000hsc.googlegro ups.com...
> Hi all !
> I have a full text search index on a table with several fields
> indexed.
> I tried the following query :
> SELECT MyIndexedTable.MyIndexedTableID
> MyIndexedTable.Title,
> KEY_TBL.RANK RANK_Total
> FROM
> MyIndexedTable
> INNER JOIN
> CONTAINSTABLE(MyIndexedTable,*,'"sport" AND "news"') AS KEY_TBL
> ORDER BY KEY_TBL.RANK DESC
> There are some records that are not returned by the query but they
> have the words "sport" and "news" in their fields.
> Any idea what could be happening?
> Thanks in advance.
> Xavi
>
|||Exactly but I'd like to know how to make it work when words are
located in different fields.
I have read about creating an extra Text (or nText) field with all the
content in it and then create an index based on that field, I also
have seen something using unions and multiple querys on the index. I
wonder if there's any better approach and which one will give better
perfomance.
Thank you for your answer!
Xavi
On 23 abr, 14:31, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> This should work. Note that sports and news will have to be in the same
> column to get a hit form this row.
> "Xavi" <xas...@.gmail.com> wrote in message
> news:1177326871.889457.41300@.e65g2000hsc.googlegro ups.com...
>
>
|||The concatenated column offers the best performance.
"Xavi" <xaspas@.gmail.com> wrote in message
news:1177335970.294430.289990@.p77g2000hsh.googlegr oups.com...
> Exactly but I'd like to know how to make it work when words are
> located in different fields.
> I have read about creating an extra Text (or nText) field with all the
> content in it and then create an index based on that field, I also
> have seen something using unions and multiple querys on the index. I
> wonder if there's any better approach and which one will give better
> perfomance.
> Thank you for your answer!
> Xavi
> On 23 abr, 14:31, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>

Multiple accounts with the name MSSQLSvc...

Hi,
Got a KDC Error with the following description:
========================================
==
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 28-04-2005
Time: 2:01:01
User: N/A
Computer: server
Description:
There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
type DS_SERVICE_PRINCIPAL_NAME.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
========================================
==
The LDP-tool gives the following results:
========================================
==
***Searching...
ldap_search_s(ld, "DC=domain,DC=local", 2,
"serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
&msg)
Result <0>: (null)
Matched DNs:
Getting 2 entries:[vbcol=seagreen]
4> objectClass: top; person; organizationalPerson; user;
1> cn: Administrator;
1> description: Built-in account for administering the computer/domain;
1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=l
ocal;
1> name: Administrator;
1> canonicalName: domain.local/Users/Administrator;[vbcol=seagreen]
5> objectClass: top; person; organizationalPerson; user; computer;
1> cn: server;
1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
1> name: server;
1> canonicalName: domain.local/Domain Controllers/server;
========================================
==
Can anyone explain me what I can do about this? Deleting one of the accounts
is not an option I guess... I read that in some cases a computer or user
should be unregistered en registered again but in this case I'm not so
confident about it re-registring the Server itself or the
administrator-account..
Any help on this is much appreciated.
Michel Schuurman
Omni Trade Automatisering B.V.Somebody setup the SPN for the service account on those machines,
unfortunately the same SPN has been promoted more than one time.
Jens Suessmeyer.
"Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> Got a KDC Error with the following description:
> ========================================
==
> Event Type: Error
> Event Source: KDC
> Event Category: None
> Event ID: 11
> Date: 28-04-2005
> Time: 2:01:01
> User: N/A
> Computer: server
> Description:
> There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
> type DS_SERVICE_PRINCIPAL_NAME.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ========================================
==
>
> The LDP-tool gives the following results:
> ========================================
==
> ***Searching...
> ldap_search_s(ld, "DC=domain,DC=local", 2,
> "serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
> &msg)
> Result <0>: (null)
> Matched DNs:
> Getting 2 entries:
> 4> objectClass: top; person; organizationalPerson; user;
> 1> cn: Administrator;
> 1> description: Built-in account for administering the computer/domain;
> 1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=l
ocal;
> 1> name: Administrator;
> 1> canonicalName: domain.local/Users/Administrator;
> 5> objectClass: top; person; organizationalPerson; user; computer;
> 1> cn: server;
> 1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
> 1> name: server;
> 1> canonicalName: domain.local/Domain Controllers/server;
> ========================================
==
> Can anyone explain me what I can do about this? Deleting one of the
> accounts is not an option I guess... I read that in some cases a computer
> or user should be unregistered en registered again but in this case I'm
> not so confident about it re-registring the Server itself or the
> administrator-account..
> Any help on this is much appreciated.
>
> Michel Schuurman
> Omni Trade Automatisering B.V.
>|||The SPN should be registered under the account SQL is starting under, and
ONLY that account.
You can use the utility setspn to check for the existence of other spn's,
delete the ones you don't want, and add the one you need.
Please note...you are NOT deleting the ACCOUNT, but the Service Principle
Name, which resides IN that user object.
Here's an article with more info than you ever wanted to know about SQL and
SPN's.:
http://support.microsoft.com/defaul...kb;en-us;811889
but there are links to getting setspn in there.
Donna Lambert
"Jens Sü?meyer" wrote:

> Somebody setup the SPN for the service account on those machines,
> unfortunately the same SPN has been promoted more than one time.
> Jens Suessmeyer.
>
> "Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
> news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
>
>

Multiple accounts with the name MSSQLSvc...

Hi,
Got a KDC Error with the following description:
==========================================
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 28-04-2005
Time: 2:01:01
User: N/A
Computer: server
Description:
There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
type DS_SERVICE_PRINCIPAL_NAME.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
==========================================
The LDP-tool gives the following results:
==========================================
***Searching...
ldap_search_s(ld, "DC=domain,DC=local", 2,
"serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
&msg)
Result <0>: (null)
Matched DNs:
Getting 2 entries:[vbcol=seagreen]
4> objectClass: top; person; organizationalPerson; user;
1> cn: Administrator;
1> description: Built-in account for administering the computer/domain;
1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
1> name: Administrator;
1> canonicalName: domain.local/Users/Administrator;[vbcol=seagreen]
5> objectClass: top; person; organizationalPerson; user; computer;
1> cn: server;
1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
1> name: server;
1> canonicalName: domain.local/Domain Controllers/server;
==========================================
Can anyone explain me what I can do about this? Deleting one of the accounts
is not an option I guess... I read that in some cases a computer or user
should be unregistered en registered again but in this case I'm not so
confident about it re-registring the Server itself or the
administrator-account..
Any help on this is much appreciated.
Michel Schuurman
Omni Trade Automatisering B.V.
Somebody setup the SPN for the service account on those machines,
unfortunately the same SPN has been promoted more than one time.
Jens Suessmeyer.
"Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> Got a KDC Error with the following description:
> ==========================================
> Event Type: Error
> Event Source: KDC
> Event Category: None
> Event ID: 11
> Date: 28-04-2005
> Time: 2:01:01
> User: N/A
> Computer: server
> Description:
> There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
> type DS_SERVICE_PRINCIPAL_NAME.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ==========================================
>
> The LDP-tool gives the following results:
> ==========================================
> ***Searching...
> ldap_search_s(ld, "DC=domain,DC=local", 2,
> "serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
> &msg)
> Result <0>: (null)
> Matched DNs:
> Getting 2 entries:
> 4> objectClass: top; person; organizationalPerson; user;
> 1> cn: Administrator;
> 1> description: Built-in account for administering the computer/domain;
> 1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
> 1> name: Administrator;
> 1> canonicalName: domain.local/Users/Administrator;
> 5> objectClass: top; person; organizationalPerson; user; computer;
> 1> cn: server;
> 1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
> 1> name: server;
> 1> canonicalName: domain.local/Domain Controllers/server;
> ==========================================
> Can anyone explain me what I can do about this? Deleting one of the
> accounts is not an option I guess... I read that in some cases a computer
> or user should be unregistered en registered again but in this case I'm
> not so confident about it re-registring the Server itself or the
> administrator-account..
> Any help on this is much appreciated.
>
> Michel Schuurman
> Omni Trade Automatisering B.V.
>
|||The SPN should be registered under the account SQL is starting under, and
ONLY that account.
You can use the utility setspn to check for the existence of other spn's,
delete the ones you don't want, and add the one you need.
Please note...you are NOT deleting the ACCOUNT, but the Service Principle
Name, which resides IN that user object.
Here's an article with more info than you ever wanted to know about SQL and
SPN's.:
http://support.microsoft.com/default...b;en-us;811889
but there are links to getting setspn in there.
Donna Lambert
"Jens Sü?meyer" wrote:

> Somebody setup the SPN for the service account on those machines,
> unfortunately the same SPN has been promoted more than one time.
> Jens Suessmeyer.
>
> "Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
> news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
>
>

Multiple accounts with the name MSSQLSvc...

Hi,
Got a KDC Error with the following description:
========================================== Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 28-04-2005
Time: 2:01:01
User: N/A
Computer: server
Description:
There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
type DS_SERVICE_PRINCIPAL_NAME.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
==========================================
The LDP-tool gives the following results:
========================================== ***Searching...
ldap_search_s(ld, "DC=domain,DC=local", 2,
"serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
&msg)
Result <0>: (null)
Matched DNs:
Getting 2 entries:
>> Dn: CN=Administrator,CN=Users,DC=domain,DC=local
4> objectClass: top; person; organizationalPerson; user;
1> cn: Administrator;
1> description: Built-in account for administering the computer/domain;
1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
1> name: Administrator;
1> canonicalName: domain.local/Users/Administrator;
>> Dn: CN=server,OU=Domain Controllers,DC=domain,DC=local
5> objectClass: top; person; organizationalPerson; user; computer;
1> cn: server;
1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
1> name: server;
1> canonicalName: domain.local/Domain Controllers/server;
==========================================
Can anyone explain me what I can do about this? Deleting one of the accounts
is not an option I guess... I read that in some cases a computer or user
should be unregistered en registered again but in this case I'm not so
confident about it re-registring the Server itself or the
administrator-account..
Any help on this is much appreciated.
Michel Schuurman
Omni Trade Automatisering B.V.Somebody setup the SPN for the service account on those machines,
unfortunately the same SPN has been promoted more than one time.
Jens Suessmeyer.
"Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> Got a KDC Error with the following description:
> ==========================================> Event Type: Error
> Event Source: KDC
> Event Category: None
> Event ID: 11
> Date: 28-04-2005
> Time: 2:01:01
> User: N/A
> Computer: server
> Description:
> There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
> type DS_SERVICE_PRINCIPAL_NAME.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ==========================================>
> The LDP-tool gives the following results:
> ==========================================> ***Searching...
> ldap_search_s(ld, "DC=domain,DC=local", 2,
> "serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
> &msg)
> Result <0>: (null)
> Matched DNs:
> Getting 2 entries:
>> Dn: CN=Administrator,CN=Users,DC=domain,DC=local
> 4> objectClass: top; person; organizationalPerson; user;
> 1> cn: Administrator;
> 1> description: Built-in account for administering the computer/domain;
> 1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
> 1> name: Administrator;
> 1> canonicalName: domain.local/Users/Administrator;
>> Dn: CN=server,OU=Domain Controllers,DC=domain,DC=local
> 5> objectClass: top; person; organizationalPerson; user; computer;
> 1> cn: server;
> 1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
> 1> name: server;
> 1> canonicalName: domain.local/Domain Controllers/server;
> ==========================================> Can anyone explain me what I can do about this? Deleting one of the
> accounts is not an option I guess... I read that in some cases a computer
> or user should be unregistered en registered again but in this case I'm
> not so confident about it re-registring the Server itself or the
> administrator-account..
> Any help on this is much appreciated.
>
> Michel Schuurman
> Omni Trade Automatisering B.V.
>|||The SPN should be registered under the account SQL is starting under, and
ONLY that account.
You can use the utility setspn to check for the existence of other spn's,
delete the ones you don't want, and add the one you need.
Please note...you are NOT deleting the ACCOUNT, but the Service Principle
Name, which resides IN that user object.
Here's an article with more info than you ever wanted to know about SQL and
SPN's.:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811889
but there are links to getting setspn in there.
Donna Lambert
"Jens Sü�meyer" wrote:
> Somebody setup the SPN for the service account on those machines,
> unfortunately the same SPN has been promoted more than one time.
> Jens Suessmeyer.
>
> "Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
> news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Got a KDC Error with the following description:
> >
> > ==========================================> > Event Type: Error
> > Event Source: KDC
> > Event Category: None
> > Event ID: 11
> > Date: 28-04-2005
> > Time: 2:01:01
> > User: N/A
> > Computer: server
> > Description:
> > There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
> > type DS_SERVICE_PRINCIPAL_NAME.
> >
> > For more information, see Help and Support Center at
> > http://go.microsoft.com/fwlink/events.asp.
> > ==========================================> >
> >
> > The LDP-tool gives the following results:
> >
> > ==========================================> > ***Searching...
> > ldap_search_s(ld, "DC=domain,DC=local", 2,
> > "serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
> > &msg)
> > Result <0>: (null)
> > Matched DNs:
> > Getting 2 entries:
> >> Dn: CN=Administrator,CN=Users,DC=domain,DC=local
> > 4> objectClass: top; person; organizationalPerson; user;
> > 1> cn: Administrator;
> > 1> description: Built-in account for administering the computer/domain;
> > 1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
> > 1> name: Administrator;
> > 1> canonicalName: domain.local/Users/Administrator;
> >> Dn: CN=server,OU=Domain Controllers,DC=domain,DC=local
> > 5> objectClass: top; person; organizationalPerson; user; computer;
> > 1> cn: server;
> > 1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
> > 1> name: server;
> > 1> canonicalName: domain.local/Domain Controllers/server;
> > ==========================================> >
> > Can anyone explain me what I can do about this? Deleting one of the
> > accounts is not an option I guess... I read that in some cases a computer
> > or user should be unregistered en registered again but in this case I'm
> > not so confident about it re-registring the Server itself or the
> > administrator-account..
> >
> > Any help on this is much appreciated.
> >
> >
> >
> > Michel Schuurman
> >
> > Omni Trade Automatisering B.V.
> >
>
>

Multi-part Identfier could not be found (with SQL Script)

Hi all,

I have attached the following script from which the error message come from.

Please help me out.

use PatientCare

go

select pr.PatientId, p.FirstName,p.LastName, m.Name

from Prescription as pr inner join Patient as p inner join Medicine as m

on p.PatientId = pr.PatientId

on pr.MedicineCode = m.MedicineCode;

Error message got is as below:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "pr.PatientId" could not be bound.

Thanx in advance.

Ronald

Ronaldlee Ejalu wrote:

use PatientCare

go

select pr.PatientId, p.FirstName,p.LastName, m.Name

from Prescription as pr inner join Patient as p inner join Medicine as m

on p.PatientId = pr.PatientId

on pr.MedicineCode = m.MedicineCode;

SELECT pr.PatientID, p.FirstName, p.LastName, m.Name

FROM Prescription pr

JOIN Patient p ON p.PatientID = pr.PatientID

JOIN Medicine m ON pr.MedicineCode = m.MedicineCode

or you can do it this way

SELECT pr.PatientID, p.FirstName, p.LastName, mName

FROM Prescription pr, Patient p, Medicine m

WHERE p.PatientID = pr.PatientID AND pr.MedicineCode = m.MedicineCode

Adamus

|||

hi ronald

looks like you got a syntax error and adamus is right

i deleted you other post cause it looks identical to this one

ragards

|||

Just to add what to has been said. You have nested joins, which I have bolded here:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join Patient as p
inner join Medicine as m
on p.PatientId = pr.PatientId
on pr.MedicineCode = m.MedicineCode;

This nested join has to operate first, then the next join:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join PatientMedicineJoinResults
on pr.MedicineCode = m.MedicineCode;

The point of this is to simplify how queries can be written when you are joining together tables, especially for lookup tables where there are outer joins involved:

select <columnlist>
from table
left outer join table2
inner join table2Lookup
on table2.table2LookupId = table2Lookup.table2LookupId
on table.tableKey = table2.tableKey
left outer join table3
inner join table3Lookup
on table3.table3LookupId = table3Lookup.table3LookupId

Not, of course that any of this matches what you need :) In your case, you are joining each of the tables to the prescription table, so the join criteria must match that, and not be nested:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join Patient as p
on p.PatientId = pr.PatientId
inner join Medicine as m
on pr.MedicineCode = m.MedicineCode;

|||

Hi all,

I really thank you for this.

It worked.