Appending select statement

#1

I'm trying to get two fields to output as one.

I have one field that returns QTY's and another that returns unit of measure. I want to merge these together so it returns into one field.

Below runs but puts results of CONVERT and i.fmeasure into separate columns.

CONVERT(DECIMAL(10,2),(hand + inspec + order) - (proqty + book + proqty + onnetqty)), + i.fmeasure as [Avail]

#2

please provide sample data?

#3

what's the best way to give you some sample data?

#4 
create table #buchedata(hand int, inspec  int, order int, 
proqty  int, book int, proqty int, onnetqty int)

insert into #buchedata
select 1,2,3,4,5,6,7 union
select 1,2,3,4,5,6,7 union
select 1,2,3,4,5,6,7

but of course using read column data types and real values from your database

#5

Thank you.

Try this:
create table #buchedata(fonhand int, fqtyinspec int, fonorder int,
fproqty int, fbook int, fnonnetqty int, fmeasure char)

insert into #buchedata
select 4300,85,0,4,0,63,'EA' union
select 10452,0,1,4,0,56,'LBS' union
select 56326,0,2,85,0,44,'EA' union
select 52,0,1,4,0,13,'LBS' union
select 685,5,0,445,0,76,'EA '

if you run this:
SELECT
CONVERT(DECIMAL(10,2),(fonhand + fqtyinspec + fonorder) - (fproqty + fbook + fnonnetqty)), + fmeasure as [Avail],
fmeasure as [U/M]
FROM #buchedata

i'd want to output to be

169.00 E
4318.00 E
10393.00 L
...
...
...

not the QTY and fmeasure in separate columns

Hope that makes sense.

Thank you.

#6

did you test this sample data DML and DDL? there are errors

#7

How about this?

SELECT
CONVERT(DECIMAL(10,2),(fonhand + fqtyinspec + fonorder) - (fproqty + fbook + fnonnetqty)) as qty, 
left(fmeasure,1) as [U/M]
FROM #buchedata
#8

this would still give me 2 columns though

#9 

SELECT cast(CONVERT(DECIMAL(10,2),(fonhand + fqtyinspec + fonorder) - (fproqty + fbook + fnonnetqty)) as varchar(150)) + ' ' +  left(fmeasure,1) as [U/M]
FROM #buchedata
1 Like
#10

That worked. Thanks so much!