1) I'm using MS Sql Server 2000 JDBC driver at WSAD5.1.1.
2) When I try to update the database in my DAO by using:
ps.executeUpdate();
3) Server throws the exception:
java.sql.SQLException: [Microsoft][SQL Server 2000 Driver for
JDBC][SQL Server]Must declare the variable '@.P7where'.
Anything is wrong with the MS JDBC driver with IBM WSAD? shall I set
something somewhere?
Experts, please help!
Show the JDBC code that creates the statement, sets the parameters, and calls the execute().
nauna wrote:
> 1) I'm using MS Sql Server 2000 JDBC driver at WSAD5.1.1.
> 2) When I try to update the database in my DAO by using:
> ps.executeUpdate();
> 3) Server throws the exception:
> java.sql.SQLException: [Microsoft][SQL Server 2000 Driver for
> JDBC][SQL Server]Must declare the variable '@.P7where'.
> Anything is wrong with the MS JDBC driver with IBM WSAD? shall I set
> something somewhere?
> Experts, please help!
Showing posts with label declare. Show all posts
Showing posts with label declare. Show all posts
Monday, March 19, 2012
Saturday, February 11, 2012
@variable in SELECT ... WHERE ... IN clause
Is there a way to create a query that can be like:
DECLARE @.group_id_list varchar(100)
SET @.group_id_list='100,101,150'
SELECT * FROM abc WHERE abc_id IN (@.group_id_list)
I get the error "Syntax error converting the varchar value '100,101,150' to a column of data type int.
Do I need to resort to a dynamic SQL statement?
You do.|||Not if you don't want to. In many cases I prefer to use a udf that I've created that takes a comma-delimited varchar and returns a table. Then you can either join on the table to limit your results, or you can use IN (SELECT id FROM Split(@.param,DEFAULT) alias1) in your where clause. The second param into my Split UDF is what the separator is (default is comma).|||Thank you. I actually ended up doing something similar (never thought of using a UDF):
DECLARE @.groups TABLE (group_id int)
I ran a while loop inserting the values into the @.groups table, then used:
SELECT * FROM abc WHERE abc_id IN (SELECT group_id FROM @.groups)
I don't know if it's efficient, but my dynamic SQL statement was going to exceed 14k in length!
@NUM_MEMBERS = EXEC (USE +@X + ; SELECT COUNT(Member_Name) FROM Logins) -HOW?
DECLARE @.X VARCHAR(10)
DECLARE @.NUM_MEMBERS SMALLINT
SELECT @.X = 'other_db'
DECLARE @.NUM_MEMBERS SMALLINT
SELECT @.X = 'other_db'
@.NUM_MEMBERS = EXEC ('USE ' +@.X + '; SELECT COUNT(Member_Name) FROM Logins')
I get an error when I try to store the result in @.NUM_MEMBERS. :/ I've been looking all over for the correct syntax. Can anyone help?
ERROR:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@.NUM_MEMBERS'.
thx n advits probably better just to use sp_executesql, eh?|||I think you have to to get results back in variables from a dynamic SQL string.
@local variable (newbie)
I am trying to run a query.
Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName
Open ColNames_csr
Fetch Next From ColNames_csr into @.FieldName
While ...
Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end
@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).
I am trying to roll through all the columns and see what the value is for @.fieldname
What am i missing?
Thanks
LJOriginally posted by LittleJonny
I am trying to run a query.
Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName
Open ColNames_csr
Fetch Next From ColNames_csr into @.FieldName
While ...
Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end
@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).
I am trying to roll through all the columns and see what the value is for @.fieldname
What am i missing?
Thanks
LJ
Query below returns value of @.FieldName - no field in table. Think about dynamic query ...
(Select @.FieldName from Contacts)|||Dynamic Query? Im not sure I understand.
@.FieldName = 'Name'
Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)
Print @.FieldValue
Always Prinst "Name"
How do I get the data in Contacts.@.FieldName?
Thanks
LJ|||Originally posted by LittleJonny
Dynamic Query? Im not sure I understand.
@.FieldName = 'Name'
Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)
Print @.FieldValue
Always Prinst "Name"
How do I get the data in Contacts.@.FieldName?
Thanks
LJ
Dynamic query is something like this:
create table test(id int, code varchar(10))
go
insert test values(1,'A')
insert test values(2,'B')
insert test values(3,'C')
go
create proc retvalue(@.sql varchar(8000),@.result varchar(50) output)
as
declare @.res varchar(50)
create table #tmp(res varchar(50))
insert #tmp exec(@.sql)
select @.result=res from #tmp
return
go
declare @.res varchar(50),@.sql varchar(8000),@.field varchar(50)
set @.field='code'
set @.sql='select '+@.field+' from test'
exec retvalue @.sql,@.res output
select @.res
Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName
Open ColNames_csr
Fetch Next From ColNames_csr into @.FieldName
While ...
Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end
@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).
I am trying to roll through all the columns and see what the value is for @.fieldname
What am i missing?
Thanks
LJOriginally posted by LittleJonny
I am trying to run a query.
Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName
Open ColNames_csr
Fetch Next From ColNames_csr into @.FieldName
While ...
Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end
@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).
I am trying to roll through all the columns and see what the value is for @.fieldname
What am i missing?
Thanks
LJ
Query below returns value of @.FieldName - no field in table. Think about dynamic query ...
(Select @.FieldName from Contacts)|||Dynamic Query? Im not sure I understand.
@.FieldName = 'Name'
Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)
Print @.FieldValue
Always Prinst "Name"
How do I get the data in Contacts.@.FieldName?
Thanks
LJ|||Originally posted by LittleJonny
Dynamic Query? Im not sure I understand.
@.FieldName = 'Name'
Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)
Print @.FieldValue
Always Prinst "Name"
How do I get the data in Contacts.@.FieldName?
Thanks
LJ
Dynamic query is something like this:
create table test(id int, code varchar(10))
go
insert test values(1,'A')
insert test values(2,'B')
insert test values(3,'C')
go
create proc retvalue(@.sql varchar(8000),@.result varchar(50) output)
as
declare @.res varchar(50)
create table #tmp(res varchar(50))
insert #tmp exec(@.sql)
select @.result=res from #tmp
return
go
declare @.res varchar(50),@.sql varchar(8000),@.field varchar(50)
set @.field='code'
set @.sql='select '+@.field+' from test'
exec retvalue @.sql,@.res output
select @.res
@@sqlstatus error
Is @.@.sqlstatus not a global variable. Why am I getting this error.
Code Snippet
declare @.obj_type varchar(50);
declare @.obj_name varchar(50);
declare @.schema varchar(100);
select @.schema = 'new';
declare schema_object cursor for
select obj.type, obj.name
from sys.objects obj join sys.schemas s on (s.schema_id = obj.schema_id)
where s.name = @.schema
open schema_object
fetch schema_object into @.obj_type, @.obj_name;
while (@.@.sqlstatus = 0)
begin
fetch schema_object into @.obj_type, @.obj_name;
end
close schema_object
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@.@.sqlstatus".
I think u must use @.@.Fetch_Status instead of @.@.sqlstatus but I'm not sure about that
just give a try
Thursday, February 9, 2012
@@RowCount problem
Hi I'm opening a RecordSet using the following code :
declare tcrl cursor FAST_FORWARD for SELECT TSample.ISmpCode
FROM TCertResults TSample
WHERE (TSample.ISmpShortCode ='24/12359')
Open trcl
What I want to be able to get a count of this recordset. Been thru the TSQL help and it's pointing me toward the @.@.RowCount command but I can't seem to get this working.
Any help would be apprieciated@.@.ROWCOUNT returns the number of rows affected by the last statement. Use the @.@.CURSOR_ROWS function to receive the number of qualifying rows in the last opened cursor.
BTW - do you really need cursor? It heavily hits DB performance.|||I agree,
Wont SELECT COUNT(*) FROM TCertResults TSample
WHERE TSample.ISmpShortCode ='24/12359' do the trick?|||No cursrors...No cursors...
There is precious little you can do without them...
Describe what your trying to do...|||The cursor is a lot more complicated than posted but the basic gist is that the results of the cursor will be 'fetched' into variables which will then be used to update other tables, etc. Then the variables will be repopulated using the fetch next command. When updating the other tables with the results of the original cursor, one of the values that I need to write is total amount of records from the original cursor.
So I need the recordcount as soon as the cursor is open. But as I'm sure you've gathered I haven't got a clue how to get this!|||Makes no sense to me...(being sober sucks...)
So you want to write the count of the entire result set, for every row in the cursor?
That seems to be a derivation of a thing...
Anyway...do this before you open the cursor and just use the local variable..
DECLARE @.x int
SELECT @.x=COUNT(*)
FROM TCertResults TSample
WHERE ISmpShortCode ='24/12359'
Still, you could do all of this without a cursor.
Good luck|||Looks for me you can use temp tables instead of cursors...
But if you are decided to stay with them - didn't the @.@.CURSOR_ROWS suits you? Why - any reason? error? sth other?|||Using the Cursor_Row function always gave me a answer of -1. To be fair though I'm new to TSQL so my syntax could be totally wrong. And to prove it here's my syntax
declare tcrl cursor FAST_FORWARD for SELECT dbo.TSample.ISmpCode
FROM dbo.TSample INNER JOIN
dbo.TCertResults ON dbo.TSample.ISmpN = dbo.TCertResults.ISmpN
WHERE (dbo.TSample.ISmpShortCode = '24/12359')
Open tcrl
PRINT @.@.CURSOR_ROWS|||You don't my count solution?|||Your solution works great thank you.
I was just continuing the thread in the hope that someone could tell me what the correct syntax @.@.Cursor_Rows function was.
Cheers|||Look in BOL (Books Online):
@.@.CURSOR_ROWS
Returns the number of qualifying rows currently in the last cursor opened on the connection. To improve performance, Microsoft SQL Server can populate large keyset and static cursors asynchronously. @.@.CURSOR_ROWS can be called to determine that the number of the rows that qualify for a cursor are retrieved at the time @.@.CURSOR_ROWS is called.
Return value Description
-m The cursor is populated asynchronously. The value returned (-m) is the number of rows currently in the keyset.
-1 The cursor is dynamic. Because dynamic cursors reflect all changes, the number of rows that qualify for the cursor is constantly changing. It can never be definitely stated that all qualified rows have been retrieved.
0 No cursors have been opened, no rows qualified for the last opened cursor, or the last-opened cursor is closed or deallocated.
n The cursor is fully populated. The value returned (n) is the total number of rows in the cursor.
Don't use Cursors...|||Originally posted by SexualChocolate
Using the Cursor_Row function always gave me a answer of -1. To be fair though I'm new to TSQL so my syntax could be totally wrong. And to prove it here's my syntax
declare tcrl cursor FAST_FORWARD for SELECT dbo.TSample.ISmpCode
FROM dbo.TSample INNER JOIN
dbo.TCertResults ON dbo.TSample.ISmpN = dbo.TCertResults.ISmpN
WHERE (dbo.TSample.ISmpShortCode = '24/12359')
Open tcrl
PRINT @.@.CURSOR_ROWS
The Great Holy Online Book says:
"If you receive '-1' as return from @.@.CURSOR_ROWS that it means:
The cursor is dynamic. Because dynamic cursors reflect all changes, the number of rows that qualify for the cursor is constantly changing. It can never be definitely stated that all qualified rows have been retrieved. You can try with Brett Kaiser's count solution. So says The Great Holy Online Book"
Again:
<ghost_voice>Beware of cursors... uuuuaahahahahahaaa....</ghost_voice>|||like I say I'm fairly new to all this. I'm using a cursor because I don't know of another was to open a recordset and step thru it. If there is an alternative then please point me at it|||Count - to determine no of records,
temp tables or derived tables to help recordset operations.|||People that normally write code in client tools tend to use cursors when writing code in SQLServer..thats perfectly understood since thats the way its done in vb, c, c# etc.
BUT SqlServer is about batch updates (most of the time); lots and lots and lots of rows being updated/inserted/deleted at once, a cursor just isnt efficient enough..
My role is:If you are forced to use cursors or other types of loops in more than 1% of your sql code you have problaly done something wrong..|||Fair enough. So is there another way you can open a RecordSet populate variables based upon the first row returned from the recordset, perform an update on another table based upon the values held within these variables, step to the next row, repopulate the variables, perform the update again, etc. Without using a cursor?
Everybody tells me that there evil and everything I've read instructs me not to use them but is there another way?
TSQL novice|||You need to get your mindset away from "stepping to the next row", and think about updating ALL the rows SIMULTANEOUSLY. That is what set-based transactions are all about, and that is the power of a database management engine.
If you want to know how to do it, you will first need to read the Books Online entries for SELECT statements and UPDATE statements. Then, if you are still confused, post again and give more details about what you are trying to do. We can assist you in writing the code, but we need a base to build upon.|||Can you give us an example of an update and the cursor itself...also, tying the # of records to the "batch" that result set came from is sort of meangless...because data is fluid by nature , that number is wrong the instant you set it on the row...
Maybe a brief business description of what's going will help make things more clear to us.|||Okey dokey, heres the cursor:
declare tcrl cursor FAST_FORWARD for SELECT TSample.ISmpCode, TAnalyses.IAnalysesN, TSample.ISmpShortCode, TCertResults.ICertResultsN, TCertResults.LabNo,
TCertResults.LabNoDateTime, TJob.IJobCode, TAnalyses.IAnCode, TAnalyses.TemperatureID + 48 AS MyTempAscii,
TMedia.SpreadOrPour, TAnalyses.MediaID + 48 AS MyMediaAscii, TAnalyses.IAnShortCertDesc, TCertResultSpecDilution.Dilution,
TCertResultSpecDilution.SpecDilN, TAnalyses.KiestraTest, TAnalyses.LabelRule, TAnalyses.IAnWorkType, TAnalyses.Pathogen,
TAnalyses.TemperatureID,TAnalyses.CameraSortID,TAn alysesIncubation.IncubationLength,TAnalysesIncubat ionTolerance.IncubationTolerance
FROM TCertResults INNER JOIN
TSample ON TCertResults.ISmpN = TSample.ISmpN INNER JOIN
TJob ON TSample.IJobN = TJob.IJobN INNER JOIN
TAnalyses ON TCertResults.IAnalysesN = TAnalyses.IAnalysesN INNER JOIN
TCertResultSpecDilution ON TCertResults.ICertResultsN = TCertResultSpecDilution.ICertResultsN INNER JOIN
TAnalysesIncubationTolerance ON
TAnalyses.IncubationToleranceID = TAnalysesIncubationTolerance.IncubationToleranceID LEFT OUTER JOIN
TMedia ON TAnalyses.MediaID = TMedia.MediaID LEFT OUTER JOIN
TAnalysesIncubation ON TAnalyses.IncubationID = TAnalysesIncubation.IncubationID
WHERE (TSample.ISmpShortCode IN (SELECT * FROM dbo.udf_Txt_SplitTAB(@.MYLSN,','))) AND (TCertResults.LabNo IS NOT NULL) AND (TCertResults.LabNoDateTime IS NOT NULL) AND
(TAnalyses.KiestraTest = 1) AND (TAnalyses.LabelRule = 'Shared') AND (TAnalyses.IAnWorkType = 'M') AND
(TAnalyses.Pathogen = 0)
Open tcrl
Fetch tcrl into @.MyLongLsn,@.MyAnalysisID,@.MyShortCode,@.MyCertResul tsN,@.MyLabNo,@.MyLabNoDateTime,@.MyJobCode,@.MyIanCod e,@.MyTempAscii,@.MySpreadOrPour,@.MyMediaAscii,@.MySh ortTestDesc,@.MyDilution,@.MySpecDilN,@.MyKiestraTest ,@.MyLabelRule,@.MyIanWorkType,@.MyPathogen,@.MyTemper atureID,@.MyCameraSortID,@.MyIncubationLength,@.MyInc ubationTolerance
While @.@.Fetch_status=0
Based upon the results of the this cursor variables will be populated and tested for certain criteria and other variables populated as a result of the results. Something like:
IF @.MySpreadOrPour = 'Pour'
BEGIN
Set @.MyMediaID = '0'
END
ELSE
BEGIN
Set @.myMediaID = @.MyMediaAscii - 48
END
Then finally another table will be populated with the values held within the variables:
INSERT INTO TBarCodAFile ( Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Dilution, LabelRule, DateDue)
VALUES (@.MyBarCode,@.MyLongLSN,@.MyJobCode,@.MyShortTestDesc ,@.MyDatePlateToBeRead,@.MyMediaID,@.MyStacker,@.MyWhi teLabels,@.MyAnalystPosition,@.MyEmpID,@.MyFormatting ,@.MyDilution,@.MyLabelRule,@.MyWholeDatePlateToBeRea d).
I'll then move to the next record in the recordset (Fetch next from tcrl into @.MyLongLsn,@.MyAnalysisID,@.MyShortCode,@.MyCertResul tsN,@.MyLabNo,@.MyLabNoDateTime,@.MyJobCode,@.MyIanCod e,@.MyTempAscii,@.MySpreadOrPour,@.MyMediaAscii,@.MySh ortTestDesc,@.MyDilution,@.MySpecDilN,@.MyKiestraTest ,@.MyLabelRule,@.MyIanWorkType,@.MyPathogen,@.MyTemper atureID,@.MyCameraSortID,@.MyIncubationLength,@.MyInc ubationTolerance).
Then do all my tests again and perform the INSERT statement again. And continue unless the end of the recordset.
No doubt this is a serious pain in the arse but I can find no other way of doing it.|||FIRST...lose the fully qualified table names...
Second, THAT'S ONE HELL OF A JOIN...ARE THE KEYS ALL IDENTITY?
Third, if it takes a long time to run, you could save the results to a temp table and do all of your work as a join to that
Fourth (and I'm liking the temp table thing more and more0 you could do the insert like:
INSERT INTO TBarCodAFile(
Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, Field8
, Field9
, Field10
, Field11
, Dilution
, LabelRule
, DateDue)
SELECT b.ISmpCode
, d.IAnalysesN
, b.ISmpShortCode
, a.ICertResultsN
, a.LabNo
, a.LabNoDateTime
, c.IJobCode
, d.IAnCode
, d.TemperatureID + 48 AS MyTempAscii
, g.SpreadOrPour
, d.MediaID + 48 AS MyMediaAscii
, d.IAnShortCertDesc
, e.Dilution
, e.SpecDilN
, d.KiestraTest
, d.LabelRule
, d.IAnWorkType
, d.Pathogen
, d.TemperatureID
, d.CameraSortID
, h.IncubationLength
, f.IncubationTolerance
FROM TCertResults a
INNER JOIN TSample b
ON a.ISmpN = b.ISmpN
INNER JOIN TJob c
ON b.IJobN = c.IJobN
INNER JOIN TAnalyses d
ON a.IAnalysesN = d.IAnalysesN
INNER JOIN TCertResultSpecDilution e
ON a.ICertResultsN = e.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance f
ON d.IncubationToleranceID = f.IncubationToleranceID
LEFT JOIN TMedia g
ON d.MediaID = g.MediaID
LEFT JOIN TAnalysesIncubation h
ON d.IncubationID = h.IncubationID
WHERE (TSample.ISmpShortCode IN
(SELECT *
FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')
)
)
AND (TCertResults.LabNo IS NOT NULL)
AND (TCertResults.LabNoDateTime IS NOT NULL)
AND (TAnalyses.KiestraTest = 1)
AND (TAnalyses.LabelRule = 'Shared')
AND (TAnalyses.IAnWorkType = 'M')
AND (TAnalyses.Pathogen = 0)|||Obviously the columns do not match...there's more in the select than the INSERT, you need to match them up...
How long does that monster take to run as a stand alone select?|||I've got to be honest and at this point say you've lost me. Maybe I just can't get my head around this temp table idea but as I see it if I populated a temp table with the results of a straight select statement surely I'd then have to read all the rows from this table to perform the criteria testing and wouldn't I still a cursor to do this. Or more likely I'm being a cretin.
Most of the linking fields are identity,yes.
Oh yeah its a proper beast, it used to be coded in Access VBA and it could take up to ten minutes to complete depending on the amount of rows affected.
I've been asked to re-code it server side and its the first time I've ventured into server side programming, so please excuse my naivety.|||Where did I lose you?
The INSERT?
The Table Aliases?
The Temp Table?
I would do...
SELECT b.ISmpCode
, d.IAnalysesN
, b.ISmpShortCode
, a.ICertResultsN
, a.LabNo
, a.LabNoDateTime
, c.IJobCode
, d.IAnCode
, d.TemperatureID + 48 AS MyTempAscii
, g.SpreadOrPour
, d.MediaID + 48 AS MyMediaAscii
, d.IAnShortCertDesc
, e.Dilution
, e.SpecDilN
, d.KiestraTest
, d.LabelRule
, d.IAnWorkType
, d.Pathogen
, d.TemperatureID
, d.CameraSortID
, h.IncubationLength
, f.IncubationTolerance
INTO WorkTable
FROM TCertResults a
INNER JOIN TSample b
ON a.ISmpN = b.ISmpN
INNER JOIN TJob c
ON b.IJobN = c.IJobN
INNER JOIN TAnalyses d
ON a.IAnalysesN = d.IAnalysesN
INNER JOIN TCertResultSpecDilution e
ON a.ICertResultsN = e.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance f
ON d.IncubationToleranceID = f.IncubationToleranceID
LEFT JOIN TMedia g
ON d.MediaID = g.MediaID
LEFT JOIN TAnalysesIncubation h
ON d.IncubationID = h.IncubationID
WHERE (TSample.ISmpShortCode IN
(SELECT *
FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')
)
)
AND (TCertResults.LabNo IS NOT NULL)
AND (TCertResults.LabNoDateTime IS NOT NULL)
AND (TAnalyses.KiestraTest = 1)
AND (TAnalyses.LabelRule = 'Shared')
AND (TAnalyses.IAnWorkType = 'M')
AND (TAnalyses.Pathogen = 0)
INSERT INTO TBarCodAFile(
Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, Field8
, Field9
, Field10
, Field11
, Dilution
, LabelRule
, DateDue)
SELECT ColList
FROM WorkTable
DROP WorkTable|||This is not all that bad. This should get you started:
INSERT INTO TBarCodAFile
(--Field1,
Field2,
Field3,
Field4,
--Field5,
Field6,
--Field7,
--Field8,
--Field9,
--Field10,
--Field11,
Dilution,
LabelRule
--DateDue
)
select --MyBarCode function,
TSample.ISmpCode,
TJob.IJobCode,
TAnalyses.IAnShortCertDesc,
--MyDatePlateToBeRead function
Case when TMedia.SpreadOrPour = 'Pour' then 0 else TAnalyses.MediaID end,
--MyStacker function,
--MyWhiteLabels function,
--MyAnalystPosition function,
--MyEmpID function,
--MyFormatting function,
TCertResultSpecDilution.Dilution,
TAnalyses.LabelRule
--MyWholeDatePlateToBeRead function
FROM TCertResults
INNER JOIN TSample ON TCertResults.ISmpN = TSample.ISmpN
INNER JOIN TJob ON TSample.IJobN = TJob.IJobN
INNER JOIN TAnalyses ON TCertResults.IAnalysesN = TAnalyses.IAnalysesN
INNER JOIN TCertResultSpecDilution ON TCertResults.ICertResultsN = TCertResultSpecDilution.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance ON TAnalyses.IncubationToleranceID = TAnalysesIncubationTolerance.IncubationToleranceID
LEFT OUTER JOIN TMedia ON TAnalyses.MediaID = TMedia.MediaID
LEFT OUTER JOIN TAnalysesIncubation ON TAnalyses.IncubationID = TAnalysesIncubation.IncubationID
WHERE TSample.ISmpShortCode IN (SELECT * FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')) --This should be changed to a join...
AND TCertResults.LabNo IS NOT NULL
AND TCertResults.LabNoDateTime IS NOT NULL
AND TAnalyses.KiestraTest = 1
AND TAnalyses.LabelRule = 'Shared'
AND TAnalyses.IAnWorkType = 'M'
AND TAnalyses.Pathogen = 0
I've commented out elements where the calculation function was not available. It should run MUCH faster if you convert it to this format.
...and I like using qualified column names, Brett! To each his own...|||Yeah to each his/her own...
But it's so verbose...
Sounds like they need to do many actions against many different tables based on the result.
What do you think of the temp table idea?
Just 1 pass versus over and over...|||Temp table (or table variable) is better than a cursor, no doubt.|||Ok I'll give those idea's a blast. Doubtless I'll be back in touch in about 15 minutes!!
Thanks again for your time and patience|||Originally posted by SexualChocolate
Ok I'll give those idea's a blast. Doubtless I'll be back in touch in about 15 minutes!!
Thanks again for your time and patience
OK...I gotta know...2:44?
Where in the world are you?|||Birmingham, England
How do I call a function from within a select statement? Told you I'd be back!|||You're better off starting a new thread for a new question but...
SELECT dbo.udf_myFunction99(Col1)....|||Thanks to everyone who posted.
Ta
declare tcrl cursor FAST_FORWARD for SELECT TSample.ISmpCode
FROM TCertResults TSample
WHERE (TSample.ISmpShortCode ='24/12359')
Open trcl
What I want to be able to get a count of this recordset. Been thru the TSQL help and it's pointing me toward the @.@.RowCount command but I can't seem to get this working.
Any help would be apprieciated@.@.ROWCOUNT returns the number of rows affected by the last statement. Use the @.@.CURSOR_ROWS function to receive the number of qualifying rows in the last opened cursor.
BTW - do you really need cursor? It heavily hits DB performance.|||I agree,
Wont SELECT COUNT(*) FROM TCertResults TSample
WHERE TSample.ISmpShortCode ='24/12359' do the trick?|||No cursrors...No cursors...
There is precious little you can do without them...
Describe what your trying to do...|||The cursor is a lot more complicated than posted but the basic gist is that the results of the cursor will be 'fetched' into variables which will then be used to update other tables, etc. Then the variables will be repopulated using the fetch next command. When updating the other tables with the results of the original cursor, one of the values that I need to write is total amount of records from the original cursor.
So I need the recordcount as soon as the cursor is open. But as I'm sure you've gathered I haven't got a clue how to get this!|||Makes no sense to me...(being sober sucks...)
So you want to write the count of the entire result set, for every row in the cursor?
That seems to be a derivation of a thing...
Anyway...do this before you open the cursor and just use the local variable..
DECLARE @.x int
SELECT @.x=COUNT(*)
FROM TCertResults TSample
WHERE ISmpShortCode ='24/12359'
Still, you could do all of this without a cursor.
Good luck|||Looks for me you can use temp tables instead of cursors...
But if you are decided to stay with them - didn't the @.@.CURSOR_ROWS suits you? Why - any reason? error? sth other?|||Using the Cursor_Row function always gave me a answer of -1. To be fair though I'm new to TSQL so my syntax could be totally wrong. And to prove it here's my syntax
declare tcrl cursor FAST_FORWARD for SELECT dbo.TSample.ISmpCode
FROM dbo.TSample INNER JOIN
dbo.TCertResults ON dbo.TSample.ISmpN = dbo.TCertResults.ISmpN
WHERE (dbo.TSample.ISmpShortCode = '24/12359')
Open tcrl
PRINT @.@.CURSOR_ROWS|||You don't my count solution?|||Your solution works great thank you.
I was just continuing the thread in the hope that someone could tell me what the correct syntax @.@.Cursor_Rows function was.
Cheers|||Look in BOL (Books Online):
@.@.CURSOR_ROWS
Returns the number of qualifying rows currently in the last cursor opened on the connection. To improve performance, Microsoft SQL Server can populate large keyset and static cursors asynchronously. @.@.CURSOR_ROWS can be called to determine that the number of the rows that qualify for a cursor are retrieved at the time @.@.CURSOR_ROWS is called.
Return value Description
-m The cursor is populated asynchronously. The value returned (-m) is the number of rows currently in the keyset.
-1 The cursor is dynamic. Because dynamic cursors reflect all changes, the number of rows that qualify for the cursor is constantly changing. It can never be definitely stated that all qualified rows have been retrieved.
0 No cursors have been opened, no rows qualified for the last opened cursor, or the last-opened cursor is closed or deallocated.
n The cursor is fully populated. The value returned (n) is the total number of rows in the cursor.
Don't use Cursors...|||Originally posted by SexualChocolate
Using the Cursor_Row function always gave me a answer of -1. To be fair though I'm new to TSQL so my syntax could be totally wrong. And to prove it here's my syntax
declare tcrl cursor FAST_FORWARD for SELECT dbo.TSample.ISmpCode
FROM dbo.TSample INNER JOIN
dbo.TCertResults ON dbo.TSample.ISmpN = dbo.TCertResults.ISmpN
WHERE (dbo.TSample.ISmpShortCode = '24/12359')
Open tcrl
PRINT @.@.CURSOR_ROWS
The Great Holy Online Book says:
"If you receive '-1' as return from @.@.CURSOR_ROWS that it means:
The cursor is dynamic. Because dynamic cursors reflect all changes, the number of rows that qualify for the cursor is constantly changing. It can never be definitely stated that all qualified rows have been retrieved. You can try with Brett Kaiser's count solution. So says The Great Holy Online Book"
Again:
<ghost_voice>Beware of cursors... uuuuaahahahahahaaa....</ghost_voice>|||like I say I'm fairly new to all this. I'm using a cursor because I don't know of another was to open a recordset and step thru it. If there is an alternative then please point me at it|||Count - to determine no of records,
temp tables or derived tables to help recordset operations.|||People that normally write code in client tools tend to use cursors when writing code in SQLServer..thats perfectly understood since thats the way its done in vb, c, c# etc.
BUT SqlServer is about batch updates (most of the time); lots and lots and lots of rows being updated/inserted/deleted at once, a cursor just isnt efficient enough..
My role is:If you are forced to use cursors or other types of loops in more than 1% of your sql code you have problaly done something wrong..|||Fair enough. So is there another way you can open a RecordSet populate variables based upon the first row returned from the recordset, perform an update on another table based upon the values held within these variables, step to the next row, repopulate the variables, perform the update again, etc. Without using a cursor?
Everybody tells me that there evil and everything I've read instructs me not to use them but is there another way?
TSQL novice|||You need to get your mindset away from "stepping to the next row", and think about updating ALL the rows SIMULTANEOUSLY. That is what set-based transactions are all about, and that is the power of a database management engine.
If you want to know how to do it, you will first need to read the Books Online entries for SELECT statements and UPDATE statements. Then, if you are still confused, post again and give more details about what you are trying to do. We can assist you in writing the code, but we need a base to build upon.|||Can you give us an example of an update and the cursor itself...also, tying the # of records to the "batch" that result set came from is sort of meangless...because data is fluid by nature , that number is wrong the instant you set it on the row...
Maybe a brief business description of what's going will help make things more clear to us.|||Okey dokey, heres the cursor:
declare tcrl cursor FAST_FORWARD for SELECT TSample.ISmpCode, TAnalyses.IAnalysesN, TSample.ISmpShortCode, TCertResults.ICertResultsN, TCertResults.LabNo,
TCertResults.LabNoDateTime, TJob.IJobCode, TAnalyses.IAnCode, TAnalyses.TemperatureID + 48 AS MyTempAscii,
TMedia.SpreadOrPour, TAnalyses.MediaID + 48 AS MyMediaAscii, TAnalyses.IAnShortCertDesc, TCertResultSpecDilution.Dilution,
TCertResultSpecDilution.SpecDilN, TAnalyses.KiestraTest, TAnalyses.LabelRule, TAnalyses.IAnWorkType, TAnalyses.Pathogen,
TAnalyses.TemperatureID,TAnalyses.CameraSortID,TAn alysesIncubation.IncubationLength,TAnalysesIncubat ionTolerance.IncubationTolerance
FROM TCertResults INNER JOIN
TSample ON TCertResults.ISmpN = TSample.ISmpN INNER JOIN
TJob ON TSample.IJobN = TJob.IJobN INNER JOIN
TAnalyses ON TCertResults.IAnalysesN = TAnalyses.IAnalysesN INNER JOIN
TCertResultSpecDilution ON TCertResults.ICertResultsN = TCertResultSpecDilution.ICertResultsN INNER JOIN
TAnalysesIncubationTolerance ON
TAnalyses.IncubationToleranceID = TAnalysesIncubationTolerance.IncubationToleranceID LEFT OUTER JOIN
TMedia ON TAnalyses.MediaID = TMedia.MediaID LEFT OUTER JOIN
TAnalysesIncubation ON TAnalyses.IncubationID = TAnalysesIncubation.IncubationID
WHERE (TSample.ISmpShortCode IN (SELECT * FROM dbo.udf_Txt_SplitTAB(@.MYLSN,','))) AND (TCertResults.LabNo IS NOT NULL) AND (TCertResults.LabNoDateTime IS NOT NULL) AND
(TAnalyses.KiestraTest = 1) AND (TAnalyses.LabelRule = 'Shared') AND (TAnalyses.IAnWorkType = 'M') AND
(TAnalyses.Pathogen = 0)
Open tcrl
Fetch tcrl into @.MyLongLsn,@.MyAnalysisID,@.MyShortCode,@.MyCertResul tsN,@.MyLabNo,@.MyLabNoDateTime,@.MyJobCode,@.MyIanCod e,@.MyTempAscii,@.MySpreadOrPour,@.MyMediaAscii,@.MySh ortTestDesc,@.MyDilution,@.MySpecDilN,@.MyKiestraTest ,@.MyLabelRule,@.MyIanWorkType,@.MyPathogen,@.MyTemper atureID,@.MyCameraSortID,@.MyIncubationLength,@.MyInc ubationTolerance
While @.@.Fetch_status=0
Based upon the results of the this cursor variables will be populated and tested for certain criteria and other variables populated as a result of the results. Something like:
IF @.MySpreadOrPour = 'Pour'
BEGIN
Set @.MyMediaID = '0'
END
ELSE
BEGIN
Set @.myMediaID = @.MyMediaAscii - 48
END
Then finally another table will be populated with the values held within the variables:
INSERT INTO TBarCodAFile ( Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Dilution, LabelRule, DateDue)
VALUES (@.MyBarCode,@.MyLongLSN,@.MyJobCode,@.MyShortTestDesc ,@.MyDatePlateToBeRead,@.MyMediaID,@.MyStacker,@.MyWhi teLabels,@.MyAnalystPosition,@.MyEmpID,@.MyFormatting ,@.MyDilution,@.MyLabelRule,@.MyWholeDatePlateToBeRea d).
I'll then move to the next record in the recordset (Fetch next from tcrl into @.MyLongLsn,@.MyAnalysisID,@.MyShortCode,@.MyCertResul tsN,@.MyLabNo,@.MyLabNoDateTime,@.MyJobCode,@.MyIanCod e,@.MyTempAscii,@.MySpreadOrPour,@.MyMediaAscii,@.MySh ortTestDesc,@.MyDilution,@.MySpecDilN,@.MyKiestraTest ,@.MyLabelRule,@.MyIanWorkType,@.MyPathogen,@.MyTemper atureID,@.MyCameraSortID,@.MyIncubationLength,@.MyInc ubationTolerance).
Then do all my tests again and perform the INSERT statement again. And continue unless the end of the recordset.
No doubt this is a serious pain in the arse but I can find no other way of doing it.|||FIRST...lose the fully qualified table names...
Second, THAT'S ONE HELL OF A JOIN...ARE THE KEYS ALL IDENTITY?
Third, if it takes a long time to run, you could save the results to a temp table and do all of your work as a join to that
Fourth (and I'm liking the temp table thing more and more0 you could do the insert like:
INSERT INTO TBarCodAFile(
Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, Field8
, Field9
, Field10
, Field11
, Dilution
, LabelRule
, DateDue)
SELECT b.ISmpCode
, d.IAnalysesN
, b.ISmpShortCode
, a.ICertResultsN
, a.LabNo
, a.LabNoDateTime
, c.IJobCode
, d.IAnCode
, d.TemperatureID + 48 AS MyTempAscii
, g.SpreadOrPour
, d.MediaID + 48 AS MyMediaAscii
, d.IAnShortCertDesc
, e.Dilution
, e.SpecDilN
, d.KiestraTest
, d.LabelRule
, d.IAnWorkType
, d.Pathogen
, d.TemperatureID
, d.CameraSortID
, h.IncubationLength
, f.IncubationTolerance
FROM TCertResults a
INNER JOIN TSample b
ON a.ISmpN = b.ISmpN
INNER JOIN TJob c
ON b.IJobN = c.IJobN
INNER JOIN TAnalyses d
ON a.IAnalysesN = d.IAnalysesN
INNER JOIN TCertResultSpecDilution e
ON a.ICertResultsN = e.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance f
ON d.IncubationToleranceID = f.IncubationToleranceID
LEFT JOIN TMedia g
ON d.MediaID = g.MediaID
LEFT JOIN TAnalysesIncubation h
ON d.IncubationID = h.IncubationID
WHERE (TSample.ISmpShortCode IN
(SELECT *
FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')
)
)
AND (TCertResults.LabNo IS NOT NULL)
AND (TCertResults.LabNoDateTime IS NOT NULL)
AND (TAnalyses.KiestraTest = 1)
AND (TAnalyses.LabelRule = 'Shared')
AND (TAnalyses.IAnWorkType = 'M')
AND (TAnalyses.Pathogen = 0)|||Obviously the columns do not match...there's more in the select than the INSERT, you need to match them up...
How long does that monster take to run as a stand alone select?|||I've got to be honest and at this point say you've lost me. Maybe I just can't get my head around this temp table idea but as I see it if I populated a temp table with the results of a straight select statement surely I'd then have to read all the rows from this table to perform the criteria testing and wouldn't I still a cursor to do this. Or more likely I'm being a cretin.
Most of the linking fields are identity,yes.
Oh yeah its a proper beast, it used to be coded in Access VBA and it could take up to ten minutes to complete depending on the amount of rows affected.
I've been asked to re-code it server side and its the first time I've ventured into server side programming, so please excuse my naivety.|||Where did I lose you?
The INSERT?
The Table Aliases?
The Temp Table?
I would do...
SELECT b.ISmpCode
, d.IAnalysesN
, b.ISmpShortCode
, a.ICertResultsN
, a.LabNo
, a.LabNoDateTime
, c.IJobCode
, d.IAnCode
, d.TemperatureID + 48 AS MyTempAscii
, g.SpreadOrPour
, d.MediaID + 48 AS MyMediaAscii
, d.IAnShortCertDesc
, e.Dilution
, e.SpecDilN
, d.KiestraTest
, d.LabelRule
, d.IAnWorkType
, d.Pathogen
, d.TemperatureID
, d.CameraSortID
, h.IncubationLength
, f.IncubationTolerance
INTO WorkTable
FROM TCertResults a
INNER JOIN TSample b
ON a.ISmpN = b.ISmpN
INNER JOIN TJob c
ON b.IJobN = c.IJobN
INNER JOIN TAnalyses d
ON a.IAnalysesN = d.IAnalysesN
INNER JOIN TCertResultSpecDilution e
ON a.ICertResultsN = e.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance f
ON d.IncubationToleranceID = f.IncubationToleranceID
LEFT JOIN TMedia g
ON d.MediaID = g.MediaID
LEFT JOIN TAnalysesIncubation h
ON d.IncubationID = h.IncubationID
WHERE (TSample.ISmpShortCode IN
(SELECT *
FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')
)
)
AND (TCertResults.LabNo IS NOT NULL)
AND (TCertResults.LabNoDateTime IS NOT NULL)
AND (TAnalyses.KiestraTest = 1)
AND (TAnalyses.LabelRule = 'Shared')
AND (TAnalyses.IAnWorkType = 'M')
AND (TAnalyses.Pathogen = 0)
INSERT INTO TBarCodAFile(
Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, Field8
, Field9
, Field10
, Field11
, Dilution
, LabelRule
, DateDue)
SELECT ColList
FROM WorkTable
DROP WorkTable|||This is not all that bad. This should get you started:
INSERT INTO TBarCodAFile
(--Field1,
Field2,
Field3,
Field4,
--Field5,
Field6,
--Field7,
--Field8,
--Field9,
--Field10,
--Field11,
Dilution,
LabelRule
--DateDue
)
select --MyBarCode function,
TSample.ISmpCode,
TJob.IJobCode,
TAnalyses.IAnShortCertDesc,
--MyDatePlateToBeRead function
Case when TMedia.SpreadOrPour = 'Pour' then 0 else TAnalyses.MediaID end,
--MyStacker function,
--MyWhiteLabels function,
--MyAnalystPosition function,
--MyEmpID function,
--MyFormatting function,
TCertResultSpecDilution.Dilution,
TAnalyses.LabelRule
--MyWholeDatePlateToBeRead function
FROM TCertResults
INNER JOIN TSample ON TCertResults.ISmpN = TSample.ISmpN
INNER JOIN TJob ON TSample.IJobN = TJob.IJobN
INNER JOIN TAnalyses ON TCertResults.IAnalysesN = TAnalyses.IAnalysesN
INNER JOIN TCertResultSpecDilution ON TCertResults.ICertResultsN = TCertResultSpecDilution.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance ON TAnalyses.IncubationToleranceID = TAnalysesIncubationTolerance.IncubationToleranceID
LEFT OUTER JOIN TMedia ON TAnalyses.MediaID = TMedia.MediaID
LEFT OUTER JOIN TAnalysesIncubation ON TAnalyses.IncubationID = TAnalysesIncubation.IncubationID
WHERE TSample.ISmpShortCode IN (SELECT * FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')) --This should be changed to a join...
AND TCertResults.LabNo IS NOT NULL
AND TCertResults.LabNoDateTime IS NOT NULL
AND TAnalyses.KiestraTest = 1
AND TAnalyses.LabelRule = 'Shared'
AND TAnalyses.IAnWorkType = 'M'
AND TAnalyses.Pathogen = 0
I've commented out elements where the calculation function was not available. It should run MUCH faster if you convert it to this format.
...and I like using qualified column names, Brett! To each his own...|||Yeah to each his/her own...
But it's so verbose...
Sounds like they need to do many actions against many different tables based on the result.
What do you think of the temp table idea?
Just 1 pass versus over and over...|||Temp table (or table variable) is better than a cursor, no doubt.|||Ok I'll give those idea's a blast. Doubtless I'll be back in touch in about 15 minutes!!
Thanks again for your time and patience|||Originally posted by SexualChocolate
Ok I'll give those idea's a blast. Doubtless I'll be back in touch in about 15 minutes!!
Thanks again for your time and patience
OK...I gotta know...2:44?
Where in the world are you?|||Birmingham, England
How do I call a function from within a select statement? Told you I'd be back!|||You're better off starting a new thread for a new question but...
SELECT dbo.udf_myFunction99(Col1)....|||Thanks to everyone who posted.
Ta
Subscribe to:
Posts (Atom)