Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 dynamic sql "convert"

Author  Topic 

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-01-20 : 10:18:13
Hi. I get Invalid column name session_dtmRealshow

set @stmt = 'SELECT *
FROM (
SELECT s.session_lngSessionid,
' + convert(varchar(10), @datefrom, 104) + ' + '' - '' + ' + convert(varchar(10), @dateto-1, 104) + ' as week,
TC.Cinema_strname,screen_strdescription,Film_strTitle,Film_strTitlealt
,convert(varchar,F.Film_dtmOpeningDate,106) as National_Release_date, D.Distrib_strName,'
+ convert(varchar(10), Session_dtmRealShow,106)+' as Show_date, '
+ convert(char(5),Session_dtmRealShow, 108) + ' as Show_time' +'

-- ,S.Screen_bytNum
--, S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode
--,D.Distrib_strHODistribCode, D.Distrib_strCode ..........etc

How can i tell dsql that this is a column name on the query?
Thanks.

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-20 : 10:43:30
Where is your FROM statement? The table is probably aliased and you need to put that before the column reference.
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2015-01-20 : 11:12:11
don't need to prefix since it's unique.
So basically what i want to do is set date to times in a pivot way.
I am writing out what exactly should go out, replacing the values i need to pass with actual dates. This will give me all nulls

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


declare @datefrom datetime
declare @dateto datetime
declare @x nvarchar(max)

set @datefrom = '20150111'
set @dateto = '20150115'


declare
@cols nvarchar(max),
@stmt nvarchar(max)

select @cols = isnull(@cols + ', ', '') + '[' + T.show_date + ']' from (select distinct convert(varchar,Session_dtmRealShow,106) as Show_date FROM tblSession S LEFT JOIN tblFilm F ON S.Film_strCode = F.Film_strCode
LEFT JOIN tblCinema_Screen CS ON CS.Screen_bytNum = S.Screen_bytNum
--LEFT JOIN tblPrice_Group PG ON S.PGroup_strCode = PG.PGroup_strCode LEFT JOIN tblSession_Types ST on S.SType_strSessionTypeCode = ST.SType_strSessionTypeCode
LEFT JOIN tblEvent E on E.Event_strCode = S.Event_strCode LEFT JOIN tblCinema_Operator CO ON S.CinOperator_strCode = CO.CinOperator_strCode
LEFT JOIN tblDistributor D ON F.Distrib_strCode = D.Distrib_strCode
inner join tblCinema TC on TC.Cinema_strcode = CO.Cinema_strcode
WHERE Session_strStatus IN ('O','P','A') AND (Session_dtmRealShow >= @datefrom AND Session_dtmRealShow < @dateto)
-- excel has time between 3 to 11:59??
--and (convert(char(5), Session_dtmRealShow, 108) >= '15:00' and convert(char(5), Session_dtmRealShow, 108) <= '23:59')
---poli specific edo all etsi to exei sto excel..
--and co.cinoperator_strcode = 'FALM'
--and s.screen_bytNum in(4,5)

Group by
-- S.Screen_bytNum ,
screen_strdescription,
Film_strTitle,Film_strTitlealt,
S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
D.Distrib_strHODistribCode, D.Distrib_strCode, D.Distrib_strName,s.Screen_bytNum,Session_dtmRealShow,TC.Cinema_strname,s.session_lngSessionid,
F.Film_dtmOpeningDate) as T





set @stmt = 'SELECT *
FROM (
SELECT s.session_lngSessionid,
convert(varchar(10), ''20150111'', 104) + '' - '' + convert(varchar(10), +''20150113'', 104) as week,
TC.Cinema_strname,screen_strdescription,Film_strTitle,Film_strTitlealt
,convert(varchar,F.Film_dtmOpeningDate,106) as National_Release_date, D.Distrib_strName,
convert(varchar(10), Session_dtmRealShow,106) as Show_date,
convert(char(5),Session_dtmRealShow, 108) as Show_time
-- ,S.Screen_bytNum
--, S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode
--,D.Distrib_strHODistribCode, D.Distrib_strCode
FROM tblSession S LEFT JOIN tblFilm F ON S.Film_strCode = F.Film_strCode
LEFT JOIN tblCinema_Screen CS ON CS.Screen_bytNum = S.Screen_bytNum
--LEFT JOIN tblPrice_Group PG ON S.PGroup_strCode = PG.PGroup_strCode LEFT JOIN tblSession_Types ST on S.SType_strSessionTypeCode = ST.SType_strSessionTypeCode
LEFT JOIN tblEvent E on E.Event_strCode = S.Event_strCode LEFT JOIN tblCinema_Operator CO ON S.CinOperator_strCode = CO.CinOperator_strCode
LEFT JOIN tblDistributor D ON F.Distrib_strCode = D.Distrib_strCode
inner join tblCinema TC on TC.Cinema_strcode = CO.Cinema_strcode
WHERE Session_strStatus IN (''O'',''P'',''A'') AND (Session_dtmRealShow >= ''20150111'' AND Session_dtmRealShow < ''20150113'')
-- excel has time between 3 to 11:59??
--and (convert(char(5), Session_dtmRealShow, 108) >= ''15:00'' and convert(char(5), Session_dtmRealShow, 108) <= ''23:59'')
---poli specific edo all etsi to exei sto excel..
--and co.cinoperator_strcode = ''FALM''
--and s.screen_bytNum in(4,5)

Group by
-- S.Screen_bytNum ,
screen_strdescription,
Film_strTitle,Film_strTitlealt,
S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
D.Distrib_strHODistribCode, D.Distrib_strCode, D.Distrib_strName,s.Screen_bytNum,Session_dtmRealShow,TC.Cinema_strname,s.session_lngSessionid,
F.Film_dtmOpeningDate

) s
PIVOT
(
max(Show_time)
FOR [Show_date] IN (' + @cols + ')
)AS pvt'


exec sp_executesql @stmt = @stmt


What i wanted to achieve was not to have to input every single hour in the pivot so i could get result, so i though i could use the "IN" with Dsql so i can only pivot the included datetimes. If there is a solution in this please let me know, i do not want alternatives as i have done this and i only need an exact solution( if any, for education).
Thanks.
Go to Top of Page
   

- Advertisement -