Help with COUNT(*)

Dear SQL,

I want to count the number of records, so I tried this:

SELECT COUNT(*) AS RecordCount
FROM Categories
WHERE Active = 1
ORDER BY Show_Order ASC

But it gives me an error:
error 8126: Column name 'Categories.Show_Order' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.

How can I make it work ? (I must ORDER it...)SELECT COUNT(*) will return a single row with a single column containing an integer value. What are you expecting an ORDER BY to sort??

Terri|||As the error says, you cant do a count without a group. Here is my suggestion:

SELECT COUNT(fieldname) AS RecordCount FROM Categories WHERE active = 1 GROUP BY fieldname ORDER BY show_order

Instead of counting all columns just use one field. A Count requires a grouping even if there isnt anything to group. For instance you have a field called id that is a primary key. Group by. Order is ALWAYS at the end and the default is ASC so no need for ASC.|||As the error says, you cant do a count without a group.
That's not accurate. You can certainly do a COUNT without explicitly giving a grouping.

What the error is saying is that if you want use an ORDER BY clause, the expression being ordered by must exist in the resultset. With this in mind, the example you've given will not work because show_order does not exist in the resultset.

And, in order to add an expression to the resultset using an aggregate function such as COUNT, you need a GROUP BY clause.

In this case, this is likely what is needed but I am not sure because more information is needed from the original poster:

SELECT Show_Order, COUNT(*) AS RecordCount
FROM Categories
WHERE Active = 1
GROUP BY Show_Order
ORDER BY Show_Order ASC

And also, I think that explicitly indicating the sort direction ("ASC") is good practice because you never know when default behaviors might change. But that's a personal preference. :-)

Terri|||Hello again & thank god 4 this forum :-)

I have now succeeded in returning the number of records
but as Terri said - when I use GROUP BY it seems to limit my recordset to only one record,

I need this SP to be very efficient, so I like to SELECT only once, as U can see on the SP (below),
right now I use another SELECT at the end to determine the number of records to return...

Please let me know how to improve it or how to COUNT the records on the first selection
Thanks in advanced, Yovav.

/*================================================================================*/
/* Get categories (All / Titles / Subtitles) */
/*================================================================================*/
CREATE PROCEDURE Admin_Categories_Get
/*
' Usage example:
' ~~~~~~~~~~~
AdoCmd.CommandType = adCmdStoredProc
AdoCmd.CommandText = "Admin_Categories_Get"
' Return parameter comes first and can be used after recordset is closed
AdoCmd.Parameters.Append AdoCmd.CreateParameter("RETURN", adInteger, adParamReturnValue, 4)
' 0 (All), 1 (All active) 2 (All active compact), 10 (All titles), 11 (All active titles), 20 (All subtitles), 21 (All active subtitles)
AdoCmd.Parameters.Append AdoCmd.CreateParameter("@.ShowType", adTinyInt, adParamInput, 1, 0)
Set CategoriesRS = AdoCmd.Execute
CategoriesRS.Close
Response.Write("Return value = " &CStr(AdoCmd.Parameters.Item("RETURN").Value))
*/
@.ShowType tinyint
AS
DECLARE @.RecordCount int
IF @.ShowType = 0 -- (All)
SELECT *
FROM Categories
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 1 -- (All active)
SELECT *
FROM Categories
WHERE Active = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 2 -- (All active compact)
SELECT Category_ID, Title, Name_Eng, Name_Heb
FROM Categories
WHERE Active = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 10 -- (All titles)
SELECT *
FROM Categories
WHERE Title = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 11 -- (All active titles)
SELECT *
FROM Categories
WHERE Active = 1 /* True */ AND Title = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 20 -- (All subtitles)
SELECT *
FROM Categories
WHERE Title = 0 /* False */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 21 -- (All active subtitles)
SELECT *
FROM Categories
WHERE Active = 1 /* True */ AND Title = 0 /* False */
ORDER BY Show_Order ASC
-- Count *ALL* records on table Categories
SELECT @.RecordCount = COUNT(*) FROM Categories
RETURN @.RecordCount
GO

|||...

oh dear goodness.

In the spirit of the holidays..

Why don't you just send in Active and Title as parameters? It appears to me that they're bits, and two bits are smaller in size than one integer.|||it wont help, coz sometimes I need to do things according to the ShowType
+
my main problem was how to return the COUNT of records together with the recordset...

help with arithmetic overflow error with insted of update trigger

Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
When I perform an update on certain records, i.e.:
UPDATE mytableview
SET field1 = 1
WHERE userid = 1234
I am finding that *some* user id's result in the follwing error:
"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."
mytable view has a number of data fileds. All the data fields are of type
smalldatatime.
When i compare the user record of a userid that causes an error to one that
doesnt cause an error, the dates do vary, where some date fields have NULL's
or correctly formated smalldatatime values (yes I know about the restriction
of smalldatetime to range between 1900 and 2079).
The odd thing is that even if i am updating a non-date field within the
view, the above arithmetic error still occurs.
My trigger looks like the following:
CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
DECLARE @.mydate datetime
SELECT @.mydate = GETDATE()
UPDATE mytable SET
field1 = ISNULL(inserted.field1, 0),
field2 = ISNULL(inserted.field2, 0),
field3 = ISNULL(inserted.field3, 0),
date1 = inserted.date1,
date2 = @.date+30
FROM inserted
WHERE mytable.userid = inserted.userid
Am i getting this error because i am mixing a date2 fiels (which is of type
smalldatetime) with @.date (which is of type datetime) ?
Any help most appreciated.Do you need the extra ms or time range? If not try:
DECLARE @.mydate smalldatetime
SELECT @.mydate = GETDATE()
SELECT @.mydate
HTH
Jerry
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||Also, drop the SELECT @.mydate -- was just for testing. Basically using the
SMALLDATETIME data type instead of DATETIME.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
> Do you need the extra ms or time range? If not try:
> DECLARE @.mydate smalldatetime
> SELECT @.mydate = GETDATE()
> SELECT @.mydate
> HTH
> Jerry
> "PWalker" <pwalker@.nospam.com> wrote in message
> news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||Sorry, I meant to say:
mytable view has a number of *date* fields. All the *date* fields are of
type
smalldatatime.
.. late night
cheers, peter
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||thanks ill try that when i get to work
I hope its as obvious as changing smalldatetime to datetime!
cheers, peter

> Also, drop the SELECT @.mydate -- was just for testing. Basically using
> the SMALLDATETIME data type instead of DATETIME.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
>

help with a User-Defined function to return a string from multiple records

I need some help with writing a User-Defined function in SQL Server 2000.
I would like to return a space-delimited string, which contains the column
data of several records from a table.
Here's an example:
table_fruit
id textid
-- --
1 APPLE
2 BANANA
4 ORANGE
8 PEAR
16 PLUM
My SQL query string uses a bitwise-AND (&) to determine which records to
return.
SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
So, for example, if I pass in the parameter @.param_fruits = 13, then I get
the following records back:
APPLE
ORANGE
PEAR
What I'd like to have is a User-Defined function that returns the data in a
concatenated space-delimited string like this:
APPLE ORANGE PEAR
I need help with writing this function. Thanks very much.To get a space delimited string, you should modify the statement as follows:
=====
-- Your function declarations etc
DECLARE @.returnString VARCHAR(8000)
SET @.returnString = ''
SELECT @.returnString = @.returnString + ' ' + textid
FROM table_fruit WHERE ([id] & @.param_fruits) > 0
RETURN (@.returnString)
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott A. Keen" <noreply@.scottkeen.com> wrote in message
news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>I need some help with writing a User-Defined function in SQL Server 2000.
> I would like to return a space-delimited string, which contains the column
> data of several records from a table.
> Here's an example:
> table_fruit
> id textid
> -- --
> 1 APPLE
> 2 BANANA
> 4 ORANGE
> 8 PEAR
> 16 PLUM
>
> My SQL query string uses a bitwise-AND (&) to determine which records to
> return.
> SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> So, for example, if I pass in the parameter @.param_fruits = 13, then I get
> the following records back:
> APPLE
> ORANGE
> PEAR
> What I'd like to have is a User-Defined function that returns the data in
> a
> concatenated space-delimited string like this:
> APPLE ORANGE PEAR
> I need help with writing this function. Thanks very much.
>|||In addition, this method is unreliable and should be done on the client
side
As an alternative take a look at Erland's (if I remember well) example
CREATE PROCEDURE get_company_names_inline @.customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inline_split_me(@.customers) s ON C.CustomerID = s.Value
go
EXEC get_company_names_inline 'ALFKI,BONAP,CACTU,FRANK'
CREATE FUNCTION inline_split_me (@.param varchar(7998)) RETURNS TABLE AS
RETURN(SELECT substring(',' + @.param + ',', Number + 1,
charindex(',', ',' + @.param + ',', Number + 1) -
Number - 1)
AS Value
FROM Numbers
WHERE Number <= len(',' + @.param + ',') - 1
AND substring(',' + @.param + ',', Number, 1) = ',')
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
drop table numbers
drop function inline_split_me
drop proc get_company_names_inline
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
> follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||Thanks very much! Worked great.
I had done the same query but had not declared the VARCHAR large enough, and
didn't use the SET statement to initialize the variable.
Thanks
Scott
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
column
get
in
>sql

Help with a stored procedure

Is it possible to do something like this? I have all the records in #rsltTable (it has an Index and OrderID) I want to be efficient, so I am trying to use the OrderID's from #rsltTable to SELECT all of the records that I need to return. Right now I am getting "Msg 102, Level 15, State 1, Procedure pe_getAppraisals3, Line 119 Incorrect syntax near '>'."

SELECT * FROM Orders WHERE OrderIDIN(SELECT OrderIDFROM #rsltTableWHERE ID=> @.l_FirstRecordAND ID<= @.l_LastRecord)

IF you need more of the SQL I can post it.

Thank You,
Jason

Greater than or equal to is >=, not =>.

SELECT * FROM Orders WHERE OrderIDIN(SELECT OrderIDFROM #rsltTableWHERE ID>= @.l_FirstRecordAND ID<= @.l_LastRecord)

HTH,
Ryan

|||Last time I program after having teeth pulled...

Help with a simple query please

I have Two Tables, TableA and TableB, both containing a common field,
Feild1.

How do I find all records in TableA, where Field1 is not in TableB?

Regards,
CiarnSELECT A.*
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.col1 = B.col1
WHERE B.col1 IS NULL

--
David Portas
SQL Server MVP
--|||Try

SELECT
A.*

FROM
TableA A
LEFT JOIN TableB B ON
A.Field1 = B.Field1|||Sorry, didn't read it properly. David's answer is correct.

Help with a query

Hello,

I need some assistance with a query that i am trying to build.

A table contains records which consitute an employee's shift.

There are 7 'Default' records for each employee, along with any number of additional records which will override the default record if the date in this record equals the date the form is displaying.

For instance: today is Friday 7/28 and the employee name is Joe; if there are no additional records out in the table for Joe for 7/28, then we will grab his default record. If there is a record for Joe for 7/28, then we will use this record to get his shift start and end times.

Here are the fields in the two records and what they may contain:

Name: Joe, XDate:"7/28/2006", XDay:Fri, StartTime:xxxx, EndTime:xxxx

Name: Joe, XDate:"Default", XDay:Fri, StartTime:xxxxx, EndTime:xxxxx

and this situation can occur for serveral employees.

So again, I need to grab the record with a XDate that matches todays date, if that does not exist then I need to grab the record with the XDate that has the word "Default" in it.

By the way, XDate is a text field and not a Date datatype field.

*****oh and one other thing i forgot to mention. If the Date of 7/28 is not found, then i would use the day value of that date "Fri" to grab the default start and end times.

Thanks for your help!

StrangeMike:

oh and one other thing i forgot to mention. If the Date of 7/28 is not found, then i would use the day value of that date "Fri" to grab the default start and end times.

Sorry I'm not very clear with this point: how will you use the XDay date? Previously you said 'Default will be used if no match XDate is found, now how will you use the XDay value? Anyways despite the use of XDay data, you may try the code below:

declare @.d smalldatetime
set @.d='2006-07-29'

select * from test
where name='Joe'
and XDate= CASE WHEN (SELECT count(*) FROM test
WHERE XDate=CONVERT(varchar(12),DATEPART(mm,@.d))+'/'+
CONVERT(varchar(12),DATEPART(dd,@.d))+'/'+
CONVERT(varchar(12),DATEPART(yy,@.d)))>0
THEN CONVERT(varchar(12),DATEPART(mm,@.d))+'/'+
CONVERT(varchar(12),DATEPART(dd,@.d))+'/'+
CONVERT(varchar(12),DATEPART(yy,@.d))
ELSE 'Default'
END

|||

Hi Lori_Jay,

The xDay is the clue to tell me which 'Default' record to take Mon-Sun.

xDate can contain the word "Default" or an actual Date "7/28/2006".

There are 7 'Default' records. So if there is no record with an actual date..like "7/28/2006",

then I need to go after the default record for the day I am currently displaying, and that is how xDay come into play. If today is a Friday, then I grab the default record where xDay = "Fri".

I know it's a bit confusing.

Thank you for your reply.

|||

You'll need to change "MyTable" to your table name, and set @.date to the textbox's .Text property.

SELECT t1.Name,ISNULL(t1.StartTime,(SELECT StartTime FROM MyTable t2 WHERE t2.Name=e.Name and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM MyTable t2 WHERE t2.Name=e.Name and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime

(SELECT DISTINCT Name,@.date AS XDate,CASE DATEPART(dw,CAST(@.date as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM MyTable) e

LEFT JOIN MyTable t1 ON (t1.Name=e.Name and t1.XDate=e.XDate)

|||

Wow nice query. I'm a little confused by the "set @.date to the textbox's .Text property." though.

I am passing two parameters to this stored query that populates a datagrid, those being..Date and Day. Here is the current query which returns the record for the date, but it also includes the default record for the same day, I removed some unrealted fields for simplicity: Using your query how can I modify this one? Thanks

SELECT tblPhotographerShifts.RecId, tblPhotographerShifts.Photographer, tblPhotographerShifts.StartTime, tblPhotographerShift.EndTime, tblPhotographerShifts.xDate, tblPhotographerShifts.Day
FROM tblPhotographerShifts
WHERE (((tblPhotographerShifts.Photographer)<>"TBA") AND ((tblPhotographerShifts.xDate)=[@.ByDate])) OR (((tblPhotographerShifts.xDate)="Default") AND ((tblPhotographerShifts.Day)=[@.Day]))
ORDER BY tblPhotographerShifts.Photographer, tblPhotographerShifts.xDate;

|||

SELECT t1.Photographer,ISNULL(t1.StartTime,(SELECT StartTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime

(SELECT DISTINCT Photographer,@.ByDate AS XDate,CASE DATEPART(dw,CAST(@.ByDate as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM tblPhotographerShifts) e

LEFT JOIN tblPhotographerShifts t1 ON (t1.Photographer=e.Photographer and t1.XDate=e.XDate)

WHERE t1.Photographer<>'TBA'

ORDER BY t1.Photographer

I don't use @.Day, since I calculate it in the query from @.ByDate already.

|||

Motley thanks for your help with this, I've gotta say I don't know how you thought of something like this. I basically just tried cutting and pasting your query into the Sql View of the query. When I tried to go to design I got this error:

"The Select includes a reserve word or argument name that is misspelled or missing, or the puncuation is incorrect"

I gave it a shot looking at it, but to be honest there are parts of this query I have never even seen before. Do you know what may be incorrect?

Thank you.

|||

Heh, I forgot the word FROM, try this:

SELECT

t1.Photographer,ISNULL(t1.StartTime,(SELECT StartTimeFROM tblPhotographerShifts t2WHERE t2.Photographer=e.Photographerand t2.XDate='Default'AND t2.XDay=e.Dow))AS StartTime,ISNULL(t1.EndTime,(SELECT EndTimeFROM tblPhotographerShifts t2WHERE t2.Photographer=e.Photographerand t2.XDate='Default'AND t2.XDay=e.Dow))AS EndTime

FROM

(

SELECTDISTINCT Photographer,@.ByDateAS XDate,CASEDATEPART(dw,CAST(@.ByDateasdatetime))WHEN 1THEN'Sun'WHEN 2THEN'Mon'WHEN 3THEN'Tue'WHEN 4THEN'Wed'WHEN 5THEN'Thu'WHEN 6THEN'Fri'WHEN 7THEN'Sat'ENDAS DowFROM tblPhotographerShifts) e

LEFT

JOIN tblPhotographerShifts t1ON(t1.Photographer=e.Photographerand t1.XDate=e.XDate)

WHERE

t1.Photographer<>'TBA'

ORDER

BY t1.Photographer|||

Getting closer... Here is a new error:

"Wrong number of arguments used with function in query expression ISNULL(t1.StartTime, (Select StartTime FROM tblPhotogpraherShifts t2 where t2.Photographer=e.Phtographer and t2.xDate='Default' and t2.Xday=e.dow))"

|||

You have a typo somewhere, although I did fix one other bug, here is my test script:

USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
--DROP TABLE tblPhotographerShifts
GO
CREATE TABLE [dbo].[tblPhotographerShifts](
[Photographer] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XDate] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XDay] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartTime] varchar(10) NULL,
[EndTime] varchar(10) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('Me','8/1/2006',NULL,'9:30','10:30')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('Me','Default','Wed','9:00','10:00')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('You','Default','Tue','10:00','11:00')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('You','Default','Wed','10:30','11:30')
INSERT INTO [tblPhotographerShifts]([Photographer],[XDate],[XDay],[StartTime],[EndTime])
VALUES ('You','8/1/2006',NULL,'11:30','12:30')

DECLARE @.ByDate varchar(20)

SET @.ByDate='8/1/2006'
SELECT e.Photographer,ISNULL(t1.StartTime,(SELECT StartTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime ,e.dow
FROM
(SELECT DISTINCT Photographer,@.ByDate AS XDate,CASE DATEPART(dw,CAST(@.ByDate as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM tblPhotographerShifts) e
LEFT JOIN tblPhotographerShifts t1 ON (t1.Photographer=e.Photographer and t1.XDate=e.XDate)
WHERE e.Photographer<>'TBA'
ORDER BY t1.Photographer

SET @.ByDate='8/2/2006'
SELECT e.Photographer,ISNULL(t1.StartTime,(SELECT StartTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS StartTime,ISNULL(t1.EndTime,(SELECT EndTime FROM tblPhotographerShifts t2 WHERE t2.Photographer=e.Photographer and t2.XDate='Default' AND t2.XDay=e.Dow)) AS EndTime ,e.dow
FROM
(SELECT DISTINCT Photographer,@.ByDate AS XDate,CASE DATEPART(dw,CAST(@.ByDate as datetime))
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat' END AS Dow FROM tblPhotographerShifts) e
LEFT JOIN tblPhotographerShifts t1 ON (t1.Photographer=e.Photographer and t1.XDate=e.XDate)
WHERE e.Photographer<>'TBA'
ORDER BY t1.Photographer

Results:

Me 9:30 10:30 Tue
You 11:30 12:30 Tue

Me 9:00 10:00 Wed
You 10:30 11:30 Wed

|||

It could be an MS Access restriction. I am just cutting and pasting into an SQL View and it is giving me the error. I'll keep trying some different things to see if I can get Access to accept your query.

Thanks

|||Motley, thanks for your assistance on this, between you and another site I have a query that is working for me.|||Heh, it would have helped if you mentioned that you were using Access. Or posted it in the AccessDataSource forums.

Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need some
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?
What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>
|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
...
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:

> I am writing a vb.net application that calls a stored procedure and need some
> help.
> I am writting the procedure to check if multiple records exists and the only
> way I can figure it out is to use @.@.RowCount, but can't get the right result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>
|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:

> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> some
> only
> result,
>
>
|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
[vbcol=seagreen]
> I assume you want to update the row if it already exists and insert the row
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return[vbcol=seagreen]
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
of[vbcol=seagreen]
message[vbcol=seagreen]
need[vbcol=seagreen]
the[vbcol=seagreen]
becuase[vbcol=seagreen]
|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =
Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:

> Again, the COUNT(*) should do exactly what you need here... can you tell me
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders table
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> multiple
> these
> way
> return
> of
> message
> need
> the
> becuase
>
>
|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
[vbcol=seagreen]
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know.
>
>
> "Adam Machanic" wrote:
|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>

Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need som
e
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result
,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of thes
e
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:

> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> some
> only
> result,
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of thes
e
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
[vbcol=seagreen]
> I assume you want to update the row if it already exists and insert the ro
w
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
>|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return[vbcol=seagreen]
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
>
of[vbcol=seagreen]
message[vbcol=seagreen]
need[vbcol=seagreen]
the[vbcol=seagreen]
becuase[vbcol=seagreen]|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =
Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:

> Again, the COUNT(*) should do exactly what you need here... can you tell m
e
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders tab
le
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> multiple
> these
> way
> return
> of
> message
> need
> the
> becuase
>
>|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
[vbcol=seagreen]
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of th
e
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know
.
>
>
> "Adam Machanic" wrote:
>|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>|||What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
..
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:

> I am writing a vb.net application that calls a stored procedure and need s
ome
> help.
> I am writting the procedure to check if multiple records exists and the on
ly
> way I can figure it out is to use @.@.RowCount, but can't get the right resu
lt,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>

Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need some
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
...
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:
> I am writing a vb.net application that calls a stored procedure and need some
> help.
> I am writting the procedure to check if multiple records exists and the only
> way I can figure it out is to use @.@.RowCount, but can't get the right result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:
> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > I am writing a vb.net application that calls a stored procedure and need
> some
> > help.
> >
> > I am writting the procedure to check if multiple records exists and the
> only
> > way I can figure it out is to use @.@.RowCount, but can't get the right
> result,
> > please help.
> >
> > What I have now is
> >
> > if exists(select c_driver from cartons where orderid = @.orderid and
> > @.@.Rowcount = 1)
> > update...
> >
> >
> > I tried using the following but got an error in my vb application becuase
> > the Procedure was returning rows
> >
> > select c_driver from cartons where orderid = @.orderid
> > if @.@.Rowcount = 1
> > update...
> >
> > Is there any way to use the above query without returning rows to VB?
> >
> > Is there a way to write an exists to query @.@.Rowcount?
> >
> >
> >
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
> I assume you want to update the row if it already exists and insert the row
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
> > I am writing a vb.net application that calls a stored procedure and need some
> > help.
> >
> > I am writting the procedure to check if multiple records exists and the only
> > way I can figure it out is to use @.@.RowCount, but can't get the right result,
> > please help.
> >
> > What I have now is
> >
> > if exists(select c_driver from cartons where orderid = @.orderid and
> > @.@.Rowcount = 1)
> > update...
> >
> >
> > I tried using the following but got an error in my vb application becuase
> > the Procedure was returning rows
> >
> > select c_driver from cartons where orderid = @.orderid
> > if @.@.Rowcount = 1
> > update...
> >
> > Is there any way to use the above query without returning rows to VB?
> >
> > Is there a way to write an exists to query @.@.Rowcount?
> >
> >
> >|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
> > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
of
> > the last operation... I have a feeling you really want:
> >
> > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > update ...
> >
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
message
> > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > I am writing a vb.net application that calls a stored procedure and
need
> > some
> > > help.
> > >
> > > I am writting the procedure to check if multiple records exists and
the
> > only
> > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > result,
> > > please help.
> > >
> > > What I have now is
> > >
> > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > @.@.Rowcount = 1)
> > > update...
> > >
> > >
> > > I tried using the following but got an error in my vb application
becuase
> > > the Procedure was returning rows
> > >
> > > select c_driver from cartons where orderid = @.orderid
> > > if @.@.Rowcount = 1
> > > update...
> > >
> > > Is there any way to use the above query without returning rows to VB?
> > >
> > > Is there a way to write an exists to query @.@.Rowcount?
> > >
> > >
> > >
> >
> >
> >|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:
> Again, the COUNT(*) should do exactly what you need here... can you tell me
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders table
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> > I tried the count() but I don't get what I need
> >
> > what I need to do is determin is if a single driver is assigned to
> multiple
> > cartons within an order. for Instance say driver #1 was assigned to Carton
> > 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
> these
> > cartons are in Order #1000
> >
> > Order# Carton# Driver#
> > 1000 100 1
> > 1000 101 24
> > 1000 102 24
> >
> > What I need to get to is this without the select (becuase doing it this
> way
> > returns an error in my vb.net application because the select wants to
> return
> > rows)
> >
> > select c_driver from Cartons where orderid = @.orderid
> > if @.@.RowCount = 1
> > --Only one driver exists for this order
> > Update orders set oDriver = (select distinct c_driver from cartons
> > where orderid = @.orderid)
> >
> > if @.@.RowCount > 1
> > -- Multiple Driver exists for this Order
> >
> >
> > "Adam Machanic" wrote:
> >
> > > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
> of
> > > the last operation... I have a feeling you really want:
> > >
> > > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > > update ...
> > >
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
> message
> > > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > > I am writing a vb.net application that calls a stored procedure and
> need
> > > some
> > > > help.
> > > >
> > > > I am writting the procedure to check if multiple records exists and
> the
> > > only
> > > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > > result,
> > > > please help.
> > > >
> > > > What I have now is
> > > >
> > > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > > @.@.Rowcount = 1)
> > > > update...
> > > >
> > > >
> > > > I tried using the following but got an error in my vb application
> becuase
> > > > the Procedure was returning rows
> > > >
> > > > select c_driver from cartons where orderid = @.orderid
> > > > if @.@.Rowcount = 1
> > > > update...
> > > >
> > > > Is there any way to use the above query without returning rows to VB?
> > > >
> > > > Is there a way to write an exists to query @.@.Rowcount?
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid => Cartons.Orderid based on the #of cartons shipped with the order. All of the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know.
>
>
> "Adam Machanic" wrote:
> > Again, the COUNT(*) should do exactly what you need here... can you tell me
> > why this won't work for you:
> >
> >
> > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > BEGIN
> > Update orders set oDriver = (select distinct c_driver from cartons
> > where orderid = @.orderid)
> > END
> > ELSE
> > BEGIN
> > -- do something else...
> > END
> >
> >
> > ... Given that, however, I should ask why you're updating your orders table
> > with the driver from the cartons table? Why denormalize your data like
> > that?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> > news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> > > I tried the count() but I don't get what I need
> > >
> > > what I need to do is determin is if a single driver is assigned to
> > multiple
> > > cartons within an order. for Instance say driver #1 was assigned to Carton
> > > 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
> > these
> > > cartons are in Order #1000
> > >
> > > Order# Carton# Driver#
> > > 1000 100 1
> > > 1000 101 24
> > > 1000 102 24
> > >
> > > What I need to get to is this without the select (becuase doing it this
> > way
> > > returns an error in my vb.net application because the select wants to
> > return
> > > rows)
> > >
> > > select c_driver from Cartons where orderid = @.orderid
> > > if @.@.RowCount = 1
> > > --Only one driver exists for this order
> > > Update orders set oDriver = (select distinct c_driver from cartons
> > > where orderid = @.orderid)
> > >
> > > if @.@.RowCount > 1
> > > -- Multiple Driver exists for this Order
> > >
> > >
> > > "Adam Machanic" wrote:
> > >
> > > > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
> > of
> > > > the last operation... I have a feeling you really want:
> > > >
> > > > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > > > update ...
> > > >
> > > >
> > > >
> > > > --
> > > > Adam Machanic
> > > > SQL Server MVP
> > > > http://www.sqljunkies.com/weblog/amachanic
> > > > --
> > > >
> > > >
> > > > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
> > message
> > > > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > > > I am writing a vb.net application that calls a stored procedure and
> > need
> > > > some
> > > > > help.
> > > > >
> > > > > I am writting the procedure to check if multiple records exists and
> > the
> > > > only
> > > > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > > > result,
> > > > > please help.
> > > > >
> > > > > What I have now is
> > > > >
> > > > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > > > @.@.Rowcount = 1)
> > > > > update...
> > > > >
> > > > >
> > > > > I tried using the following but got an error in my vb application
> > becuase
> > > > > the Procedure was returning rows
> > > > >
> > > > > select c_driver from cartons where orderid = @.orderid
> > > > > if @.@.Rowcount = 1
> > > > > update...
> > > > >
> > > > > Is there any way to use the above query without returning rows to VB?
> > > > >
> > > > > Is there a way to write an exists to query @.@.Rowcount?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid => Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>

Help using Lookup

Hi

I am trying to use lookup to see if a item esists in my table ( 3 key fields ). If the lookup fails I want to insert the records. If it succeeds I have put a recordcount to catch the items that are not required. I don't think that I understand the settings for failed rows. I have tried setting the Configure Error Output to redirect, but this does not seem to work. I have the below errors.

[SQL Server Destination [151]] Error: Unable to prepare the SSIS bulk insert for data insertion.

[DTS.Pipeline] Error: component "SQL Server Destination" (151) failed the pre-execute phase and returned error code 0xC0202071.

Can someone please advise me how to set up this component to work for my application

Thanks

ADG

SQL Server Destinations only work on the machine that have the actual SQL Server installed locally. Is this the case?|||

Hi Phil

I have SQL Server 2005 Developer Edition installed on a stand alone machine at the moment. Previous two task flows use a SQL Server Destination (same table) and work OK.

I did not know that I could not use a SQL Server Destination on a network. Eventually I will migrate my solution to one of our group servers, currently I am developing the solution, or rather battling to learn SQL server. I guess that maybe I should use another type of destination once the above bug is ironed out.

Regards

ADG

|||

Bug fixed

I deleted the Look up and set it up again and all is well. Not sure what i did first time, ( I fiddled with too many settings I suspect

Help TSQL, The latest records?

How do I find the latest record?
Original data
C_ID M_ID DATE SCORE
-- -- -- --
2467 14843 2005-09-27 45
2467 55877 2005-09-26 89
7392 12365 2005-09-26 98
7392 199128 2005-09-11 78
7412 96143 2005-09-21 68
7412 201850 2005-09-01 86
Desired Result:
C_ID M_ID DATE SCORE
-- -- -- --
2467 14843 2005-09-27 45
7392 12365 2005-09-26 98
7412 96143 2005-09-21 68
DLL:
CREATE TABLE Splat(C_ID INT,
M_ID INT,
[DATE] varchar(10),
[SCORE] tinyint)
INSERT INTO Splat
SELECT 2467, 55877, '2005-09-26' , 89
UNION
SELECT 2467, 14843, '2005-09-27', 45
UNION
SELECT 7392, 12365, '2005-09-26', 98
UNION
SELECT 7392, 199128, '2005-09-11', 78
UNION
SELECT 7412, 96143, '2005-09-21', 68
UNION
SELECT 7412, 201850, '2005-09-01', 86
Thanks,
CulamA common approach:
SELECT *
FROM tbl t1
WHERE t1.date_col = ( SELECT MAX( t2.datecol )
FROM tbl t2
WHERE t2.c_id = t1.c_id );
Make sure you do define keys in your table, if you do not have one.
Anith|||Thank you Sir, you are life-saver!
"Anith Sen" wrote:

> A common approach:
> SELECT *
> FROM tbl t1
> WHERE t1.date_col = ( SELECT MAX( t2.datecol )
> FROM tbl t2
> WHERE t2.c_id = t1.c_id );
> Make sure you do define keys in your table, if you do not have one.
> --
> Anith
>
>

Help to write query...

Hi !

There is one table tCustomers. It has following columns: ID, Name, Code...

By the mistake in this table has appeared incorrect records (duplicates).

How can I write the query to find them ?

I tried:

Select c.ID ID1,s.ID ID2, c.NAME NAME1,s.NAME NAME2, c.Code C1, s.Code C2, From tCustomers c, tCustomers s
where c.Code=s.Code and c.ID <> s.ID

But the result is not that I expected

Hi,

If only the ID field constain duplicates, you can use the following query:

SELECT tCustomers.ID, tCustomers.Name, tCustomers.Code
FROM tCustomers
WHERE (((tCustomers.ID) In (SELECT [ID] FROM [tCustomers] GROUP BY [ID] HAVING Count(*)>1 )))
ORDER BY tCustomers.ID;

If all the fields are duplicated, use the following query:

SELECT tCustomers.ID, tCustomers.Name, tCustomers.Code, Count(tCustomers.ID) NumberOfDups
FROM tCustomers
GROUP BY tCustomers.ID, tCustomers.Name, tCustomers.Code
HAVING (((Count(tCustomers.ID))>1));

Hope this helps

Help to deleting n numbers of records

Hi
One of our users have by mistake created a number of dublicate records in
the database. Now I need to delete the "faulty" records from the table and
just keep one of them. The records are linked to a customer table, so I have
a Recordid column in the table that I can use to group on. E.g. if I in the
table have 10 records with RecordID 1, I need to delete 9 of them and keep
1, 8 records with RecordID 2 I need to delete 7 and keep 1 etc.
I'll have to search the customer table to find the recordID's that is used
to link to the child table, so my plan is to use a cursor to find these and
put them into a variable. I'll then use this to find the record(s) in the
child table, get the number of records and then either delete them one by
one until I've only 1 pr. RecordID left or use SELECT Top x based on the
number of records found with each recordid and then delete all but 1 record.
It's not a huge number of records I need to delete so from a practical
and/or performance point of wiev it's not critical how I do it. It's more
that I'm currious to hear if my approach is the best one or if any of you
have any other ideas?
TIA
Regards
SteenTo answer this properly we'll need to know the keys and constraints in
your tables. Please post DDL (CREATE TABLE) and some sample data
(INSERTs) if you want help with the actual code.
You missed out one critical step from your solution: Add a new unique
constraint so that this can't happen again.
David Portas
SQL Server MVP
--|||Hi
First of all, it's a vendor application, so I can't change anything in the
database or application. I this case it's not a problem though, since it's
ok to create several records as they did, but in this case it's just because
they had some problems with a printer and therefore thay ran a wizard
several times which generated a number of records that where baiscally same.
It's these records thay now want to get deleted.
The 2 tables that's involved is called Lejer and Note.
CREATE TABLE [Lejer] (
[EjendomNr] [EjendomNr] NOT NULL ,
[LejemaalNr] [LejemaalNr] NOT NULL ,
[LejerNr] [LejerNr] NOT NULL ,
[LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
....and about 200 more column definitions
CREATE TABLE [Note] (
[NoteID] [RecordID] NOT NULL ,
[Tabelnavn] [TabelNavn] NOT NULL ,
[RecordID] [RecordID] NOT NULL ,
[Dato] [Dato] NOT NULL ,
[NoteType] [KodeId] NOT NULL ,
....and some more column definitions.
The fields that links the tables are Lejer.LejerID and Note.RecordID.
Below is ans example of sample data
Lejer:
Ejendomnr Lejemaalnr Lejernr LejerID
1 1 1 1000
1 2 2 1001
1 3 3 1002
2 1 1 1003
2 2 2 1004
3 1 1 1005
3 2 2 1006
Note
NoteID RecordID NoteType
1 1000 29000
2 1000 29000
3 1000 29000
4 1000 29000
5 1001 29000
6 1001 29000
7 1001 29000
8 1002 29000
9 1002 29000
10 1002 29000
11 1003 29000
12 1003 29000
I'd like to find the notes where the type is e.g. 29000 and are linked a
record in the Lejer table with the Ejendomnr of e.g. 1.
and then delete all notes but 1.
In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note records with NoteID 1 to 10. Out of these I'd like to delete 3 of the
ones with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with
RecordID 1002.
As mentioned in my original post, I could get all the RecordID's into a
cursor and then e.g. count the number of occurences for each of them and
then do a delete n-1 times. I just don't know if that's the smartest way to
do it or if there's a better approach?
Regards
Steen
David Portas wrote:
> To answer this properly we'll need to know the keys and constraints in
> your tables. Please post DDL (CREATE TABLE) and some sample data
> (INSERTs) if you want help with the actual code.
> You missed out one critical step from your solution: Add a new unique
> constraint so that this can't happen again.
> --
> David Portas
> SQL Server MVP|||How about
Delete Note
Where NoteType = '29000'
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Here I'm assuming that a given Note.RecordId must exist in Lejer.LejerId.
However, it is possible that this is not the case and you want to ensure tha
t
the record does exist in the Lejer table then you could add an Exists clause
like so:
Delete Note
Where NoteType = '29000'
And Exists(
Select *
From Lejer As L1
Where L1.LegerId = Note.RecordId
)
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Obviously, you should execute this code carefully to ensure that it is produ
ce
the results you want before you commit against production data.
HTH
Thomas
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Hi
> First of all, it's a vendor application, so I can't change anything in the
> database or application. I this case it's not a problem though, since it's
ok
> to create several records as they did, but in this case it's just because
they
> had some problems with a printer and therefore thay ran a wizard several t
imes
> which generated a number of records that where baiscally same. It's these
> records thay now want to get deleted.
> The 2 tables that's involved is called Lejer and Note.
>
> CREATE TABLE [Lejer] (
> [EjendomNr] [EjendomNr] NOT NULL ,
> [LejemaalNr] [LejemaalNr] NOT NULL ,
> [LejerNr] [LejerNr] NOT NULL ,
> [LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
> .....and about 200 more column definitions
>
> CREATE TABLE [Note] (
> [NoteID] [RecordID] NOT NULL ,
> [Tabelnavn] [TabelNavn] NOT NULL ,
> [RecordID] [RecordID] NOT NULL ,
> [Dato] [Dato] NOT NULL ,
> [NoteType] [KodeId] NOT NULL ,
> ....and some more column definitions.
> The fields that links the tables are Lejer.LejerID and Note.RecordID.
>
> Below is ans example of sample data
> Lejer:
> Ejendomnr Lejemaalnr Lejernr LejerID
> 1 1 1 1000
> 1 2 2 1001
> 1 3 3 1002
> 2 1 1 1003
> 2 2 2 1004
> 3 1 1 1005
> 3 2 2 1006
> Note
> NoteID RecordID NoteType
> 1 1000 29000
> 2 1000 29000
> 3 1000 29000
> 4 1000 29000
> 5 1001 29000
> 6 1001 29000
> 7 1001 29000
> 8 1002 29000
> 9 1002 29000
> 10 1002 29000
> 11 1003 29000
> 12 1003 29000
>
> I'd like to find the notes where the type is e.g. 29000 and are linked a
> record in the Lejer table with the Ejendomnr of e.g. 1.
> and then delete all notes but 1.
> In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note
> records with NoteID 1 to 10. Out of these I'd like to delete 3 of the ones
> with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with Record
ID
> 1002.
> As mentioned in my original post, I could get all the RecordID's into a cu
rsor
> and then e.g. count the number of occurences for each of them and then do
a
> delete n-1 times. I just don't know if that's the smartest way to do it or
if
> there's a better approach?
> Regards
> Steen
>
> David Portas wrote:
>|||Hi Thomas
Thanks for you input. That was another way of doing it than I had in my
mind. I'll try it out in my test db.
Regards
Steen
Thomas Coleman wrote:
> How about
> Delete Note
> Where NoteType = '29000'
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Here I'm assuming that a given Note.RecordId must exist in
> Lejer.LejerId. However, it is possible that this is not the case and
> you want to ensure that the record does exist in the Lejer table then
> you could add an Exists clause like so:
> Delete Note
> Where NoteType = '29000'
> And Exists(
> Select *
> From Lejer As L1
> Where L1.LegerId = Note.RecordId
> )
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Obviously, you should execute this code carefully to ensure that it
> is produce the results you want before you commit against production
> data.
> HTH
>
> Thomas
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...

help Simple question

Dear all,
I need to do some analysis on particular records.
For that I have one Table named EVENT in which I have a field named CODE.
From that field value I can get many entries with same CODE value.
Is there a way to extract the value of CODE field which occurs more often in
a table ?
thanks
regards
SergeHi
create table #test
(
col int not null primary key,
code int
)
insert into #test values (1,100)
insert into #test values (2,100)
insert into #test values (3,200)
insert into #test values (4,100)
insert into #test values (5,200)
insert into #test values (6,800)
select top 1 code,count(*)as occur from #test
group by code
order by occur desc
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:2FC5F7A1-C1F4-4033-82B9-8F84EF8FFDD7@.microsoft.com...
> Dear all,
> I need to do some analysis on particular records.
> For that I have one Table named EVENT in which I have a field named CODE.
> From that field value I can get many entries with same CODE value.
> Is there a way to extract the value of CODE field which occurs more often
> in
> a table ?
> thanks
> regards
> Serge

Help setting up a trace for a sp

Hello,
I haven't really used the trace program much. I am having a problem with
a sp that we created it seems to skip records during the cursor. I want to
setup a trace on that specific store procedure only is there a way to do
this? Thanks in advance. The name of the sp is called "datacollection"
Jake
I'm a little confused as to what you want to trace. Would you explain
more? Sounds like you want to trace the progress through a SP. Meaning you
want to show every command it executes? Sounds like you what you want to do
is use the Transact-SQL debugger. Is this correct? I have not used the
T-SQL debugger, so I really can't help you if this is what you are looking
for. But here is an msdn article that might get you started:
http://msdn.microsoft.com/library/de...qldebugger.asp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jake" <rondican@.hotmail.com> wrote in message
news:O7WS7hTWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I haven't really used the trace program much. I am having a problem
with
> a sp that we created it seems to skip records during the cursor. I want to
> setup a trace on that specific store procedure only is there a way to do
> this? Thanks in advance. The name of the sp is called "datacollection"
> Jake
>
|||Gregory,
I was looking at the profiler tool that comes with SQL. You are correct
in that I want to see each line being processed for the sp. I will take a
look at this and see if I can tweak it to work for our needs. Thanks.
Jake
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O3SQ8uTWEHA.3012@.tk2msftngp13.phx.gbl...
> I'm a little confused as to what you want to trace. Would you explain
> more? Sounds like you want to trace the progress through a SP. Meaning
you
> want to show every command it executes? Sounds like you what you want to
do
> is use the Transact-SQL debugger. Is this correct? I have not used the
> T-SQL debugger, so I really can't help you if this is what you are looking
> for. But here is an msdn article that might get you started:
>
http://msdn.microsoft.com/library/de...qldebugger.asp
>
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Jake" <rondican@.hotmail.com> wrote in message
> news:O7WS7hTWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> with
to
>
|||You might also want to review the "Transact-SQL Debugger Window" topic in
BOL.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jake" <rondican@.hotmail.com> wrote in message
news:OM8ThzTWEHA.1048@.tk2msftngp13.phx.gbl...
> Gregory,
> I was looking at the profiler tool that comes with SQL. You are
correct[vbcol=seagreen]
> in that I want to see each line being processed for the sp. I will take a
> look at this and see if I can tweak it to work for our needs. Thanks.
> Jake
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O3SQ8uTWEHA.3012@.tk2msftngp13.phx.gbl...
> you
to[vbcol=seagreen]
> do
looking
>
http://msdn.microsoft.com/library/de...qldebugger.asp[vbcol=seagreen]
> ----
> --
> ----
> --
problem[vbcol=seagreen]
want[vbcol=seagreen]
> to
do
>