adding a unique identity to each row of the output?
-
i have run into yet another problem with my final year project.
I have this stored procedure but the trouble is i cant use it in a datagrid or list because none of the outputs are unique. is there any way of adding a unique identity to each row of the output?
I get the feeling this isnt a very good idea but is it possible?
edit:the first column should be unique but the application only see's it as a date.
declarations - finding @consultancytypeid, @level, @clientid
SELECT top 10 (left(Thedate,11) + ' - ' + consultantname ) 'Consultant Name', Thedate 'Date'
FROM ben_WeekEndsAndHolidays, ben_vw_consultant_ability
WHERE dayname <> 'Saturday'
AND dayname <> 'Sunday'
AND Thedate > getdate()
AND consultancytypeid = @consultancytypeid
AND consultancytypelevel >= @level
AND consultantid NOT IN
-- no disputes
(
SELECT consultantid
FROM ben_client_consultant_unavailability
WHERE clientid = @clientid
)
--check they are not on holiday
AND consultantid NOT IN
(
SELECT consultantid
FROM ben_consultant_availability
WHEREdateunavailable = Thedate
)
--check they are not already booked
AND consultantid NOT IN
(
SELECT consultantid
FROMben_consultancy
WHERE consultancydate = Thedate
)
ORDER BY Date
GO
-
hi, ben
whenever i come across this problem, i create a table that has an identity columns and then select the data into that, as follows :-
create table add_unique_identity
(unique_identifier int identity(1,1) , {rest of fields....})
this starts the unique_identifier field at 1, and increments each row by 1.
you then just need to select from this table (temporary or otherwise)
hope this helps
Paul
-
DECLARE @t int
SET @t = 0
SELECT @t = @t + 1 'Unique ID',
{rest of query here}
That will add row numbers to each row. It obviously doesn't persist the ID, so it can't be used to refer back to the row later. But, it might work for your purposes.
-
You could also try an easy addition to your select statement:
SELECT top 10 (left(Thedate,11) + ' - ' + consultantname ) AS 'Consultant Name', Thedate AS 'Date', COUNT(*) AS RowID....
I wasn't born stupid - I had to study.
-
Marshall,
Man, if that worked, this forum probably wouldn't even exist... life would be much too simple...
. You can't mix variable assignements with data-retrieval.Server: Msg 141, Level 15, State 1, Line 7
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Also, you need either "" or [] around Unique ID for it to be a column alias... the single quotes will not work as you have it posted.
Paul's suggestion of pumping it into a table with an idenity column is the one I normally use... pretty fast too...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Don't think it's going to be quite that simple... you'll need to add a GROUP BY in order to include an aggragate function in the Select list in the presence of other column names... I'm also thinking that the count will always return a 1 in this particular query.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ben,
I think Paul has steered you in the right direction... I've added his suggestion to your code... give it a shot...
--===== If the temp table exists, drop it
IF OBJECT_ID('TempDB..#MyTemp') IS NOT NULL
DROP TABLE #MyTemp
--===== Your original query with a couple of mods
SELECT IDENTITY(INT,1,1) AS RowNum,
top 10 (left(Thedate,11) + ' - ' + consultantname ) 'Consultant Name', Thedate 'Date'
INTO #MyTemp
FROM ben_WeekEndsAndHolidays, ben_vw_consultant_ability
WHERE dayname <> 'Saturday'
AND dayname <> 'Sunday'
AND Thedate > getdate()
AND consultancytypeid = @consultancytypeid
AND consultancytypelevel >= @level
AND consultantid NOT IN
-- no disputes
(
SELECT consultantid
FROM ben_client_consultant_unavailability
WHERE clientid = @clientid
)
--check they are not on holiday
AND consultantid NOT IN
(
SELECT consultantid
FROM ben_consultant_availability
WHERE dateunavailable = Thedate
)
--check they are not already booked
AND consultantid NOT IN
(
SELECT consultantid
FROM ben_consultancy
WHERE consultancydate = Thedate
)
ORDER BY Date
--===== Then, this returns the record set
SELECT *
FROM #MyTemp
ORDER BY RowNum
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
hi,
firstly thanks for all the replies!!
Marshall Smith's suggestion threw up some errors i couldnt understand.
Farrell Keough's suggestion kept mentioning group by error messages which i couldnt get rid of even if i added group by.
I have decided to go the route ripg1011 mentioned, about the temporary table. i wasnt sure on the syntax to do it, so thanks Jeff for the help! Ill be trying it out again later tonight so ill let you know if it works.
thanks guys
edit:
tried it and it worked great!
I had to change the select top 10 from where it was to the bottom (where it selects out the new table), but apart from that it all worked.
when on the crap uni hosting server it didnt tho, it doesnt allow temporary tables. i ended up swapping the temp table to a normal table and that works great.
any thing i should beware of using a normal table rather than i temporary one?
ben
-
hi ben,
no, no issues using a permanent table (as long as you remember to truncate it)
Paul
-
ok, I have another problem with the hosting. i have no permissions to drop the table. i can truncate the table but when i run the script again, substituting truncate for the drop command, i get an error message
There is already an object named 'ben_MyTemp1' in the database.
im presuming this is from the select into line.
i will try and get permissions to drop tables on monday from our lecturer but if i cant is there any way i can get round this?
it only needs to demo once so maybe...and one of these is a dirty hack...but
maybe use select into but alter it somehow to select into an existing table?(if that is indeed the error)
or create a new table each time the script is run. so dbo.temp1, then dbo.temp2 etc.
i know this is very dirty but i will only have to run it about 5 times.
any good suggestions welcome!
--===== If the temp table exists, drop it
truncate table ben_MyTemp1
--===== Your original query with a couple of mods
SELECT IDENTITY(INT,1,1) AS RowNum,
(left(Thedate,11) + ' - ' + consultantname ) 'Consultant Name', Thedate 'Date'
INTO ben_MyTemp1
FROM ben_WeekEndsAndHolidays, ben_vw_consultant_ability
WHERE dayname 'Saturday'
AND dayname 'Sunday'
AND Thedate > getdate()
AND consultancytypeid = @consultancytypeid
AND consultancytypelevel >= @level
AND consultantid NOT IN
-- no disputes
(
SELECT consultantid
FROM ben_client_consultant_unavailability
WHERE clientid = @clientid
)
--check they are not on holiday
AND consultantid NOT IN
(
SELECT consultantid
FROM ben_consultant_availability
WHERE dateunavailable = Thedate
)
--check they are not already booked
AND consultantid NOT IN
(
SELECT consultantid
FROM ben_consultancy
WHERE consultancydate = Thedate
)
ORDER BY Date
sussed it. thanks again for the help!
-
Since the permanent table now exists, you could make the following changes and it should work...
--===== Truncate the existing table
TRUNCATE TABLE ben_MyTemp1
--===== Your original query with a couple of mods
INSERT INTO ben_MyTemp1([Consultant Name],[Date])
SELECT
--IDENTITY(INT,1,1) AS RowNum,
(left(Thedate,11) + ' - ' + consultantname ) AS [Consultant Name],
Thedate AS [Date]
FROM ben_WeekEndsAndHolidays, ben_vw_consultant_ability
WHERE dayname <> 'Saturday'
AND dayname <> 'Sunday'
AND Thedate > getdate()
AND consultancytypeid = @consultancytypeid
AND consultancytypelevel >= @level
AND consultantid NOT IN
-- no disputes
(
SELECT consultantid
FROM ben_client_consultant_unavailability
WHERE clientid = @clientid
)
--check they are not on holiday
AND consultantid NOT IN
(
SELECT consultantid
FROM ben_consultant_availability
WHERE dateunavailable = Thedate
)
--check they are not already booked
AND consultantid NOT IN
(
SELECT consultantid
FROM ben_consultancy
WHERE consultancydate = Thedate
)
ORDER BY Date
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
yeh i managed to figure it out in the end
cheers
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply