I have a field called [OEOLL].[quantity_stk]. it can be xxxx.xx For this example, lets say a record has a value of 40.00.
I would like the value to be returned as 004000. The field should be 6 characters, zero filled, and should be the value of [OEOLL].[quantity_stk] * 100.
Here is my statement so far:
RIGHT('000000'+ISNULL([OEOLL].[Quantity_Stk] * 100,0) ,6)
on that 40.00 it is returning 000.00 I need it to drop the decimals (including decimal point). I think then it will show correctly.
What if your [OEOLL].[Quantity_Stk] is greater than 9999? Code is going to silently remote the most significant order of magnitude figure Hopefully that column is defined as numeric 9999.99 and won't accept any larger value?
Thank you but yes, that field cannot be larger than 9999.99. And the equipment used to trap the that number cannot handle numbers bigger than this. That figure is a small portion of a full UPC Barcode and universally it cannot be bigger than xxxxxx. In this particular case all transactions must be 2 decimals, so sticking with only 6 figures, it cannot be more than xxxx.xx. But again, thanks for double checking (I have figured that out the hardware in the past!).
I have a field [sic: column] called OEOLL.stock_qty. it can be xxxx.xx <<
Please read any book on SQL. A field is part of a temporal value in this language. Then read a book on data modeling with a chapter on the ISO-11179 and Metadata L8 rules for data element names. The "_qty" postfix is called an attribute property.
I would like the value to be returned as 004000. The field [sic] should be 6 characters, zero filled, and should be the value of OEOLL.stock_qty * 100. <<
NO! That is how COBOL works. SQL is based on a tiered architecture where data is in a database layer and then you can pass it to a presentation layer, where you apply a COBOL PICTURE clause. We would have DDL (which you failed to post; narratives are rude) like this:
See how we use abstract data types and constraints, instead strings and computation? Totally different mindset! The first few months of switching to a totally different language are the worst; you will keep using old programming model, then one day you have a epiphany and start writing real SQL instead of kludges.
I want to thank you for basically calling me an idiot. Just so we are square, I am not a DBA nor do I want to be or care to be. I needed a simple answer to a simple question about pulling data out of COLUMNS in a table in a database that I had nothing to do with designing. I wanted it in a specific format. Sorry I so RUDELY sent a narrative. Pot, Kettle by the way, your response was as rude as it gets and completely unnecessary since it was plain to see I already had my answer and you were 2 days late to the party. Way to swoop in a save the day! Now would someone please kick me out of this group so I don't get anymore emails????
No reason to malign DBAs. Celko is not a DBA. He's some type of over-theorized consultant that pointy-haired upper-level managers hire because he throws around a lot of gobbledygook, like all such vastly overpaid "consultants" who never actually do any meaningful work.
No reason to malign DBAs. Celko is not a DBA. He's some type of over-theorized consultant that pointy-haired upper-level managers hire because he throws around a lot of gobbledygook, like all such vastly overpaid "consultants" who never actually do any meaningful work. <<
Actually, I throw ANSI/ISO standards and best practices based on research in training classes for working programmers. The actual RDBMS theory people say I am too much into SQL and applications. And I have not been vastly overpaid since the Dot-Com bubble burst Ghod, I miss that!
“He who loves practice without theory is like the sailor who boards ship without a rudder and compass and never knows where he may cast.”
Leonardo da Vinci
I am one of the people who defined the SQL language and gave over a decade of his life with the standards process. Then i wrote a classic series of books that have stayed in print for decades (do you know how rare more than one edition is for Geek books?).
If this were a medical forum and a posting wanted a cure for acne, would you have replied “take arsenic or lead salts” as an answer? Heavy metal poisons raise the white blood cell count and cure local infections. It works really quick. But it is not so good long term.
.
This guy is a kid who wants other people to do his job for him because he cannot do it. He did not read the forum rules about DDL and specs. His question was as absurd as “On scale from 1 to 10, what color is your favorite letter of the alphabet?”, but nobody pointed it out. When I did, he got mad and whiny.
He was looking for a kludge. I do not give kludges; I do not think that is professional or good for IT in general. Oh, by the way, what are your contributions to the database profession?
@jcelko why don't you go boost your ego somewhere else. This site (unless I'm totally misinformed) is for helping people, NOT putting people down. Reading your posts, I rarely see you helping people. You proclaim how good you are (and especially how stupid the rest of us are) and that we should read books (I bet you mean the books you've written). No doubt you know alot about databases, but you know nothing about, leaving a good impression when helping people solving their problem. I'm getting tired of reading your insulting answers, so I'm hoping you will change your attitude or be excluded! Have you ever considered not posting, when you only have negative thing to say!
Ps.: You could be awesome to learn from (like many other guys in here).
Comparing yourself to Da Vinci?? Here's a quote for you, and could be about you:
"There but for the grace of God goes ... God."
The first general-purpose computer was a colossal ( ) kludge. I guess we should not have read Luftwaffe and U-boat messages until the theory was right and the machine matched the theory. System R was a kludge. Indeed, every modern RDBMS is a kludge compared to Codd's 13 rules. By your lights, they should never have been released, as they certainly weren't theoretically up to par. I read Codd and Date for theory, not you. My loss I'm sure.
You -- like Da Vinci, btw -- have royal benefactors and thus can luxuriate in theory all you want. Some of us have to get actual, practical work done. We're not gods and don't pretend to be. We use theory as much as we can, and admittedly know less than true theoretical experts, but aren't allowed time to make everything perfect.
Btw, believe me, you are the only one on the planet obsessed with COBOL, and particularly with it being some type of insult. I dare say that many companies wouldn't be able to pay you your bloated amounts without COBOL in their history, and maybe even in their present.
I stumbled over something just the other day which would safeguard this situation - it uses REPLICATE instead of RIGHT. Notwithstanding that you have a limit, currently, but it would mean that if the column were to be made wider your code would fail-safe.
SELECT ProductCode
, RIGHT('00000' + ProductCode, 5) AS [RIGHT]
, REPLICATE('0', 5-LEN(ProductCode))+ProductCode AS [REPLICATE]
FROM
(
SELECT '1' AS ProductCode
UNION ALL SELECT '21'
UNION ALL SELECT '321'
UNION ALL SELECT '4321'
UNION ALL SELECT '54321'
UNION ALL SELECT '654321' -- REPLICATE() integer_expression will be negative
) AS T
Wasn't meant to malign DBA's. I just meant i personally won't be one, not for any reason other than i am perfectly happy where i am at. But i have often wondered about the true value of consulting. We have customers who pay consultants. Those consultants call us to do things. We bill the customer for those jobs. I'll bet the consultants are billing the customer too.
I've been in IT for over 35 years, and at some very large companies, and I've yet to meet a single really good, useful consultant. Literally, I swear, not one. Maybe it should be: "Those who can't do, consult".
Hopefully that column is defined as numeric 9999.99 and won't accept any larger value?
Ghod, I miss that!
) kludge. I guess we should not have read Luftwaffe and U-boat messages until the theory was right and the machine matched the theory. System R was a kludge. Indeed, every modern RDBMS is a kludge compared to Codd's 13 rules. By your lights, they should never have been released, as they certainly weren't theoretically up to par. I read Codd and Date for theory, not you. My loss I'm sure.