Appending select statement

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]

please provide sample data?

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

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

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.

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

How about this?

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

this would still give me 2 columns though


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

That worked. Thanks so much!