CURSOR Select Clause

Oct 11, 2006

I need to dynamically construct the field order of a cursor based on fixed labels from another table, but when I put that resulting query I receive the error:

Server: Msg 16924, Level 16, State 1, Line 78
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

I have 6 fields defined in the cursor select, and 6 parameters in the fetch. The results of running the @sql portion returns valid data. Should this be possible to define a parameter containing the select clause of the cursor?

select colnum, coldesc, colname into #ae_defs from ae_adefs
select @Sql = (select colname from #ae_defs where coldesc = 'PATIENT NAME') +
', ' +
(select colname from #ae_defs where coldesc = 'PATIENT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'ACCOUNT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT DATE') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT TYPE') +
', DocID from ae_dtl1'

DECLARE myCursor CURSOR FOR
Select @SQL

OPEN myCursor
print @@Cursor_rows
FETCH NEXT FROM myCursor into @var1, @var2, @var3, @var4, @var5, @DocID

View 2 Replies


ADVERTISEMENT

Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable

Mar 28, 2000

Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list


I have difficulty combining a select statement with a where clause stored in a variable inside a cursor

The users select the mail content and frequency of delivery and i deliver the mail

I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.

Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work

For example

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary

How do I combine the select statement with the where clause?

Help me...I need help urgently

View 1 Replies View Related

Order By Clause In DECLARE CURSOR Select Statement Won't Compile

May 7, 2008

The stored procedure, below, results in this error when I try to compile...


Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.

However the select statement itself runs perfectly well as a query, no errors.

The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.

What gives with this?

Thanks in advance
R.

The code:




Code Snippet

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author: -----
-- Create date:
-- Description: inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
-- Add the parameters for the stored procedure here
@importedReportID int,
@authCode varchar(12)
AS
BEGIN
DECLARE @errmsg VARCHAR(80);

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--IF (@authCode <> 'TX-TEC')
--BEGIN
-- SET @errmsg = 'Unsupported reporting format:' + @authCode
-- RAISERROR(@errmsg, 11, 1);
--END

DECLARE srcRecsCursor CURSOR LOCAL
FOR (SELECT
ImportedRecordID
,ImportedReportID
,AuthorityCode
,[ID]
,[Field1] AS RecordType
,[Field2] AS FormType
,[Field3] AS ItemID
,[Field4] AS EntityCode
,[Field5] AS LastName
,[Field6] AS FirstMiddleNames
,[Field7] AS Title
,[Field8] AS Suffix
,[Field9] AS AddressLine1
,[Field10] AS AddressLine2
,[Field11] AS City
,[Field12] AS [State]
,[Field13] AS ZipFull
,[Field14] AS OutOfStatePAC
,[Field15] AS FecID
,[Field16] AS Date
,[Field17] AS Amount
,[Field18] AS [Description]
,[Field19] AS Employer
,[Field20] AS Occupation
,[Field21] AS AttorneyJob
,[Field22] AS SpouseEmployer
,[Field23] As ChildParentEmployer1
,[Field24] AS ChildParentEmployer2
,[Field25] AS InKindTravel
,[Field26] AS TravellerLastName
,[Field27] AS TravellerFirstMiddleNames
,[Field28] AS TravellerTitle
,[Field29] AS TravellerSuffix
,[Field30] AS TravelMode
,[Field31] As DptCity
,[Field32] AS DptDate
,[Field33] AS ArvCity
,[Field34] AS ArvDate
,[Field35] AS TravelPurpose
,[Field36] AS TravelRecordBackReference
FROM ImportedNativeRecords
WHERE ImportedReportID IS NOT NULL
AND ReportType IN ('RCPT','PLDG')
ORDER BY ImportedRecordID -- this should work but gives syntax error!
);

END

View 3 Replies View Related

Cursor Declared With Variable In Where Clause

Nov 17, 1999

When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before.

select @var = oldValue

declare testCursor cursor
for select someColumns
from someTable
where someColumn = @var

select @var = newValue

open testCursor

fetch next from testCursor into @someColumns

Thank's in advance.

Mirko.

View 2 Replies View Related

How To Include Variable In CURSOR SQL Filter Clause?

Jul 23, 2005

After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim

View 1 Replies View Related

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?

Jul 20, 2005

Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave

View 5 Replies View Related

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause

May 14, 2008

2 examples:

1) Rows ordered using textual id rather than numeric id


Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id






Result set is ordered as: 1, 11, 2
I expect: 1,2,11


if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.




Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id




Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


and


Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View 12 Replies View Related

SELECT Then DELETE Versus Extra Clause In SELECT

Nov 29, 2007

Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'

View 1 Replies View Related

Cursor Vs. Select

Jul 23, 2005

buddies,situation: a processing must take place on every row of a table, andoutput results to another table, that can't be done via an insertinto..select query (let's assume that it's not possible for now).There're 2 solutions I have in mind:1) open a cursor and cycle through each row (The table can have up to1M rows)2) create a clustered index (i.e on an identity column) then have aloop like:declare @i int, @rows int,@col1 varchar(20), @col2 varchar(20),... @coln varchar(20),@outval1 varchar(20),... -- output valuesselect @i=1, @rows = max(xid) from tblname -- xid is clustered indexedwhile (@i<=@rows)beginselect @col1 = col1, @col2 = col2,...@coln = colnfrom tblnamewhere xid = i-- do the processing on the variables-- then insert results to another tableset @i = @i+1endI'd like to know your ideas of which one would be more efficient. Anyother solutions are much appreciatedthanks,Tamy

View 4 Replies View Related

Select Output With Cursor

May 8, 2002

In my previous post I asked how to do the bottom question. I got a response to use a cursor, now I made an attempt to use a cursor but I still get the same response. Any help will be greatly appreciated.


--CURRENT OUTPUT--

empID Rank Skills
------- ---- --------------------------------------------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer
2924 3 Create Spreadsheet
2924 3 Joining Tables in a Query



--DESIRED OUTPUT--

empID Rank Skills
------ ---- ------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet


--Here is the cursor script.--

Declare @skills varchar(255),@skills2 varchar(255),@message varchar(255),@empID varchar(255), @Rank varchar(255)
DECLARE emp_skills CURSOR For
select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924'

DECLARE emp_skills2 CURSOR For
select B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924'
OPEN emp_skills
OPEN emp_skills2
FETCH NEXT FROM emp_skills into @empID, @Rank, @skills
FETCH NEXT FROM emp_skills2 into @skills2
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = @skills2
FETCH NEXT FROM emp_skills2 into @skills2
Print @empID + ' '+ @Rank + ' ' + @message


FETCH NEXT FROM emp_skills into @empID, @Rank, @skills

End
CLOSE emp_skills
DEALLOCATE emp_skills
CLOSE emp_skills2
DEALLOCATE emp_skills2


--Previous Post--

Another question for all you SQL experts, I have a lot of them. I am trying to select from a table wher some conditions need to be met based on an employee ID. What I am doing is when the rank is a 1,2, or 3 I pick up the text description of that rank. Can I make it so that I get the ID only once and all the text descriptions are on the same line. Here is the sql script along with my current output and my desired output.



--SQL SCRIPT__

select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3')


--CURRENT OUTPUT--

empID Rank Skills
------- ---- --------------------------------------------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer
2924 3 Create Spreadsheet
2924 3 Joining Tables in a Query



--DESIRED OUTPUT--

empID Rank Skills
------ ---- ------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet

View 2 Replies View Related

Select Statement In Cursor

Mar 26, 2000

Hi...


I have a stored procedure that rertrieves data from an sql database
and sends out a mail to each receipient who meets the criteria

I am using SQL mail.


I dynamically generate the where clause for my sql query based on criteria taken
from other stored procedures and store it in a varchar variable
called @sqlquery

When i have the following code to run my cursor

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City,
Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary.

What should i do. i have tested the variable @sqlquery and it is
definately not blank. There is no bracket error or anything.

Please help!!!

Thanks much indeed

Ramesh

View 1 Replies View Related

Using Select Statement Instead Of Cursor

May 20, 2003

Hi All, Can anyone please help?

TableA has data as below:

ssn sex dob rel_code
111111111 m 19500403 m
111111111 f 19570908 w
111111111 f 19770804 d
111111111 f 19801203 d
111111111 f 19869712 d
111111111 m 19870907 s
111111111 m 19901211 s

I have to convert the rel_code into a specific manner so the data will look as below in TableB:

ssn sex dob rel_code
111111111 m 19500403 01
111111111 f 19570908 02
111111111 f 19770804 20
111111111 f 19801203 21 111111111 f 19869712 22
111111111 m 19870907 30
111111111 m 19901211 31

Member's rel_code = 01
spouse's rel_code = 02
daughter's rel_code starts from 20 with the oldest and increments by 1.
Son's rel_code starts from 30 and increments by 1 from oldest to the youngest.

I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor.

Thanks in advance.

Jannat.

View 6 Replies View Related

Dynamic Select For CURSOR

May 14, 2008

Hi all

I am trying to do dynamic Select for Cursor. The dynamic would be like this:
IF CONDITION1 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID
IF CONDITION2 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID AND
CustomerSiteID = @CustomerSiteID

etc etc

Here's the cursor


DECLARE RateList CURSOR FOR
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE (BASED ON CONDITION)
ORDER BY CustomerTenderID,
CustomerSiteID,
SupplierID,
ContractPeriod

OPEN RateList
FETCH NEXT FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rowNum = @rowNum + 1

-- DO SOME FUNKY STUFF


FETCH NEXT
FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID

View 4 Replies View Related

Cursor Select And Variables

Oct 23, 2006

I have problems to place my variable into the select statement.

DECLARE @DB_NAME varchar(64)
DECLARE MR_ReqPro_DB_cursor CURSOR FOR
select name from dbo.sysdatabases where name like '%MR_req%'
OPEN MR_ReqPro_DB_cursor
FETCH NEXT FROM MR_ReqPro_DB_cursor
INTO @DB_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
print @DB_NAME; --works fine

Select NAME, FILEDIRECTORY FROM @DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\%');

FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @DB_NAME
END
CLOSE MR_ReqPro_DB_cursor
DEALLOCATE MR_ReqPro_DB_cursor

GO

How could i use a variable like @DB_Name in my select ?

View 1 Replies View Related

Adding Variable To SELECT For CURSOR

Oct 13, 2003

I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable.
The following fails to parse:

Declare Cursor1 Cursor
For
'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name'
Open cursor1

That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.

View 7 Replies View Related

Variable As Column In Cursor Select

Jun 7, 2004

I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example:

declare @col varchar(50)

set @col = 'Temperature'

declare notifycurs cursor scroll for
select @col from Table

Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC
(ie. exec('select '+@col+' from Table' )
but that gave me an error.

Is there a way to pass in a variable for a column name for a curor select statement????

View 7 Replies View Related

How To Specify Which Database To Use For A Select Statement Within A Cursor?

Nov 9, 2007

Hi everyone,

I have been trying to perform the following task:

Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns:

Database name
Table name
Column name from that table with a clustered index

I have already created the following script which displays all the databases for a given server:

declare @DBname nvarchar(128)
declare testCursorForDB cursor
for
select name from sys.databases with (nolock)
where name not in ('master','tempdb','model','msdb')
order by name

open testCursorForDB
fetch next from testCursorForDB
into @DBname

while @@fetch_status = 0
begin
print @DBname
fetch next from testCursorForDB
into @DBname
end

close testCursorForDB
deallocate testCursorForDB

I also have created the following query which will display all the table and column names which have a clustered index for a given database:

select object_name(i.id) as TableName,

i.name as IndexName
from sys.sysindexes as i with (nolock)
where i.indid = '1'

However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor.

Any help is greatly appreciated

Thanks!

View 7 Replies View Related

SELECT DISTINCT Will Always Result In A Static Cursor

Jul 31, 2007

an example for the pb
1)First i have created a dynamic cursor :

DECLARE authors_cursor CURSOR DYNAMIC
FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7
OPEN authors_cursor
FETCH first FROM authors_cursor

2)The result for this cursor is for expamle 'USA'.

3) If now i do an update on that location with a new value 'USA1'

update am_location set location_en = 'USA1' WHERE LOCATION_ID = 7

4)now if i fetch the cursor , i''ll get the old value (USA) not (USA1).

If i remove DISTINCT from the cursor declaration , the process works fine .

View 10 Replies View Related

DB Engine :: TOP Changing Cursor Select Performance

Jun 15, 2015

the cursor at the bottom  iterates only to print the number of rows.The problem is in the select. This takes 30 seconds to iterate through 1242 records.But if I add a TOP 1000000 or whatever number to the select, the same iteration takes less than a 1 second.I've tested each query without cursor, and  both have the same cost and performance. (Not exactly the same plan)Note that I got the same performance improvement declaring the cursor as STATIC.Why the top is affecting the cursor iteration so much?

Declare @query varchar(512)
DECLARE Itera CURSOR --LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
select --TOP 1000000

[code]....

View 2 Replies View Related

SELECT DISTINCT Will Always Result In A Static Cursor

Jul 31, 2007

An example for my pb
1) Created a dynamic cursor :
DECLARE cursor_teste CURSOR DYNAMIC
FOR Select DISTINCT name from table WHERE ID = 1
OPEN cursor_teste
FETCH first FROM cursor_teste
2)The result for this cursor is for example 'teste'.
3) If now i do an update on that name with a new value 'teste1'
than if i fetch the cursor , i''ll get the old value (teste) .


any idea how to make a select distinct result in a dynamic Cursor?

View 7 Replies View Related

How To Put Condition In Select Statement To Write A Cursor

Mar 29, 2008

col1 col2 col3 col4
36930.60 145 N . 00
17618.43 190 N . 00
6259.20 115 N .00
8175.45 19 N .00
18022.54 212 N .00
111.07 212 B .00
13393.05 67 N .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.

Can any one reply for this..............

View 3 Replies View Related

Select With IN Clause

Aug 3, 2006

Hi SQL Experts,

i have a strange problem

i have a variable which stores some values(ID) with single quote (so that i can use directlt inside the IN Clause of SELECT

Declare @DMSIDs AS VARCHAR(1000) -- variable declare,

Select @DMSIDs = '''DMS00046847'',''DMS00048305''' -- for test putting 2 correct values with escape characters

Select * from issue where id in (@DMSIDs) -- valid statment, but does not return any data
Select * from issue where id in ('DMS00046847','DMS00048305') -- same above constant value this returns data, but putting the values in varaible then trying fails.

The reason is i have a master table called issue and have another table [delta] where a particular column will store all the ID's of the issue table comma separated with single quote and i wanted to use something like below in my actual application

Select * from issue where ID in (Select distinct delta_ID from Delta_branch where date = getdate())
but since the above example with variable is not returning any data i wonder if such is possible in any other ways.

thank you for reading and helping me.

View 3 Replies View Related

Select To Clause

Aug 3, 2007

I'm having trouble using the To clause in my select statement. The following errs out with

Incorrect syntax near the keyword 'to'.


use pubs

select

*

from

view_Rates to 'c: est.csv'

I also tried this and got same error:


use pubs

select

*

to 'c: est.csv'

from

view_Rates

View 6 Replies View Related

Moving Average Using Select Statement Or Cursor Based?

Jul 30, 2007

ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE)
1 01/01/2007 PURCHASE 10 1000
2 01/01/2007 PURCHAES 5 1100
3 01/01/2007 SALES -5 *TobeCalculated
4 02/01/2007 Purchase 20 9000
5 02/01/2007 SALES -10 *TobeCalculated
5 02/01/2007 purchase 50 8000
6 03/01/2007 Sales -10 *TobeCalculate
7 01/01/2007 Purchase 20 12000

I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?

View 20 Replies View Related

How SELECT In WHERE Clause Works?

Jun 8, 2008

Please help 
I'm trying to do a select command but doesn't return any record
I have two tables one is "lists" another one is "list_records"
in the liss table I have 4 records and in the list_records I have only one record which is tell who is already visited the site so I created a query to get people who is NOT visited the site
 
Here is my query (I got 0 record return) 
 
SELECT *FROM lists
list_reccords
WHERE NOT EXISTS (SELECT *
                                                   FROM lists,
                                                      list_records                                       WHERE list_records.is_visited = 1
                                             AND lists.list_id = list_records.list_id)

View 4 Replies View Related

Insert Into ....... Select Clause

Mar 26, 2004

Hi,
I have a question about Insert into .....
Select clause in a SP. I need to insert some rows into a temperary table in a specific order. For example,
insert into #TempTable
{
.......
........
}
Select * from products order by @SortBy @SortDirection.
//
First of all, the order by clause does not take
variables. I have tried to use
declare @query varchar (1000)
set @query = 'Select * from products order by " + @SortBy + ' ' + @SortDirection
exec (@query)
//
However, I get an error message because I should supply a Select statement.
How can I solve this problem??

Thanks for your help!

View 3 Replies View Related

Using Greater Than '&>' In SELECT Clause

Jan 10, 2006

Here is the first part of a query for MySQL that I am trying to get working on MSSQL:


Code:


SELECT n.*,
round((n.rgt-n.lft-1)/2,0) AS childs,
count(*)+(n.lft>1) AS level,
((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower,
(( (n.lft-max(p.lft)>1) )) AS upper
FROM table n
...



But, I get this error message:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.

Is there a way to convert this? Thanks

View 2 Replies View Related

IF Statement In SELECT WHERE Clause

Jun 12, 2008

Hi Everyone,

I have the following stored procedure, I would like to use
IF statement or something of the sort in the where clause i.e.
The last line in the SP is: AND (category.categoryID = @categoryID),
I only want to check this, if @categoryID is not = 12.
So can I do something like this:

IF @categoryID <> 12
AND (category.categoryID = @categoryID)

STORED PROCEDURE:

CREATE PROCEDURE sp_get_total_risk_patients
@categoryID int
AS

SELECT COUNT(DISTINCT patient.patientID) AS total_patients
FROM patient
INNER JOIN patient_record ON patient.patientID = patient_record.patientID
INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID
INNER JOIN category ON category.categoryID = sub_category.categoryID
WHERE risk = 6
AND (completed_date = '' OR completed_date IS NULL)
AND (category.categoryID = @categoryID)

View 4 Replies View Related

Select A As B And Reference B In The Where Clause

Aug 23, 2007

Hello,

When you rename a field/calculation in the select part of the sql statement, how do you reference it in the where clause?

For example:

Select A, B, (C + D) as X
From test
WHERE X > 1

Many thanks!

View 3 Replies View Related

Question About Where Clause In A Select

Feb 28, 2008

Hi,

I have a simple select statement that joins a master and a detail tables using a single field. Looks somthing like this:

Master:
Field1 (Unique key)
more fields...
DateField (Index field)

Detail:
Field1 (unique key)
Field2 (unique key)
more fields....

The master has 100 thousand records and the detail has 100 million records.

If I had a statment "Select.....From Master, Detail" what would be the best way to write the where clause?

Would one of the following where clauses run faster than the other based on the number of records in the tables?

Where1:
where Master.DateField = value and Master.Field1 = Detail.Field1

Where2:
where Master.Field1 = Detail.Field1 and Master.DateField = value

View 3 Replies View Related

'SELECT Clause Does Not Return Rowset From Sp'

May 3, 2004

I have sp20, simplified, as:

ALTER PROCEDURE dbo.sp20 (@CustomerID int, @aDate as datetime) AS

SELECT Customers.* INTO #EndResult1 FROM Customers WHERE Customers.CustomerID >= @CustomerID

SELECT Orders.* INTO #EndResult2 FROM Orders Where Orders.[TakenDate] >= @aDate

SELECT #EndResult1.*, #EndResult2.*
FROM #EndResult1 INNER JOIN #EndResult2 ON #EndResult1.CustomerID = #EndResult2.CustomerID

This works fine in EM.

When I try to execute it from MS Access ADP Project I get

'Stored Procedure excuted succesfully, but did not return any records'

Although, in EM it returns the right number of records.

Thank you in advance - Rehman

View 2 Replies View Related

UDefined Functions In Select/ Where Clause

Sep 11, 2000

Hi,

Is there any way of emulating Oracle's capability of passing output of user-defined functions in the select statement or better still in the Where clause in SQL server 7.0? If not then could we hope for it in SQl server 2000?

Regards,
Vikas..

View 1 Replies View Related

Use A Variable Along With The FROM Clause In SELECT Statement

Dec 28, 2004

I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts.

What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*).

This is the code in my procedure..

DECLARE @tab_list CURSOR
set @tab_list = CURSOR FOR select * from table_list
OPEN @tab_list

DECLARE @tab_name varchar(256)
DECLARE @rec_cnt int
FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt

select count(*) from @tab_name

This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table.

Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there.

Please help me to construct the select statement that is similiar to

x=<table name>
select * from x
where x is a variable and the table name gets substituted.

what is the syntax for it ?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved