CR 8.5: Subselect field doesn't appear in field list 1

[azoe]

I have created a stored procedure from the query below to use in my report.

The problem is that the only field that shows up in the "Insert Fields" list is td.adj_amt. I also need to display rcm.desc_40 (in the subselect).

Why isn't it there and/or how can I get it there?

Thank you -


SELECT

(Select Top 1 rcm.desc_40
From reason_code_links rcl
left outer join reason_code_mstr rcm on rcl.reason_code_id = rcm.reason_code_id
Where rcl.trans_id = t.trans_id),
td.adj_amt

FROM
trans_detail td
inner join transactions t on td.trans_id = t.trans_id

where
t.closing_date between '20050302' and '20050401'
and t.type = 'A'

 

[vidru]

You need to alias the column you're returning from the subquery:

SELECT desc_40 =
(Select Top 1 rcm.desc_40
From reason_code_links rcl
left outer join reason_code_mstr rcm on rcl.reason_code_id = rcm.reason_code_id
Where rcl.trans_id = t.trans_id), td.adj_amt
etc......

-dave

 

[azoe]

Thanks that was it!

Have a great weekend.