Formatting
-
I hope this is an appropriate forum to post this...I am very new to sql so bear with me if the lingo isn't correct.
I need to format an integer (i.e. 34587) to look like 345+87 (stationing). The plus sign is always before the last two numbers. The number of leading values will vary (i.e. 4533+56, 34+00). Also, decimals may be present (34587.45 reported as 345+87.45) so some how I need to recognize these and report appropriately.
Here is the existing code. Basically, I'm grabbing values from several tables and reporting to a single field. The values requiring formatting are the begin_station and end_station.
Thank you in advance. Also, if possible a little explanation would be great so I can understand what's happening.
create or replace view ncs_additional_lines_coating as
select ml.route_Id,
ml.begin_measure,
ml.end_measure,
ml.BEGIN_SERIES,
ml.BEGIN_STATION,
ml.end_series,
ml.end_station,
detail.LINE_DESIG_NBR,
detail.begin_series detail_begin_series,
detail.end_series detail_end_series,
detail.begin_station detail_begin_station,
detail.end_station detail_end_station,
coating.description coating_description,
'Line: ' || lpad(trim(detail.LINE_DESIG_NBR), 8) || lpad(detail.begin_series || '/' || detail.begin_station, 12) || lpad(detail.end_series || '/' || detail.end_station, 12) || lpad(detail.end_measure - detail.begin_measure || '''', 6) || lpad(coating.description, 53) as sheet_description
from ncs_additional_lines_v1,
ncs_additional_lines_detail_v1 detail,
ncs_asg_matchline_v2 ml,
ncs_asg_external_coating_v1 coating
where ncs_additional_lines_v1.LINE_IDn = detail.LINE_IDn and
ncs_additional_lines_v1.sheet_nbr = ml.SHEET_NBR
-
That looks like Oracle syntax with the "||" string concatenation operator.
Oracle & Sql Server have different string manipulation functions that will be required for your station parsing/formatting, so are you looking for a Sql Server or Oracle solution ?
-
Yep... I concur with PW... "Create or Replace" is definitely Oracle code and the string functions available are quite different in SQL Server.
So, which do you need, Oracle Code or SQL Server code?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yea, sorry about that. I am hitting an oracle database and got twisted up b/c I am using PL/SQL developer...like it says next to my name...NEWBIE. I'll get there. So, anyhow...Oracle code.
Thanks for the replies. Any help still appreciated, but I understand I am in the WRONG place.
Lates.
RH
-
Well, here's one way to do this in TSQL, but I'm sure it's irrelevant for Oracle
--data
declare @t table (x decimal(10, 2))
insert @t
select 34587
union all select 453356
union all select 3400
union all select 34587.45
union all select 34587.55
union all select 99999
union all select 99999.9
union all select 99999.99
union all select 10000.01
union all select 3401
union all select 1
union all select 0.1
--calculation
select LeftBit + '+' + case when LEFT(RightBit + '.', 2) LIKE '[0-9].%' then '0' else '' end + RightBit from
(select
cast(floor(x/100) as varchar(10)) as LeftBit,
cast(cast(x - floor(x/100)*100 as float) as varchar(10)) as RightBit
from @t) a
/*results
345+87
4533+56
34+00
345+87.45
345+87.55
999+99
999+99.9
999+99.99
100+00.01
34+01
0+01
0+00.1
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I should've mentioned that, in general, it's better to do formatting at the front-end...
Of course, there are always exceptions.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply