July 9, 2019 at 11:23 am
Hi all,
Please I need help on how to get all the columns using the below TSQL. The error is that the Updated_On column could not be bound. I really need the Updated_On column to be part of my column list.
select EventType as [Event Type], ccro as [Company Number], ccompany as [Company Name], pcode1 as [Postcode],
dateadd(dd,0, datediff(dd,0, getdate())) as [Event Date], p.updated_on, (year(getdate()) *100) + month(getdate()) as [Month],
isnull(locale, 'Unknown') as [Locale],isnull(town, 'Unknown') as [Town], isnull(county, 'Unkown') as [County], isnull([RFA Region], 'Unknown') as Region, cast(NULL as nvarchar(4000)) as [text]
--into bo_custom_TT.dbo.detrimental_events_report_icc_1rf_1
from (select * from openquery (IRON, 'select b.ccro, b.ccompany, b.cpostcode as pcode1, a.updated_on, cast(coalesce(cl_gr_nw_null, 0) as char(1)) CLGNW,
cast(acc_old_23 as char(1)) ACOLD, cast(rp_n_3_yrs as char(1)) RPNEG, cast(rp_d_3_yrs_ln as char(1)) RPDLN,
cast(nw_d_3_yrs_ln as char(1)) NWDLN , cast(rp_d_2_yrs_nw_n as char(1)) RPNWN, cast(wcd_gr_nw as char(1)) WCDNW,
cast(wcd_and_nw_n as char(1)) NNWWC, cast(nw_d_50_pc_1_yr as char(1)) NWDPY, cast(wcd_d_50_pc_1_yr as char(1)) WCDPY
FROM lookup.gsb_override a
inner join redlight.lrg b on a.nlrg = b.nid
inner join redlight.triggers c on b.ccro = c.ccro
where c.ltd = 1')) p
UNPIVOT
(EventValue for EventType in ( [CLGNW], [ACOLD], [RPNEG], [RPDLN], [NWDLN], [RPNWN] , [WCDNW], [NNWWC], [NWDPY], [WCDPY])
) as unpvt
left outer join [dbo].[uk_postcodes] a on a.postcode = pcode1
where EventValue = 1
July 9, 2019 at 12:13 pm
I was able to resolve this by making the open query select list a CTE and selecting from the CTE.
July 9, 2019 at 12:27 pm
I was able to resolve this by making the open query select list a CTE and selecting from the CTE.
also, procedure sp_describe_first_result_set can be useful in such case
link:
July 9, 2019 at 1:35 pm
A reformatted version. Don't know how you can read the one you posted!
SELECT [Event Type] = EventType
,[Company Number] = ccro
,[Company Name] = ccompany
,Postcode = pcode1
,[Event Date] = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
,p.updated_on
,Month = (YEAR(GETDATE()) * 100) + MONTH(GETDATE())
,Locale = ISNULL(locale, 'Unknown')
,Town = ISNULL(town, 'Unknown')
,County = ISNULL(county, 'Unkown')
,Region = ISNULL([RFA Region], 'Unknown')
,text = CAST(NULL AS NVARCHAR(4000))
--into bo_custom_TT.dbo.detrimental_events_report_icc_1rf_1
FROM
(
SELECT *
FROM OPENQUERY
(IRON
,'select b.ccro, b.ccompany, b.cpostcode as pcode1, a.updated_on, cast(coalesce(cl_gr_nw_null, 0) as char(1)) CLGNW,
cast(acc_old_23 as char(1)) ACOLD, cast(rp_n_3_yrs as char(1)) RPNEG, cast(rp_d_3_yrs_ln as char(1)) RPDLN,
cast(nw_d_3_yrs_ln as char(1)) NWDLN , cast(rp_d_2_yrs_nw_n as char(1)) RPNWN, cast(wcd_gr_nw as char(1)) WCDNW,
cast(wcd_and_nw_n as char(1)) NNWWC, cast(nw_d_50_pc_1_yr as char(1)) NWDPY, cast(wcd_d_50_pc_1_yr as char(1)) WCDPY
FROM lookup.gsb_override a
inner join redlight.lrg b on a.nlrg = b.nid
inner join redlight.triggers c on b.ccro = c.ccro
where c.ltd = 1'
)
) p
UNPIVOT
(
EventValue
FOR EventType IN (CLGNW, ACOLD, RPNEG, RPDLN, NWDLN, RPNWN, WCDNW, NNWWC, NWDPY, WCDPY)
) unpvt
LEFT OUTER JOIN dbo.uk_postcodes a
ON a.postcode = pcode1
WHERE EventValue = 1;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 9, 2019 at 2:19 pm
eobiki10 wrote:I was able to resolve this by making the open query select list a CTE and selecting from the CTE.
also, procedure sp_describe_first_result_set can be useful in such case link: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql
Personally, I recommend sys.dm_exec_describe_first_result_set(); it's much easier to use than sys.sp_describe_first_result_set . You can limit what columns you want, use the INTO clause to get it into a table, etc. sys.dm_exec_describe_first_result_set (Transact-SQL)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply