Mathematical Computation In Transact SQL
Mar 5, 2008
What is the equivalent in Transact SQL for VB.net ^ (Exponent Operator)
I have a following equation in VB.net
DotGain = ((1 - 10 ^(D0 - D50)) /(1 - 10 ^ (D0 - D100))) * 100 - 50
where D0, D50 and D100 are decimal variables
Working on creating a SQL UDF where @D0,@D50 and @D100 are transact sql decimal variables
DECLARE @DotGain decimal
DECLARE @D0 decimal
DECLARE @D50 decimal
DECLARE @D100 decimal
SET @D0 = 0.10
SET @D50 = 0.54
SET @D100 = 1.20
SELECT @DotGain = 1 - POWER(10,(@D0 - @D50)) / 1 - POWER(10,(@D0 - @D100)) * 100 - 50
but it is giving me different result in TSQL than VB2005
Can someone correct me?
Thanks in advance.
View 7 Replies
ADVERTISEMENT
Feb 1, 2004
i am trying to set up a quick and dirty, but efficient way to calculate permutation and combination. anyone out there dealt with this?
View 6 Replies
View Related
Feb 6, 2004
Please execute the script below to understand the problem -
---
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)
--id & col1 make up the PK.
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')
select *
from test
select *
from test2
--The rows are identical.
--Script A
select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)
--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)
-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.
TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')
--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.
Thanks
drop table test
drop table test2
go
--
View 3 Replies
View Related
Jul 20, 2005
My table is laid out as such:ID (int) What (varchar 20) TimeStamp (smalldatetime)------- ------------- ---------------73 Start <T1>73 Misc <T2>73 End <T3>81 Start <T1'>81 Misc <T2'>81 End <T3'>....I need to calculate End - Start for each unique ID (i.e. T3-T1 andT3'-T1') and then take the average of those (2 in this case) entries.Any help is appreciated.Alex.
View 8 Replies
View Related
Sep 28, 2007
how to round the exponential values.
for example my input is 8.14444367893246e-4
my expected output should be 8.14444367893246
how to achieve this?
View 10 Replies
View Related
Jan 10, 2008
hello sir
Thank for ur response to me in such a way.
I m student of cs in final semester & designing a project for online test series for all subject like mathematics, physics, chemistry, geography. Ihave a pb for storing mathematical questions which includes special symbols in organic chemistry structural farmulas etc.How can we store in database. i m sending a test paper for better understanding. plz help me.I will be hioghly obliged to u for this.
Your's sincerely
Avanish Yadav
View 1 Replies
View Related
Aug 5, 2007
I've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.
For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given
2
4
3
6
0
Average: 5
What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?
I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.
Any help will be appreciated.
View 1 Replies
View Related
Sep 17, 2006
Hi
I would like to is there any way we can store mathematical equations, that can be typed using equations 3.0, in sql server database as a column. What would be the datatype for the same.
Thanks
View 1 Replies
View Related
Feb 20, 2008
Hi,I would like to know if its possible to add/subtract/multiply/divide values between a cell in a database and a textbox/label on a web form? how would I go about doing this? Also, I would like a number from a cell to appear in a label when a page first loads. How can I achieve this?
View 8 Replies
View Related
Sep 17, 2014
I am having 4 Columns Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt.
I want to perform following without using case if it is possible.
When value of Flag is 0 then Qty*(CF2/CF1)
When value of Flag is 1 then Qty
And i Don't want to use any functions like isnull,NullIf,IIF even not union or union all.How to do this calculation without using any function.
Actually i am having more then 100000 rows in table and if i use functions then my index might not be called.,that why want to avoid cases and functions.
View 9 Replies
View Related
Aug 2, 2007
When I am executing a query that uses a mathematical function on values from 2 tables the query takes much longer than the same query that uses values from 1 table, even though the join remains the same.
Why is this happening?
Is there a way to bypass this problem?
Long query ( values from 2 tables ) :
SELECT
MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = ‘test’
Short query ( values from 1 table ) :
SELECT
MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = ‘test’
Long query execution plan:
Execution Tree
--------------
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( ( sign ( [attribute].[keyValue]--2027587559 ) *sign ( [attribute].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
|--Nested Loops ( Inner Join )
|--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=’test’ ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Table Spool
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )
Short query execution plan:
Execution Tree
--------------
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( [partialagg1005] ) ) )
|--Nested Loops ( Inner Join )
|--Stream Aggregate ( DEFINE: ( [partialagg1005]=MAX ( ( sign ( [data].[keyValue]--2027587559 ) *sign ( [data].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
| |--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=’test’ ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )
View 1 Replies
View Related
Jan 7, 2008
I have the following SELECT statement. It returns the first record from EVENT_TEACHERS. I want the last record. 'iID' is the unique identifier so I want to select this stuff where iID is max. How can I add this to my string? I can't get the block out of my head of wanting to put the aggregate in the WHERE clause.
str = "SELECT vwStaffList4Assessment.*, EVENT_TEACHERS.iID, EVENT_TEACHERS.iAsmtID, ASMT.* FROM EVENT_TEACHERS INNER JOIN ASMT ON EVENT_TEACHERS.iAsmtID = ASMT.iAsmtID INNER JOIN vwStaffList4Assessment ON ASMT.DVN=vwStaffList4Assessment.DVN"
Thanks
View 5 Replies
View Related
Feb 28, 2008
I am trying to insert to our DB. Here is my insert statement.
str = "INSERT INTO SURVEY (q3) VALUES (" & q3 & ") WHERE iID="&iID
Here is my debug:
DEBUG: INSERT INTO SURVEY (q3) VALUES (1) WHERE iID=23
And here is my error:
Incorrect syntax near the keyword 'WHERE'.
Can someone please tell me what I am doing wrong? Thanks in advance.
View 3 Replies
View Related
Mar 15, 2006
I have the following string in Access as a field
SpecCmplDate: IIf([SaleStatus]="Spec",[Close],"")
How do I create this field in SQL Server?
View 2 Replies
View Related
Jul 27, 2001
I am new to SQL and need some help. Would anyone be able to tell how to write a statmement, that would bring back several columns with specific amounts of spaces in it? I don't want to change the sizes of the columns forever, just for this one query.
Thanx in advance.
sql amateur
View 2 Replies
View Related
Jul 29, 1998
Hello all,
I`m trying to do a "order by" on a datefield in a query (month only), and I would like my result set to be sorted started with a value that I provide.
For example, I would like the result set to be sorted begining with october:
October ...
November ...
December ...
January ..
February ...
March ...
But of course it gave me:
January ..
February ...
March ...
November ...
December ...
I attempted to do it in 2 selects with a union but it wouldn`t let me do an "order by" in both select statements with the union operator.
-skye
View 3 Replies
View Related
Sep 5, 2004
CALL CENTER IT SUPPORT
I START A NEW JOB WITH A COMPANY PROVIDES IT SUPPORT TO CALL CENTERS. WE USE VB6 CONNECTING
VIA ADO TO SQL SERVER 2000 STORED PROCEDURES. OUR ONLINE REPORTING TOOL USES CRYSTAL REPORTS &
ASP (LITTLE VB6). CAN ANYONE RECOMMEND GOOD BOOKS AND WEBSITES TO PREPARE ME FOR MY NEW POSITION
WITH THIS COMPANY?
ALSO, HOW DOES ONE GO ABOUT BUYING RELEVANT CODE IN VB6, TRANSACT SQL, ASP?
WITH CALL CENTERS (OUR CLIENTS) MOVING OVERSEAS-OUTSOURCING, WHAT ABOUT ENRICHING OUR
POTENTIAL CLIENT BASE...FOR EXAMPLE, RECOMMENDING MARKETING EFFORTS TO 'NAILED DOWN' CLIENTS
LIKE PHARMACEUTICAL COMPANIES WHERE THE GOVERNMENT DEMANDS SUCH WORK BE DONE IN AMERICA. OR
EXPANDING OUR ROLE TO ALSO DO...?
ANY IDEAS TO ABOVE THREE ISSUES PLEASE-PLEASE EMAIL mikelynn@comcast.net
View 1 Replies
View Related
Jan 9, 2007
Hi: Can any one please tell me the difference b/w TSQL and simple SQL. Also there is a a TSQL use in my VB6 Program, how to i find that SQL in SQL Server?
cnADOSQL.Execute "DBCC CHECKIDENT ('tbl_distlist_balrange', RESEED, 0)"
Thanks.
View 3 Replies
View Related
May 14, 2004
Hi,
What is the sql syntaxe to specify which database can be accessed by a login.
View 2 Replies
View Related
May 30, 2008
I am trying to use LIKE in a statement but I can't seem to pull all the rows for Age of Empire. I have at least 50 rows with the word Age of Empire but I only get two rows. What is the best way to use LIKE in the statement below so that it pulls all rows with the words Age of Empire?
Select * from dbo.proddesc
where codeabbreviation Like '%Age of empire%'
View 2 Replies
View Related
Mar 12, 2006
Hi Im reading about active and temporal databases. I have a quesiton though.
In which category does SQL server falls into?
Also
What is the difference between Transact SQL and TSQL2?
thanks
View 4 Replies
View Related
Jul 11, 2006
I am trying to tally up information from two tables, but I am runningin to trouble and I can't seem to figure this out. I have aCreditsTable and a DebitsTable and I am trying to create a querry thatsums up the info from each per UserID and returns TotalCredits,TotalDebits, and a Ballance.CreditsTableUserID Ammount Applied+----------+----------+----------+| 192 | 1 | True || 192 | 2 | True || 207 | 1 | True || 207 | 1 | True || 207 | 2 | True || 212 | 3 | True |DebitsTableUserID Ammount Applied+----------+----------+----------+| 192 | 1 | True || 207 | 1 | True || 207 | 1 | True |***This is the Function I have tried, but it doesn't return the correctresultsALTER FUNCTION [dbo].[BallanceTotals]()RETURNS TABLEASRETURN(SELECT DISTINCTdbo.CreditsTable.UserID, SUM(dbo.CreditsTable.Ammount) AS TotalCredits,SUM(dbo.DebitsTable.Ammount) AS TotalDebits,SUM(dbo.CreditsTable.Ammount - dbo.DebitsTable.Ammount) AS BallanceFROMdbo.CreditsTable FULL OUTER JOINdbo.DebitsTable ON dbo.CreditsTable.UserID = dbo.DebitsTable.UserIDWHERE(dbo.CreditsTable.Applied = 1) OR (dbo.DebitsTable.Applied = 1)GROUP BYdbo.CreditsTable.UserID)*** This is what it returns, it is not adding things up correctly (itlooks like it is adding NULL values as 1 instead of 0 or something)BallanceTotalsTotal TotalUserID Credits Debits Ballance+----------+----------+----------+----------+| 192 | 3 | 2 | 1 || 207 | 4 | 3 | 1 || 212 | 3 | | |*** This is what I want it to return!BallanceTotalsTotal TotalUserID Credits Debits Ballance+----------+----------+----------+----------+| 192 | 3 | 1 | 2 || 207 | 4 | 2 | 2 || 212 | 3 | 0 | 3 |I would really appreciate some help in getting this to work correctly!-Daniel
View 5 Replies
View Related
Aug 10, 2015
Why is my where skewing data? Â This 1st statement shows everything perfectly, but this go around Ineed to add one more stipulation and adding in the where returns 0 results. Â But if I run a simple select with the where added, I get the intended result set.
--works
with cte as(select *, row_number() over(partition by time1, time2, userID
order by userID ) as rn from timetable)
delete from cte where rn > 1
--no work
with cte as(select *, row_number() over(partition by time1, time2, userID
order by userID ) as rn from timetable where timeadded > 0)
delete from cte where rn > 1
View 5 Replies
View Related
Jul 10, 2015
Below is my SQL code and after that output i get. For result i would like to get SUM of column Expr1 (which is 60.668,66) so that means only 1 row not 5. I know it's not that hard but i've been searching around and trying but no luck...
SELECT INVENTTABLE_1.ITEMID AS MA, INVENTDIM_1.INVENTLOCATIONID, SUM(INVENTSUM_1.PHYSICALINVENT) AS Expr1
FROM dbo.INVENTDIM AS INVENTDIM_1 INNER JOIN
dbo.INVENTSUM AS INVENTSUM_1 ON INVENTDIM_1.INVENTDIMID = INVENTSUM_1.INVENTDIMID INNER JOIN
dbo.ECORESPRODUCTTRANSLATION AS ECORESPRODUCTTRANSLATION_1 INNER JOIN
dbo.INVENTTABLE AS INVENTTABLE_1 ON ECORESPRODUCTTRANSLATION_1.PRODUCT = INVENTTABLE_1.PRODUCT ON
[Code] ....
View 4 Replies
View Related
Feb 28, 2008
I have 3 tables in SQL 2005. I have dumbed them down significantly, to illustrate my question.
1) Item Table - Contains a list of 2 items, "Coca-Cola" and "Pepsi-Cola".
2) Market Share - Contains the 'market share' information for the items in "Item Table". 1 to 1 relationship.
3) Retail Price - Contains the 'average retail' price data for the items in "Item Table". 1 to 1 relationship.
All three tables are linked by "iItemID".
I am trying to build a simply query that generates the following results:
----------------------------------------------------------
Item Name Market Share Retail Price
-----------------------------------------------------------
Coca-Cola mm.mm% $xx.xx
Pepsi-Cola mm.mm% $xx.xx
If, I first focus on retrieving the Market Share data, using the following query, it retrieves what I want.
SELECT tblItems.sItemName, tblMarketShare.sYTD
FROM tblItems INNER JOIN
tblMarketShare ON tblMarketShare.iItemID = tblItems.iItemID
----------------------------------
Item Name Market Share
----------------------------------
Coca-Cola mm.mm%
Pepsi-Cola mm.mm%
However, if I then add the RetailPrice price table (using the following query), I get the following incorrect results.
SELECT tblItems.sItemName, tblMarketShare.sYTD, tblRetailPrice.sYTD
FROM tblItems INNER JOIN
tblMarketShare ON tblMarketShare.iItemID = tblItems.iItemID INNER JOIN
tblRetailPrice ON tblRetailPrice.iItemID = tblItems.iItemID
----------------------------------------------------------
Item Name Market Share Retail Price
-----------------------------------------------------------
Coca-Cola mm.mm% $xx.xx
Pepsi-Cola mm.mm% $xx.xx
Coca-Cola mm.mm% $xx.xx
Pepsi-Cola mm.mm% $xx.xx
Where the first two values for Market Share are repeated again for row 3 and 4.
How do I syntax my SQL statement to return a single data set in the following fashion?
----------------------------------------------------------
Item Name Market Share Retail Price
-----------------------------------------------------------
Coca-Cola mm.mm% $xx.xx
Pepsi-Cola mm.mm% $xx.xx
I know this is simple for someone who write queries all the time. But this is driving me crazy. I have tried various JOINS (LEFT OUTER, RIGHT, etc.), but I am missing something.
Anyone's help greatly appreciated.
Thanks,
Vance
View 6 Replies
View Related
Mar 18, 2008
Is it possible to create a Server Role without have the Drop Database option
View 2 Replies
View Related
Dec 23, 2004
I need to return a computed value and have no idea how to do it.
I have a table of dates:
Column DataType
RowID int
Person nvarchar(20)
Percent real
Date smalldatetime
What I need is the Annual Fiscal Year Average. The fiscal year starts and ends in June. I need it to add all Percent values between the months of June 2001 - June 2002, June 2002 - June 2003, and so on and on....
A result set like this...
Person - FiscalYear - FiscalYearResult
John D - 2001 - .58
John D - 2002 - .52
John D - 2003 - .50
Jane D - 2001 - .58
Jane D - 2002 - .52
Jane D - 2003 - .50
so on and so on...
How do I write this in Transact-SQL to get this result set...
Thanks,
Roger
View 1 Replies
View Related
Aug 3, 2000
I have a table containing almost 700000 records. The table contains the following fields:
table_id, member#, car_eff_dt, car_term_dt, mbr_occur.
A member can occur more than one time and his effective date/termination date are sorted sequentially.
An example would look like this:
table_id member# car_eff_dt car_term_dt mbr_occur
1 12345678901 01/01/1995 12/31/1995 null
2 12345678901 01/01/1996 12/31/1996 null
3 12345678901 01/01/1997 12/31/1997 null
4 10987654321 07/15/1998 02/16/1999 null
5 10987654321 04/01/2000 12/31/9999 null
Using Transact-SQL, is it possible to generate a unique number for each occurance of a particular member#?
table_id member# car_eff_dt car_term_dt mbr_occur
1 12345678901 01/01/1995 12/31/1995 1
2 12345678901 01/01/1996 12/31/1996 2
3 12345678901 01/01/1997 12/31/1997 3
4 10987654321 07/15/1998 02/16/1999 1
5 10987654321 04/01/2000 12/31/9999 2
Currently, I am running the code in an Access/VBA module through an ODBC connection, but I would like to be able to run all my code directly on the server.
Thanks in advance for any help you might be able to provide.
View 1 Replies
View Related
Aug 28, 2002
Dear SQLGuru,
I came to know one of the better option in Oracle SQL for TOP-N Analysis-To find the top 5 usage accounts, top 10 users, etc. Is there any such way in SQL server ?
Is there any ways of using JOINS ( using keyword INNER & OUTER ) ? Some of the scripts that is generated automatically in Microsoft access with Join condition is not even working in MSSQL server 6.5. Is it true ? Or is there any other way that I can use these scripts in SQL 6.5.
If possible, pls. help me with some sample scripts ?
Thanks a lot in advance.
Naga
View 1 Replies
View Related
Sep 2, 1999
Hi!
I need help!
We have 98% transact log full.
After run
DUMP TRANSACTION WITH TRUNCATE ONLY it still doesn't purge the log.
How can we resolve problem.
Thank you,
sev
View 2 Replies
View Related
Jul 4, 2004
Ok here is the situation I need a little help with.
I am passing an XML document to a stored procedure. My XML document looks like this:
<ROOT>
<customer ID="10" Address="1234 Somewhere"></customer>
<customer ID="20" Address=""></customer>
</ROOT>
Now I use sp_xml_preparedocument and OpenXML to Insert the XML ID and Address attributes into a table in my database. The table has 2 fields (ID int) and (Address varchar(100) nullable)
The result of the insertion is 2 rows in my table with the ID field containing (10 and 20) and the Address field containing (1234 Somewhere and an empty string).
My first question is, why isn't the second row Address field in my database null instead of an empty string since thats what I would like happen. Is it because the XML Address atribute = "".
If that is the case then that should bring up my second question. Since the OpenXML inserts Address="" as an empty string instead of NULL, why doesn't a regular TransactSQL INSERT statement called from ASP does the same thing?? If I lost you here then let me give a quick example.
An html web form with a text field (txtAddress). The user leaves the text box empty and submits the form. My ASP takes the value of the form, Request.Form("txtAddress") and passes it to stored proceudre through an ASP parameter object. My stored procedure simply does an INSERT into tablename (ID, Address) VALUES (@passedID, @PassedAddress). Now the outcome in the database is that the Address field is NULL and not an empty string.
That is why im confused. Why arent the OpenXML and the straight-forward insertion yield the same result (either null or empty string). It seems to me that an empty text box is an empty string and not null. ASP's ISNull(txtAddress) agrees and returns false.
Any insight ot help on this issue is greatly appreciated.
View 1 Replies
View Related
Aug 19, 1999
What I am trying to do is iterate through a set of records from a table, and setting one field to a common value on each record, and setting another to an incremented value. Here it is:
Table contains columns A, B, and C.
A is a unique ID.
B is an ID.
C is another value.
I want to, for all records where field B = "AAA", Set B to "AAB", and set A on each record to the next unique ID that I get from somewhere else. Here's my SQL Stored Proc:
DECLARE MYCURSOR SCROLL Cursor FOR SELECT A, B FROM MyTable where B = 1 FOR UPDATE.
Loop:
FETCH NEXT INTO @vcA, @vcB FROM MYCURSOR.
UPDATE MYTABLE SET A = @nctr, B = "AAB" WHERE CURRENT OF MYCURSOR
SELECT @nctr = @nctr + 1
If @@FETCH_STATUS = 0 goto Loop:
I hit an error as soon as I get to the Update -
"Cursorfetch: Number of variables declared in the INTO list must match that of selected columns"
Go Figure. I really need an answer on this ASAP! Help!
View 1 Replies
View Related
Dec 8, 2005
Hello, I am having some transact SQL problems.
ALLOWED:
Column1,
CASE FOO
WHEN 1 THEN 'Hello'
ELSE 'Goodbye'
END as MyValue,
Column2
NOT ALLOWED:
Column1,
CASE FOO
WHEN > 1 THEN 'Hello'
ELSE 'Goodbye'
END as MyValue,
Column2
The problem seems to be the > symbol. Apparantly the case statement is only for equality checks, and nothing else.
So, what can I do to fix this? I have these kinds of comparisons all over.
~Le
View 2 Replies
View Related
