Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Microsoft SQL - Question about data types.

Old 05-27-04, 02:05 PM
  #1  
DVD Talk Hero
Thread Starter
 
Join Date: Jan 2000
Location: Montreal, Canada
Posts: 29,516
Microsoft SQL - Question about data types.

Essentially, I'm reading an ISAM file and extracting data to a flat file, then FTP that file to an SQL server and loading it into a database. Problem is that negative numbers are stored in a somewhat weird fashion on that ISAM file. For example, -10 is stored as 1p, -11 as 1q, ans so on (don't ask ). No problem, when I do the extract I can just load the negative numerics into alpha fields so, for a 9 char field I get ' -10' on my output file.

Now if I know that some of the numeric fields are always positive, I can just output them as is. So if I have a 9 char field with a value of 10, it will output on my flat file as '0000000010'.

If I do that, the positive numbers will have leading zeros, but the negative ones (converted to alpha) won't. How does SQL handle this? Would I be better off just outputting ALL numeric fields to alpha fields?

P.S. I'm new to that SQL stuff.

One more thing, do I have to make '-10' '10-'? Does SQL expect the '-' sign to be at the end?

Last edited by eXcentris; 05-27-04 at 02:12 PM.
eXcentris is offline  
Old 05-27-04, 02:09 PM
  #2  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,704
If the positive numbers (whether output as numbers or alphas) are read into SQL Server numeric fields they won't have leading zeroes.
X is offline  
Old 05-27-04, 02:19 PM
  #3  
DVD Talk Hero
Thread Starter
 
Join Date: Jan 2000
Location: Montreal, Canada
Posts: 29,516
Ok so SQL doesn't care if it sees 10 with leading blanks or leading zeros?

One more thing, if a value is zero and I output that to alpha it will come out as blank. Will SQL translate that to zero if I set the data type to numeric for that field or do I have to ensure that '0' is actually loaded in that field?
eXcentris is offline  
Old 05-27-04, 02:32 PM
  #4  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,704
The leading zeroes or spaces should convert just fine.

A blank instead of a 0 would normally be considered a null value. You could define your table as using 0 for a default value for those columns that could have blanks (and not allowing null values for that field) or you could do queries to fill in nulls with 0 after the data is in.
X is offline  
Old 05-27-04, 02:43 PM
  #5  
DVD Talk Hero
Thread Starter
 
Join Date: Jan 2000
Location: Montreal, Canada
Posts: 29,516
Ok, thanks a bunch! Feels kindda weird just asking for stuff without arguing.
eXcentris is offline  
Old 05-27-04, 04:39 PM
  #6  
DVD Talk Legend
 
Shazam's Avatar
 
Join Date: Jul 1999
Location: Canuckistan
Posts: 10,027
You could define your table as using 0 for a default value for those columns that could have blanks (and not allowing null values for that field) or you could do queries to fill in nulls with 0 after the data is in.
It's usually better to do your first suggestion, rather than the second. Nullable fields occupy a bit more space in the database (that SQL Server uses to indicate whether or not the field is null or not).

As well, it introduces a tristate that might be just an annoyance than a help.
Shazam is offline  
Old 05-27-04, 11:38 PM
  #7  
DVD Talk Platinum Edition
 
Join Date: Oct 1999
Location: South Surrey, BC
Posts: 3,990
For example, -10 is stored as 1p, -11 as 1q, ans so on (don't ask ).
Those look like COBOL numeric-display values, which denote negatives via ORing the value 64 into the least significant digit.

I think that the decimal sign is at the front for negative SQL values. On COBOL, it might have been at the back.

RD
DivxGuy is offline  
Old 05-28-04, 08:12 AM
  #8  
DVD Talk Hero
Thread Starter
 
Join Date: Jan 2000
Location: Montreal, Canada
Posts: 29,516
DivxGuy, it's actually DIBOL which is essensially a VAX/VMS version of COBOL.

I decided to load '0' in numeric fields that have a null value in the front end while I do the data extract (using a "mask" like "ZZZZZZZZX-"). That way, I don't have to worry about it in SQL.
eXcentris is offline  
Old 06-01-04, 10:19 AM
  #9  
DVD Talk Hero
Thread Starter
 
Join Date: Jan 2000
Location: Montreal, Canada
Posts: 29,516
I have a question about the SQL numeric data types. When I define my data type as 'bigint' or 'decimal' I don't have control on the length of the field, it seems to be fixed at 8 (bigint) or 9 (decimal). Is there any way around that? I've got numeric fields in my file extract that are 20 char long (Dibol D18.2 extracted to A20).
eXcentris is offline  
Old 06-01-04, 10:54 AM
  #10  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,704
Money type gives you 19 digits, however 4 are to the right of the decimal place.

You may need to store in character form and convert to numeric in a program that is able to handle such high precision itself.
X is offline  
Old 06-01-04, 12:58 PM
  #11  
DVD Talk Legend
 
Shazam's Avatar
 
Join Date: Jul 1999
Location: Canuckistan
Posts: 10,027
Originally posted by eXcentris
I have a question about the SQL numeric data types. When I define my data type as 'bigint' or 'decimal' I don't have control on the length of the field, it seems to be fixed at 8 (bigint) or 9 (decimal). Is there any way around that? I've got numeric fields in my file extract that are 20 char long (Dibol D18.2 extracted to A20).
I implore you to read Books Online (it should have been installed when you installed the SQL Server client software) and gain an understanding of what the various datatypes are, and how to use to them.

In fact, it is entirely possible to create a decimal field that can contain 20 digits (NOT characters).
Shazam is offline  
Old 06-01-04, 01:05 PM
  #12  
DVD Talk Hero
Thread Starter
 
Join Date: Jan 2000
Location: Montreal, Canada
Posts: 29,516
I figured out that you can change the default precision value of DECIMAL fields from the default of 19, to a max of 38. This gives me a max field length of 17. I can probably deal with that in my extract program and load my D18.2 fields into A17 fields. I never get numbers that big anyway so I can eliminate some of the leading zeros.
eXcentris is offline  
Old 06-01-04, 01:27 PM
  #13  
DVD Talk Hero
Thread Starter
 
Join Date: Jan 2000
Location: Montreal, Canada
Posts: 29,516
Shazam, thanks. I'm on a web page now that describes all the data types. I just realized where the confusion comes from. "Length 9" refers to storage bytes, not to field size in characters. So, forget what I said.

I work from home. I connect to my company's SQL server using a remote connection. So I don't have the Books Online docs. But I guess I could always download it on my PC.
eXcentris is offline  
Old 06-01-04, 01:32 PM
  #14  
DVD Talk Legend
 
Shazam's Avatar
 
Join Date: Jul 1999
Location: Canuckistan
Posts: 10,027
Yes, you can download them from MS's site.

Man, working without BOL is like fumbling in the dark.
Shazam is offline  
Old 06-01-04, 01:43 PM
  #15  
DVD Talk Hero
Thread Starter
 
Join Date: Jan 2000
Location: Montreal, Canada
Posts: 29,516
For now, I use other web based ressources I found and I'm also quite adept at fumbling.
eXcentris is offline  
Old 06-01-04, 01:53 PM
  #16  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,704
Originally posted by eXcentris
I work from home. I connect to my company's SQL server using a remote connection.
I hope you're using VPN.
X is offline  
Old 06-01-04, 02:32 PM
  #17  
DVD Talk Hero
Thread Starter
 
Join Date: Jan 2000
Location: Montreal, Canada
Posts: 29,516
Originally posted by X
I hope you're using VPN.
Yes sir I do.
eXcentris is offline  
Old 06-01-04, 03:12 PM
  #18  
DVD Talk Legend
 
Shazam's Avatar
 
Join Date: Jul 1999
Location: Canuckistan
Posts: 10,027
Originally posted by eXcentris
For now, I use other web based ressources I found and I'm also quite adept at fumbling.
That's not what your girlfriend told me
Shazam is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive Advertising Cookie Policy Privacy Statement Terms of Service

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.