dynamic sql "convert" - SQL Server Forums

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.

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources