Placeholder column in a cursor

Question

Post reply

Placeholder column in a cursor

Jim W. White

Old Hand

Points: 346
More actions
May 4, 2005 at 9:54 am

#85887

I need to create a query of a single table and bring back five columns. The first four columns are from the table and the last column is a placeholder for numeric data that I'll fill in later in my program. This placeholder column needs to be defined as numeric 999999.9999. My query from Visual FoxPro using ODBC is:
SELECT EmployeeID, LastName, FirstName, MiddleInit, 000000.0000 AS salary FROM Employees
The cursor I get back in VFP is not what I expected. The Salary column is defined as numeric with a total width of six and with four decimal postions (9.9999) which causes a truncation of data when I update the cursor later. I've corrected the problem by changing my query to 999999.9999 AS Salary for the column in question and then I reset the column in my program to zero for all rows I get back. But is there a better way to do this within MS SQL so that I get a cursor back with a numeric column that has a total width of at least eleven with four decimal postions?
Jim White

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

What is the data type of Salary column and how do you define it?

You need define it as numeric (10, 4)

Jim W. White Old Hand Points: 346 More actions · Answer 2

It is a numeric column, but it is not a column that exists in the table that I'm selecting from. It's a column that I am creating dynamically at the time the cursor is generated. I want a column that is numeric in definition, ten bytes wide with four decimal positions. It is a placeholder for data I will add after the cursor is returned to my program. I want the column to be returned with the other columns I select with all zeros in each for each row. I'm going to manipulate the column later in my Visual FoxPro program by selecting data from another table in another database. The column only exists in the cursor.

Jim

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

SELECT EmployeeID, LastName, FirstName, MiddleInit, CAST(0 AS Decimal(10,4)) AS Salary FROM dbo.Employees

Jim W. White Old Hand Points: 346 More actions · Answer 4

Thanks, Remi! That worked. Earlier I tried using the CAST() function but instead of Decimal I specified Numeric which for some reason didn't work. When the cursor got to my VFP program the column was defined as Numeric(6,4) which caused the truncation. But specifying Decimal works.

Thanks again!

Jim White

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Numeric(6,4) = 6 numbers, 4 decimals (00.0000)

(10,4) = 10 numbers, 4 decimals (000000.0000)

Jim W. White Old Hand Points: 346 More actions · Answer 6

Sorry, I misspoke there. I tried Numeric(10,4), not Numeric(6,4).

Jim