Help with SQL query

I have a ContactTypes table as follows:-
ContactTypeId int PK
ContactType varchar(50)
I have several different types of contact types (i.e. Invoice,
Business, Manager, Home, etc).
I have a Client table as follows:
ClienttId int PK
ClientName varchar(50)
I have a ContactAddresses table as follows:-
ContactAddressId int PK
ClientId
ContactTypeId
Address1
Address2
Town
Postcode
Can anyone tell me how i can write an SQL query to fetch all the
different types of contacts for a client, regardless if they exist or
not?
The query should return something like for a selected Client:-
Contact Type Address1 Address2 Town PostCode
Invoice 123 somewhere there AB12 C12
Business 456 overthere here CD34 E21
Manager
Home 987 here where D21 S12
So far i have come up with the following but it requires separate SQL
statements for each column. Can anyone recommend a way around this?
SELECT ContactType
,(SELECT Address1 FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
,(SELECT Address2 FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
,(SELECT Town FROM ContactAddresses CA WHERE ClientId = @.clientId AND
CA.ContactTypeId = CT.ContactTypeId)
,(SELECT PostCode FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
FROM ContactTypes CT
Thanks in advance for any suggestions.
DanI think you want a cross join to get all possible combinations of client and
contact type, and an outer join to get the addresses that exist, while still
keeping the contact type/client combinations that do not.
select c.clientname
, b.ContactType
, a.Address1
, a.Address2
, a.Town
, a.PostCode
from ContactTypeId b
cross join Client c
left outer join ContactAddresses a
on c.ClienttId = a.ClienttId
and c.ContactTypeID = b.ContactTypeID
<dan_williams@.newcross-nursing.com> wrote in message
news:1148503334.127146.20190@.i39g2000cwa.googlegroups.com...
> I have a ContactTypes table as follows:-
> ContactTypeId int PK
> ContactType varchar(50)
> I have several different types of contact types (i.e. Invoice,
> Business, Manager, Home, etc).
> I have a Client table as follows:
> ClienttId int PK
> ClientName varchar(50)
> I have a ContactAddresses table as follows:-
> ContactAddressId int PK
> ClientId
> ContactTypeId
> Address1
> Address2
> Town
> Postcode
>
> Can anyone tell me how i can write an SQL query to fetch all the
> different types of contacts for a client, regardless if they exist or
> not?
> The query should return something like for a selected Client:-
> Contact Type Address1 Address2 Town PostCode
> Invoice 123 somewhere there AB12 C12
> Business 456 overthere here CD34 E21
> Manager
> Home 987 here where D21 S12
> So far i have come up with the following but it requires separate SQL
> statements for each column. Can anyone recommend a way around this?
> SELECT ContactType
> ,(SELECT Address1 FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT Address2 FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT Town FROM ContactAddresses CA WHERE ClientId = @.clientId AND
> CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT PostCode FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> FROM ContactTypes CT
> Thanks in advance for any suggestions.
> Dan
>

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
ChuckConcatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help with SQL

I have a table named "policy_details" having four columns called policy_details(varchar),effectdate(datetime),Historyid(int) and policy_status(varchar).

I want to keep only the maximum historyid records in "policy_details" where policy_details and effectdate should be equal and policy_status should be '30240084' . I want to delete the remaining records where policy_details and effectdate should be equal and policy_status should be '30240084'.

Cheers

Praveen

So if you order by policy_details ASC, affectdate ASC, historyid DESC then you only want to keep the first row from each (policy_details, affectdate) group.

I would use the ROW_NUMBER() function and delete where it's not 1

DELETE
FROM policy_details
WHERE ROW_NUMBER() OVER ( PARTITION BY policy_details, affectdate
ORDER BY historyid DESC
) > 1
AND policy_status = '30240084'

|||

Adam,

Thanks for your query.

I am getting an error "The ranking function "ROW_NUMBER" must have an ORDER BY clause."

One more thing is i want same effectdate with only date but not time part.

DELETE
FROM policy_details
WHERE ROW_NUMBER() OVER ( PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103)
ORDER BY historyid DESC
) > 1
AND policy_status = '30240084'

I did not understand "The ranking function "ROW_NUMBER" must have an ORDER BY clause" error though it is having order by clause. Any ideas?

Cheers

Praveen

|||

Use A CTE, it will work:

WITH myCTE

AS

(SELECT ROW_NUMBER() OVER ( PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103) ORDER BY historyid DESC) as num

FROM policy_details

WHERE policy_status = '30240084')

DELETE FROM myCTE WHERE num>1

|||

Interesting, when I write the following query, which is almost exactly like yours:

begin transaction

DELETE
FROM person.address
WHERE ROW_NUMBER() OVER ( PARTITION BY addressId
ORDER BY addressLine1 DESC
) > 1
and addressLine1 = 'fred'
rollback transaction

The error I get is:

Msg 4108, Level 15, State 1, Line 3
Windowed functions can only appear in the SELECT or ORDER BY clauses.

(which is more clear).

|||Hello,
After some research, I tested out this syntax which works:
DELETE c
FROM (SELECT * FROM (SELECT RANK() OVER (PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103) ORDER BY historyid DESC) as num
FROM policy_details WHERE policy_status = '30240084' ) AS t
WHERE t.num>1) c|||

Hi Limno,

Thanks for your help with the SQL which works fine with my data.

I have written a small strored proc which deletes some data based on certain criteria which works fine but it takes approximately 30 minutes for one million records. Is there any way to write this stored proc logic into a single SQl query or sub queries?

The SP is :

-- ***********************************************************************

declare @.policy_details_id uniqueidentifier

declare @.Prepolicy_details_id uniqueidentifier

declare @.EffectDate datetime

declare @.CloseDate datetime

declare @.historyid int

DECLARE DATECur CURSoR FOR

select distinct(policy_details_id),effectdate,closedate,historyid from SM_Cust_policy_Details

where closedate = '2079-06-06 00:00:00.000' and derivative = 0

order by policy_details_id,effectdate

OPEN DATECur

FETCH NEXT FROM DATECur INTO @.policy_details_id, @.effectdate,@.CloseDate,@.historyid

WHILE @.@.FETCH_STATUS = 0

BEGIN

DELETE SM_Cust_policy_details where policy_details_id=@.policy_details_id

and historyid <> @.historyid

and closedate <> '2079-06-06 00:00:00.000'

and CONVERT(DATETIME,CONVERT(CHAR(10),effectdate,103)) >= CONVERT(DATETIME,CONVERT(CHAR(10),@.effectdate,103))

and policy_status_id = 30240084

and derivative = 0

FETCH NEXT FROM DATECur INTO @.policy_details_id, @.effectdate,@.CloseDate,@.historyid

END

CLOSE DATECur

DEALLOCATE DATECur

-- ***********************************************************************

Once again thanks for your help..

Cheers

Praveen

|||Can you explain what the query is trying to do in english rather than trying to decode your criteria. The potential query may differ depending the nature of the data in your table e.g. uniqueness and nullability of the columns.|||

Hi

I need to delete the table data based on the following criteria.

1) "policy_details_id" should be same

2) "effectdate" is greater than or equal to effectdate

3) "HistoryId" should not be same

4) "closedate" is not equal to ' 2079-06-06 00:00:00.000 '

5) "policy_status_id" should be 30240084

6) "Derivative" should be 0

Cheers

Praveen

|||

I can read your code. I got that much from the stored proc you posted. I'm trying to understand what the stored procedure is trying to achieve.

Should just one row per policy_details_id remain after the delete? Why are you doing distinct of the policy_details_id in you cursor query? why delete where effectdate is greater than @.effectdate? Wouldn't that delete more recent rows.

Like I said, I'm trying to understand what this query is doing "in english" and not "in SQL". An explanation similar to your original posting is what I'm after.

|||

Hi Adam,

Thanks for your reply and here are the answers for your queries.

******** Should just one row per policy_details_id remain after the delete?

Yes, I want to keep only one row per policy_details_id after the deletion.

****** Why are you doing distinct of the policy_details_id in you cursor query?

I used distinct caluse in order to reduce the cursor result set in stored proc. Anyway you can avoid distinct caluse in the sql query.

******** why delete where effectdate is greater than @.effectdate? Wouldn't that delete more recent rows

Yes, it is going to delete more recent rows.

Cheers

Praveen

|||

Hello:

Please check this query :

DELETE c

FROM (SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY policy_details_id ORDER BY historyid DESC, effectdate) as num

FROM policy_details WHERE (closedate<> '2079-06-06 00:00:00.000' OR closedate is NULL) AND Derivative=0 AND policy_status_id = 30240084 ) AS t

WHERE t.num>1) c

|||

Hi Limno,

Needs to change the query.

First i need to do the following query..

1) select policy_details_id,effectdate,closedate,historyid from SM_Cust_policy_Details where closedate = '2079-06-06 00:00:00.000' and derivative = 0

Based on the result set of this query i need to do the following delete query.

2) DELETE SM_Cust_policy_details where policy_details_id = resultset_policy_details_id and historyid <> resultset_historyid and closedate <> '2079-06-06 00:00:00.000' and CONVERT(DATETIME,CONVERT(CHAR(10),effectdate,103))>=CONVERT(DATETIME,CONVERT(CHAR(10),resultset_effectdate,103)) and policy_status_id=30240084 and derivative = 0.

Note: resultset_policy_details_id,resultset_historyid,resultset_effectdate are the result set query values of policy_details_id,historyid,effectdate in query number 1.

How can i integrate both of the above queries and make a single query?

Cheers

Praveen

|||

Could you post a set of your sample data in your table and the expected result? Thanks.

|||

The data consists like this..

policy_details_id policy_status_id historyid effectdate closedate

70C36E97-9564-A048-0000-9665018B81FF 30240084 9 2004-09-11 12:00:00.000 2004-10-11 11:34:00.000
70C36E97-9564-A048-0000-9665018B81FF 30240084 10 2004-10-11 11:34:00.000 2005-09-11 11:47:00.000
70C36E97-9564-A048-0000-9665018B81FF 30240084 11 2005-09-11 12:00:00.000 2005-09-11 12:00:00.000
70C36E97-9564-A048-0000-9665018B81FF 14075352 12 2005-09-11 11:47:00.000 2079-06-06 00:00:00.000

First i need to consider the effectdate where closedate is '2079-06-06 00:00:00.000'.In this case it is '2005-09-11 11:47:00.000'.

I need to delete the records where effectdate(only datepart) is equal or greater than '2005-09-11' and policy_status_id should be 30240084.

In the above case the third record i.e historyid = 11 is going to be deleted.

Let me know if you have any problems to understand.

Cheers

Praveen

Help with some SQL pls

I have this SP:

CREATE PROCEDURE GetFarmDetails
(@.FarmID varchar(200))
AS
SELECT Name, Address1,Address2, County,Postcode,TelNo,FaxNo,MobileNo,Contact,CustNo,StatusID,RepID FROM Customers
WHERE CustomerID = @.CustomerID

RETURN
GO

But I want the SQL to find the StatusName and RepName from the Status and Rep table according to the StatusID and RepID returned above. Could this be added to the above SQL and I get the StatusName and RepName from the one SP?

Thanks for any help.

Simon

yes do you have any PK-FK relation with the other two tables youmentioned? you need to provide us the data structure too for someone tohelp you out..
|||

Yes there are Pk-FK relationships between Rep and Status tables and Customers.

Thanks

Simon

|||Sorry, the SP should read:

CREATE PROCEDURE GetFarmDetails
(@.CustomerIDvarchar(200))
AS
SELECT Name, Address1,Address2, County,Postcode,TelNo,FaxNo,MobileNo,Contact,CustNo,StatusID,RepID FROM Customers
WHERE CustomerID = @.CustomerID

RETURN
GO

Thanks
Simon
|||

Hello,

Here is the Solution.

SELECT Status.StatusName , Rep.RepName FROM
Customer C

LEFT OUTER JOIN Status
ON StatusID = C.StatusID
LEFT OUTER JOIN Rep
ON RepID = C.RepID

WHERE
CustomerID = @.CustomerID

Here you go little one,

Hope it will work out well

Regards

KeanXSoul

|||Just the job, will post the final SP later for others.
Simon

help with Select statement

Hi!

This is my table:

CREATE TABLE [Query_Result] (
sifrob VARCHAR(13),
katbroj VARCHAR(15),
kol FLOAT
)

This is some values:

INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 3)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501910', 'G-46935', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508122', 'G-46944', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508698', 'G-50314', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49502695', 'G-51201', 1)

As you can see some vaules are duplicated and only the 'kol' is
different. I would like to write a SELECT statements that will get all the
values from table, but on the duplicated ones that I get it once and the
'kol' column is sum of the two 'kol' values ( so if we have 499501879 the
'kol must be 4.

Please help.

Best regards,
Zvonkoselect sifrob,katbroj,sum(kol) as kol
from Query_Result
group by sifrob,katbroj

Denis the SQL Menace
http://sqlservercode.blogspot.com/
Zvonko wrote:

Quote:

Originally Posted by

Hi!
>
This is my table:
>
CREATE TABLE [Query_Result] (
sifrob VARCHAR(13),
katbroj VARCHAR(15),
kol FLOAT
)
>
>
This is some values:
>
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 3)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501910', 'G-46935', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508122', 'G-46944', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508698', 'G-50314', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49502695', 'G-51201', 1)
>
As you can see some vaules are duplicated and only the 'kol' is
different. I would like to write a SELECT statements that will get all the
values from table, but on the duplicated ones that I get it once and the
'kol' column is sum of the two 'kol' values ( so if we have 499501879 the
'kol must be 4.
>
Please help.
>
Best regards,
Zvonko

Help with Reindexing all tables in a Database?

Hello,
I was provided this script:
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
However, I am not sure of what the variables:
table_name
information_schema.tables
base table
are. And if I do not have to give them values - how does the script
know what they are?
Am I required to fill them in? And if so with what data.
I do have access to the DB and can see all the table names and have
logged in as the database owner.
I tried running it as it and got this:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 2501, Level 16, State 1, Line 12
Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
Any suggestions for the forced into place back up dba?
Thanks,
TmuldMaybe you're simply running this script in the wrong database?
Make sure you've selected the correct database in the database selection
list in the menu bar before running the script.
It might even help if you placed a USE command at the top of the script so
that it ensures the correct DB is being used when the script is run, eg:
USE [yourdbname]
DECALRE @.TableName...
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1176418044.844493.175160@.y80g2000hsf.googlegroups.com...
> Hello,
> I was provided this script:
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> However, I am not sure of what the variables:
> table_name
> information_schema.tables
> base table
> are. And if I do not have to give them values - how does the script
> know what they are?
> Am I required to fill them in? And if so with what data.
> I do have access to the DB and can see all the table names and have
> logged in as the database owner.
> I tried running it as it and got this:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Server: Msg 2501, Level 16, State 1, Line 12
> Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
> Any suggestions for the forced into place back up dba?
> Thanks,
> Tmuld
>sql

Help with Reindexing all tables in a Database?

Hello,
I was provided this script:
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
However, I am not sure of what the variables:
table_name
information_schema.tables
base table
are. And if I do not have to give them values - how does the script
know what they are?
Am I required to fill them in? And if so with what data.
I do have access to the DB and can see all the table names and have
logged in as the database owner.
I tried running it as it and got this:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 2501, Level 16, State 1, Line 12
Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
Any suggestions for the forced into place back up dba?
Thanks,
TmuldMaybe you're simply running this script in the wrong database?
Make sure you've selected the correct database in the database selection
list in the menu bar before running the script.
It might even help if you placed a USE command at the top of the script so
that it ensures the correct DB is being used when the script is run, eg:
USE [yourdbname]
DECALRE @.TableName...
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1176418044.844493.175160@.y80g2000hsf.googlegroups.com...
> Hello,
> I was provided this script:
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> However, I am not sure of what the variables:
> table_name
> information_schema.tables
> base table
> are. And if I do not have to give them values - how does the script
> know what they are?
> Am I required to fill them in? And if so with what data.
> I do have access to the DB and can see all the table names and have
> logged in as the database owner.
> I tried running it as it and got this:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Server: Msg 2501, Level 16, State 1, Line 12
> Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
> Any suggestions for the forced into place back up dba?
> Thanks,
> Tmuld
>

Help with Query

I have two columns I want to compare, both varchar, and
would be in this format
Subject | Instructor
ACCT101-nnnnn | jcdoe
ACCT101-nnnnn | jcdoe
ACCT102-nnnnn | jcdoe
ACCT102-nnnnn | jcsmith
The subject is made up of (Table.Subject + '-' +
CourseNumber)
What I want to do, is query for those cases where the
Table.Subject(ie ACCT101)has the same instructor for each
instance. So if one teacher was teaching the same
section of the course my query would be like this, from
the above example:
Subject | Instructor
ACCT101 | jcdoe
Does anyone know how I could do this?
Thanks.
You could try this:
select
substring(Subject, charindex('-', Subject)) as Subject,
min(Instructor) as Instructor
from yourTable
group by substring(Subject, charindex('-', Subject))
having min(Instructor) = max(Instructor
or
select distinct
substring(Subject, charindex('-', Subject)) as Subject,
Instructor
from yourTable
where not exists (
select * from yourTable Tcopy
where substring(Tcopy.Subject, charindex('-', Tcopy.Subject)) =
substring(yourTable.Subject, charindex('-', yourTable.Subject))
and Tcopy.Instructor <> yourTable.Instructor
)
Just a suggestion. If the two pieces of the [Subject] column have
independent meanings in your table, you might consider keeping them in
separate columns to avoid having to use SUBSTRING to get the information
out.
Steve Kass
Drew University
spacejunk wrote:

>I have two columns I want to compare, both varchar, and
>would be in this format
>Subject | Instructor
>--
>ACCT101-nnnnn | jcdoe
>ACCT101-nnnnn | jcdoe
>ACCT102-nnnnn | jcdoe
>ACCT102-nnnnn | jcsmith
>--
>The subject is made up of (Table.Subject + '-' +
>CourseNumber)
>What I want to do, is query for those cases where the
>Table.Subject(ie ACCT101)has the same instructor for each
>instance. So if one teacher was teaching the same
>section of the course my query would be like this, from
>the above example:
>Subject | Instructor
>--
>ACCT101 | jcdoe
>--
>Does anyone know how I could do this?
>Thanks.
>
>

Help with Query

I have the following table:
TableName: Customers
ID - Integer
CustomerID - VarChar
Name - VarChar
LoanNo= Int
Amount = Money
ID CustomerID Name LoanNo Amount
1 Chuck1 Chuck 1 2.00
2 Mike1 Mike 1 4.00
3 Dinah1 Dinah 1 6.00
4 James1 James 1 1.00
5 James1 James 2 3.00
6 Chuck1 Chuck 2 5.00
What I want to do is return all the Customers but only their highest LoanNo
(like MAX(LoanNo)).
Example:
ID CustomerID Name LoanNo Amount
2 Mike1 Mike 1 4.00
3 Dinah1 Dinah 1 6.00
5 James1 James 2 3.00
6 Chuck1 Chuck 2 5.00
Thanks,
Chuck> What I want to do is return all the Customers but only their highest
> LoanNo
> (like MAX(LoanNo)).
One method is with a derived table. Untested example:
SELECT
Customers.ID,
Customers.Name,
Customers.LoanNo,
Customers.Amount
FROM Customers
JOIN
(SELECT
CustomerID,
MAX(LoanNo) AS LoanNo
FROM dbo.Customers
GROUP BY CustomerID) AS MaxLoanNos ON
MaxLoanNos.CustomerID = Customers.CustomerID AND
MaxLoanNos.LoanNo = Customers.LoanNo
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:eBja%23rl7GHA.3760@.TK2MSFTNGP02.phx.gbl...
>I have the following table:
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> Name - VarChar
> LoanNo= Int
> Amount = Money
> ID CustomerID Name LoanNo Amount
> 1 Chuck1 Chuck 1 2.00
> 2 Mike1 Mike 1 4.00
> 3 Dinah1 Dinah 1 6.00
> 4 James1 James 1 1.00
> 5 James1 James 2 3.00
> 6 Chuck1 Chuck 2 5.00
> What I want to do is return all the Customers but only their highest
> LoanNo
> (like MAX(LoanNo)).
> Example:
> ID CustomerID Name LoanNo Amount
> 2 Mike1 Mike 1 4.00
> 3 Dinah1 Dinah 1 6.00
> 5 James1 James 2 3.00
> 6 Chuck1 Chuck 2 5.00
> Thanks,
> Chuck
>

Help with Query

I have a table which holds system variables for a program I am writing.

It looks like this...

(AccountName VarChar(128), SettingName VarChar(20), Sequence Int, Setting VarChar(255))

There is a 'ALL USERS' AccountName and then individual users account names... such as DOMAIN\USERNAME. I Would like to Select all of the records containing 'ALL USERS' but overide the 'All USERS' entries with the individual user name settings where the SettingNames are equal.

AccountName SettingName Sequence Setting
ALL USERS DRIVESEARCH 1 D
ALL USERS DRIVESEARCH 2 G
ALL USERS DRIVESEARCH 3 H
ALL USERS DRIVESEARCH 4 I
ALL USERS DRIVESEARCH 5 J
ALL USERS JOBAUTHVIEWER 1 \\Path
ALL USERS OBJMDLWORD 1 wpfiles
ALL USERS RECENTFILESTODISPLAY 1 16
DOMAIN\USERRECENTFILESTODISPLAY 1 100

I would like...

ALL USERS DRIVESEARCH 1 D
ALL USERS DRIVESEARCH 2 G
ALL USERS DRIVESEARCH 3 H
ALL USERS DRIVESEARCH 4 I
ALL USERS DRIVESEARCH 5 J
ALL USERS JOBAUTHVIEWER 1 \\Path
ALL USERS OBJMDLWORD 1 wpfiles
DOMAIN\USERRECENTFILESTODISPLAY 1 100

Notice Recentfilestodisplay is down to one instead of two.
Is there a way to query this?something like this should do it shouldn't it?

select AccountName, SettingName, Sequence Int, Setting
from tblSystemVariables where AccountName = @.SpecificName
Union
select AccountName, SettingName, Sequence Int, Setting
from tblSystemVariables where
AccountName = 'ALL USERS'
and SettingName not in
(select SettingName from tblSystemVariables where AccountName = @.SpecificName)

where tblSystemVariables is your table and @.SpecificName is your specific domain/user combo.

Does that work?

Not really that elegant I know...|||Thank You! Works Great

Ended up with:

SELECT AccountName, SettingName, Sequence, Setting
FROM LoadSystemSettings
WHERE AccountName = 'DOMAIN\USER'
UNION
SELECT AccountName AS AccountName1, SettingName AS SettingName1, Sequence AS Sequence1, Setting AS Setting1
FROM LoadSystemSettings
WHERE AccountName = 'ALL USERS' AND SettingName NOT IN
(SELECT SettingName
FROM dbo.LoadSystemSettings
WHERE AccountName = 'DOMAIN\USER')

Was Missing the "NOT IN" Clause.

Kent|||Glad to be of service. Have a good one.

Help with query

Hi all,
I have the following table:
CREATE TABLE [dbo].[Company](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](100) NOT NULL,
[CompanyTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL)
These companies can have a variety of CompanyTypeID's. I want to
retrieve a different number of records for each CompanyTypeID in one
call.
Ex.:
DECLARE @.countType1 int
DECLARE @.countType2 int
DECLARE @.countType3 int
DECLARE @.countType4 int
DECLARE @.countType5 int
SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
UNION
SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
UNION
SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
UNION
SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
At this point, Im doing it all with a UNION and getting what I want.
IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
HAVING and use the @.count variables to return the desired number of
records per companytype.
Thanks,
VictorWhat's wrong with using the union?
you'll want to change it to a UNION ALL in the interim, though, since
there won't be any duplicates to eliminate.
ViLo wrote:
> Hi all,
>
> I have the following table:
> CREATE TABLE [dbo].[Company](
> [CompanyID] [int] IDENTITY(1,1) NOT NULL,
> [CompanyName] [varchar](100) NOT NULL,
> [CompanyTypeID] [int] NOT NULL,
> [Active] [bit] NOT NULL)
>
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
>
> Thanks,
> Victor
>|||ViLo (victor@.lamovidarecords.com) writes:
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
First of all, your query looks funny, as you say TOP 100 but as
there is no ORDER BY, you will get the 100, 200, etc as the optimizer
sees fit. Which will be neither random, nor deterministic.
If you are on SQL 2005 you can use TOP (@.count) if the arbitrary
choice is OK to you.
On SQL 2000, you probably need to use a temp table, particularly if you
want the TOP 200 by alphabet or so.
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

help with OPENXML

I'm having problem with inserting xml into sql server.
I have procedure:
------
CREATE PROC sp_insert_BGListaIzvj @.strXML varchar(8000)
AS
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
INSERT INTO BGListaIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj', 2)
WITH BGListaIzvj
INSERT INTO BGIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj/BGIzvj', 2)
WITH BGIzvj
EXEC sp_xml_removedocument @.hDoc
GO
------
and then I save xml data from dataset:
------
Dim connStr As String =
"Provider=SQLOLEDB;Server=-BERNARD-;database=form;Integrated Security=SSPI"
Dim cmd As New SqlXmlCommand(connStr)
cmd.RootTag = "ROOT"
cmd.CommandType = SqlXmlCommandType.Sql
cmd.CommandText = "SELECT L.ListaIzvjID, L.FirmaID, L.BlgID, L.RedBroj,
L.Razdoblje, L.PocStanje, L.ZavStanje, L.Fin, L.Godina, L.Mjesec, L.Sn,
I.ListaIzvjID AS ListaIzvjID2, I.BlgID AS BlgID2, I.Datum, I.Primljen,
I.Opis, I.Dokum, I.Konto, I.Duguje, I.Potrazuje, I.U, I.Jed, I.Odj, I.Tip,
I.BR, I.Godina AS Godina2 FROM BGListaIzvj L INNER JOIN (SELECT ListaIzvjID,
BlgID, Datum, Primljen, Opis, Dokum, Konto, Duguje, Potrazuje, U, Jed, Odj,
Tip, BR, Godina FROM BGIzvj) I ON I.ListaIzvjID = L.ListaIzvjID WHERE
(L.FirmaID = '13') FOR XML AUTO, ELEMENTS"
Dim DA As New SqlXmlAdapter(cmd)
Dim ds As New DataSet
DA.Fill(ds)
ds.WriteXml("D:\BGListaIzvj.xml")
------
and this is the xml file:
------
<?xml version="1.0" standalone="yes"?>
<ROOT>
<L>
<ListaIzvjID>16</ListaIzvjID>
<FirmaID>13</FirmaID>
<BlgID>7</BlgID>
<RedBroj>1</RedBroj>
<Razdoblje>01.01.2004 do 31.01.2004</Razdoblje>
<PocStanje>328.79</PocStanje>
<ZavStanje>143.18</ZavStanje>
<Fin>1</Fin>
<Godina>2004</Godina>
<Mjesec>01.2004</Mjesec>
<Sn>0</Sn>
<BGIzvj>
<ListaIzvjID2>16</ListaIzvjID2>
<BlgID2>7</BlgID2>
<Datum>2004-01-07T00:00:00</Datum>
<Primljen>-</Primljen>
<Opis>plaa za 12/03 za Amaliju Cigler</Opis>
<Dokum>Upl1</Dokum>
<Konto>1009</Konto>
<Duguje>222.4</Duguje>
<Potrazuje>0</Potrazuje>
<U>Zagreb</U>
<Jed>0</Jed>
<Odj>0</Odj>
<Tip>U</Tip>
<BR>1</BR>
<Godina2>2004</Godina2>
</BGIzvj>
</L>
<L>
<ListaIzvjID>17</ListaIzvjID>
<FirmaID>13</FirmaID>
<BlgID>7</BlgID>
<RedBroj>2</RedBroj>
<Razdoblje>01.06.2004 do 30.06.2004</Razdoblje>
<PocStanje>143.18</PocStanje>
<ZavStanje>1797.79</ZavStanje>
<Fin>1</Fin>
<Godina>2004</Godina>
<Mjesec>06.2004</Mjesec>
<Sn>0</Sn>
<BGIzvj>
<ListaIzvjID2>17</ListaIzvjID2>
<BlgID2>7</BlgID2>
<Datum>2004-06-15T00:00:00</Datum>
<Primljen>gotovina</Primljen>
<Opis>mat. trokove i isplata PN</Opis>
<Dokum>Upl2</Dokum>
<Konto>1009</Konto>
<Duguje>15000</Duguje>
<Potrazuje>0</Potrazuje>
<U>Zagreb</U>
<Jed>0</Jed>
<Odj>0</Odj>
<Tip>U</Tip>
<BR>2</BR>
<Godina2>2004</Godina2>
</BGIzvj>
</L>
</ROOT>
------
Now, I'm trying to save that xml into sql database with this:
Dim sr As New StreamReader("d:\BGListaIzvj.xml")
Dim strXML As String
strXML = sr.ReadToEnd
sr.Close()
cmd.Parameters("@.strXML").Value = strXML
SqlConnection1.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
SqlConnection1.Close()
but I'm getting error, XML Parsing error: An Invalid character found in text
content.
I tried to put <?xml version="1.0" encoding="utf-8" ?> in first row of xml
file, but it won't work.
And why is that <L> </L> there?
Can somebody help me?
thanks!
Try NTEXT for the type of @.strXML (I assume that the data is UTF-16
encoded).
The L element is there because of your FOR XML query.
Best regards
Michael
"green_eye" <bernard@.form.hr> wrote in message
news:clt9kv$cnq$1@.ls219.htnet.hr...
> I'm having problem with inserting xml into sql server.
> I have procedure:
> ------
> CREATE PROC sp_insert_BGListaIzvj @.strXML varchar(8000)
> AS
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
>
> INSERT INTO BGListaIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj', 2)
> WITH BGListaIzvj
>
> INSERT INTO BGIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj/BGIzvj', 2)
> WITH BGIzvj
>
> EXEC sp_xml_removedocument @.hDoc
> GO
> ------
>
> and then I save xml data from dataset:
> ------
> Dim connStr As String =
> "Provider=SQLOLEDB;Server=-BERNARD-;database=form;Integrated
> Security=SSPI"
> Dim cmd As New SqlXmlCommand(connStr)
> cmd.RootTag = "ROOT"
> cmd.CommandType = SqlXmlCommandType.Sql
>
> cmd.CommandText = "SELECT L.ListaIzvjID, L.FirmaID, L.BlgID, L.RedBroj,
> L.Razdoblje, L.PocStanje, L.ZavStanje, L.Fin, L.Godina, L.Mjesec, L.Sn,
> I.ListaIzvjID AS ListaIzvjID2, I.BlgID AS BlgID2, I.Datum, I.Primljen,
> I.Opis, I.Dokum, I.Konto, I.Duguje, I.Potrazuje, I.U, I.Jed, I.Odj, I.Tip,
> I.BR, I.Godina AS Godina2 FROM BGListaIzvj L INNER JOIN (SELECT
> ListaIzvjID, BlgID, Datum, Primljen, Opis, Dokum, Konto, Duguje,
> Potrazuje, U, Jed, Odj, Tip, BR, Godina FROM BGIzvj) I ON I.ListaIzvjID =
> L.ListaIzvjID WHERE (L.FirmaID = '13') FOR XML AUTO, ELEMENTS"
>
> Dim DA As New SqlXmlAdapter(cmd)
> Dim ds As New DataSet
> DA.Fill(ds)
>
> ds.WriteXml("D:\BGListaIzvj.xml")
>
> ------
>
> and this is the xml file:
> ------
> <?xml version="1.0" standalone="yes"?>
> <ROOT>
> <L>
> <ListaIzvjID>16</ListaIzvjID>
> <FirmaID>13</FirmaID>
> <BlgID>7</BlgID>
> <RedBroj>1</RedBroj>
> <Razdoblje>01.01.2004 do 31.01.2004</Razdoblje>
> <PocStanje>328.79</PocStanje>
> <ZavStanje>143.18</ZavStanje>
> <Fin>1</Fin>
> <Godina>2004</Godina>
> <Mjesec>01.2004</Mjesec>
> <Sn>0</Sn>
> <BGIzvj>
> <ListaIzvjID2>16</ListaIzvjID2>
> <BlgID2>7</BlgID2>
> <Datum>2004-01-07T00:00:00</Datum>
> <Primljen>-</Primljen>
> <Opis>plaa za 12/03 za Amaliju Cigler</Opis>
> <Dokum>Upl1</Dokum>
> <Konto>1009</Konto>
> <Duguje>222.4</Duguje>
> <Potrazuje>0</Potrazuje>
> <U>Zagreb</U>
> <Jed>0</Jed>
> <Odj>0</Odj>
> <Tip>U</Tip>
> <BR>1</BR>
> <Godina2>2004</Godina2>
> </BGIzvj>
> </L>
> <L>
> <ListaIzvjID>17</ListaIzvjID>
> <FirmaID>13</FirmaID>
> <BlgID>7</BlgID>
> <RedBroj>2</RedBroj>
> <Razdoblje>01.06.2004 do 30.06.2004</Razdoblje>
> <PocStanje>143.18</PocStanje>
> <ZavStanje>1797.79</ZavStanje>
> <Fin>1</Fin>
> <Godina>2004</Godina>
> <Mjesec>06.2004</Mjesec>
> <Sn>0</Sn>
> <BGIzvj>
> <ListaIzvjID2>17</ListaIzvjID2>
> <BlgID2>7</BlgID2>
> <Datum>2004-06-15T00:00:00</Datum>
> <Primljen>gotovina</Primljen>
> <Opis>mat. trokove i isplata PN</Opis>
> <Dokum>Upl2</Dokum>
> <Konto>1009</Konto>
> <Duguje>15000</Duguje>
> <Potrazuje>0</Potrazuje>
> <U>Zagreb</U>
> <Jed>0</Jed>
> <Odj>0</Odj>
> <Tip>U</Tip>
> <BR>2</BR>
> <Godina2>2004</Godina2>
> </BGIzvj>
> </L>
> </ROOT>
> ------
> Now, I'm trying to save that xml into sql database with this:
>
> Dim sr As New StreamReader("d:\BGListaIzvj.xml")
> Dim strXML As String
> strXML = sr.ReadToEnd
> sr.Close()
>
> cmd.Parameters("@.strXML").Value = strXML
> SqlConnection1.Open()
> Try
> cmd.ExecuteNonQuery()
> Catch ex As Exception
> MsgBox(ex.Message)
> End Try
> SqlConnection1.Close()
>
> but I'm getting error, XML Parsing error: An Invalid character found in
> text content.
> I tried to put <?xml version="1.0" encoding="utf-8" ?> in first row of
> xml file, but it won't work.
> And why is that <L> </L> there?
>
> Can somebody help me?
>
> thanks!
>
>
|||OK, thanks! It works with nText type, but I have some other problem.
With this procedure:
CREATE PROC sp_insert_BGListaIzvj @.strXML nText
AS
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
INSERT INTO BGListaIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj')
WITH BGListaIzvj
INSERT INTO BGIzvj
SELECT *
FROM OPENXML(@.hDoc, '//BGIzvj')
WITH BGIzvj
EXEC sp_xml_removedocument @.hDoc
GO
and this XML file:
<?xml version="1.0" standalone="yes"?>
<ROOT>
<BGListaIzvj ListaIzvjID="16" FirmaID="13" BlgID="7" RedBroj="1"
Razdoblje="01.01.2004 do 31.01.2004" PocStanje="328.79" ZavStanje="143.18"
Fin="1" Godina="2004" Mjesec="01.2004" Sn="0">
<BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
Primljen="-" Opis="plaa za 12/03 za Amaliju Cigler" Dokum="Upl1"
Konto="1009" Duguje="222.4" Potrazuje="0" U="Zagreb" Jed="0" Odj="0" Tip="U"
BR="1" Godina2="2004" />
<BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
Primljen="Ljubomir Babi" Opis="plaa za Amaliju Cigler 12/03" Dokum="Ispl1"
Konto="2304" Duguje="0" Potrazuje="222.4" U="Zagreb" Jed="3" Odj="0" Tip="I"
BR="1" Godina2="2004" />
</BGListaIzvj>
<BGListaIzvj ListaIzvjID="17" FirmaID="13" BlgID="7" RedBroj="2"
Razdoblje="01.06.2004 do 30.06.2004" PocStanje="143.18" ZavStanje="1797.79"
Fin="1" Godina="2004" Mjesec="06.2004" Sn="0">
<BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-15T00:00:00"
Primljen="gotovina" Opis="mat. trokove i isplata PN" Dokum="Upl2"
Konto="1009" Duguje="15000" Potrazuje="0" U="Zagreb" Jed="0" Odj="0" Tip="U"
BR="2" Godina2="2004" />
<BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-30T00:00:00"
Primljen="Ljubomir Babi" Opis="PN 3,4,5,7 -dnevnice" Dokum="Ispl5"
Konto="4600" Duguje="0" Potrazuje="595" U="Zagreb" Jed="3" Odj="0" Tip="I"
BR="5" Godina2="2004" />
</BGListaIzvj>
</ROOT>
I get rows in second table (BGIzvj) with NULL values in columns ListaIzvjID,
BlgID and Godina. These three tables are the same as in BGListaIzvj table.
Is that a problem? Is there way to fix this?
Thanks!
|||Hi Bernard
What are the schemas of your relational tables?
Since you get the rows, your path expressions find the nodes that map to
rows, but it looks like that the relative paths implied by the relational
schema does not find any values for the nodes where you get NULL.
This probably means that your Schema names and attribute names do not fit.
Best regards
Michael
"Bernard" <bernard@.form.hr> wrote in message
news:cm0576$r2e$1@.ls219.htnet.hr...
> OK, thanks! It works with nText type, but I have some other problem.
> With this procedure:
> ----
> CREATE PROC sp_insert_BGListaIzvj @.strXML nText
> AS
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
>
> INSERT INTO BGListaIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj')
> WITH BGListaIzvj
>
> INSERT INTO BGIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '//BGIzvj')
> WITH BGIzvj
>
> EXEC sp_xml_removedocument @.hDoc
> GO
> ----
>
> and this XML file:
> ----
> <?xml version="1.0" standalone="yes"?>
> <ROOT>
> <BGListaIzvj ListaIzvjID="16" FirmaID="13" BlgID="7" RedBroj="1"
> Razdoblje="01.01.2004 do 31.01.2004" PocStanje="328.79" ZavStanje="143.18"
> Fin="1" Godina="2004" Mjesec="01.2004" Sn="0">
> <BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
> Primljen="-" Opis="plaa za 12/03 za Amaliju Cigler" Dokum="Upl1"
> Konto="1009" Duguje="222.4" Potrazuje="0" U="Zagreb" Jed="0" Odj="0"
> Tip="U" BR="1" Godina2="2004" />
> <BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
> Primljen="Ljubomir Babi" Opis="plaa za Amaliju Cigler 12/03"
> Dokum="Ispl1" Konto="2304" Duguje="0" Potrazuje="222.4" U="Zagreb" Jed="3"
> Odj="0" Tip="I" BR="1" Godina2="2004" />
> </BGListaIzvj>
> <BGListaIzvj ListaIzvjID="17" FirmaID="13" BlgID="7" RedBroj="2"
> Razdoblje="01.06.2004 do 30.06.2004" PocStanje="143.18"
> ZavStanje="1797.79" Fin="1" Godina="2004" Mjesec="06.2004" Sn="0">
> <BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-15T00:00:00"
> Primljen="gotovina" Opis="mat. trokove i isplata PN" Dokum="Upl2"
> Konto="1009" Duguje="15000" Potrazuje="0" U="Zagreb" Jed="0" Odj="0"
> Tip="U" BR="2" Godina2="2004" />
> <BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-30T00:00:00"
> Primljen="Ljubomir Babi" Opis="PN 3,4,5,7 -dnevnice" Dokum="Ispl5"
> Konto="4600" Duguje="0" Potrazuje="595" U="Zagreb" Jed="3" Odj="0" Tip="I"
> BR="5" Godina2="2004" />
> </BGListaIzvj>
> </ROOT>
> ----
>
> I get rows in second table (BGIzvj) with NULL values in columns
> ListaIzvjID, BlgID and Godina. These three tables are the same as in
> BGListaIzvj table. Is that a problem? Is there way to fix this?
>
> Thanks!
>
>
>

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 inserting multiple records using a CSV value.

I have the Temporary table:

ItemDetailID (int)
FieldID (int)
FieldTypeID (int)
ReferenceName (Varchar(250))
[Value] (varChar(MAX))

in one instance Value might equal: "1, 2, 3, 4"

This only happens when FieldTypeID = 5.

So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the TableFieldListValues(ItemDetailID, [value])

I have created a function to split the [Value] into a table of INTs

Any Advice?

If your function returns a table type data, loop through the table and do an INSERT for each row.

|||

I would love to do that... but... I can program my way out of a box using C#... with SQL.. i could probably take a baby step to the bathroom :\

Do you know of any links/resources/source that could show me how? I've googled like crazy, but no luck :(

|||

You could do an :

(1) Declare a table variable with an additional column Processed tinyint.

(2) INSERT INTO @.table

SELECT dbo.someFunction

(3) Loop through the table.

WHILE EXISTS (SELECT * FROM @.table Where Procesed = 0)

Begin

Get the values from the @.table

Insert into the Original table

update @.table set processed = 1 Where Condition

End

|||

I think I understand...

While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?

|||

RTernier:

I think I understand...

While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?

Yes.

|||

That would work. Now another question (Yea, I'm not that strong in SQL :P )

While I go through the WHILE loop,

Is there a way I can grab the values of the loop I'm going through?

Example:

WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

====

I could do this right:

WHILE EXISTS(SELECT * FROM @.Table T WHERE Processed = 0)
BEGIN

PRINT T.MyColumn
END

===

if not, how can I directly access the values from T?

|||

If the values returned by your function are unique, then you can use a MIN(Id) to get each id, else you can add an IDENTITY column to your table variable and use that to navigate through each row.

Decare @.rowid int

WHILE ...

Begin

SELECT @.rowid = MIN(id) FROM @.Table Where Processed = 0

INSERT INTO ...original table

Update @.t Set Processed = 1 Where Id = @.Rowid

End