Case Statement in openQuery - 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 2005 Forums

Transact-SQL (2005)

Case Statement in openQuery

Author

Topic

Adis
Starting Member

2 Posts

Posted - 2009-04-20 : 02:37:01

set @sql = 'select *
from openquery(['+@remote_server+'],''
select Null as TAARICH_BIZUA_F,case when STATUS_DIVUACH_S_A_F = ''''9'''' then ''''E'''' when STATUS_DIVUACH_S_A_F = ''''2'''' then ''''M'''' end,
MAHLAKA,MISPAR_AOBD_F,ltrim(rtrim(MSYMH_F)),rtrim(MK_T_HAPARIT_F),t.SUG_HAZMANA_F,t.MISPAR_HAZMANA_F,sum(SHAOT_BEFOAL_F)
from '+@data_source+'.dbo.PCS_SHOP_BMTRANS t left join '+@data_source+'.dbo.PCS_PRODUCT_BMROUTE p
on (t.MISPAR_HAZMANA_F=p.MISPAR_HAZMANA_F and t.SUG_HAZMANA_F =p.SUG_HAZMANA_F and t.MISPAR_SHALAV_HAZMANA_F=p.MISPAR_SIDURI_SLB_F and t.PIZUL_SHALAV_HAZMANA_F=p.MISPAR_PIZUL_SLB_F)
where ltrim(rtrim(MSYMH_F)) = '''''+@tNum+'''''
AND (STATUS_DIVUACH_S_A_F = ''''9'''' or STATUS_DIVUACH_S_A_F = ''''2'''')
AND SUG_TNOAH_F = '''''+@sug+'''''
AND (BITUL_TIKUN_TNOAH_F != '''''+@kod_bitul+''''' and BITUL_TIKUN_TNOAH_F != ''''D'''')
AND (TAARICH_BIZUA_F between '+CONVERT(VARCHAR(8), @sdate, 112)+' and '+CONVERT(VARCHAR(8), @edate, 112)+' )
group by STATUS_DIVUACH_S_A_F,MISPAR_MAHLAKA_F,MAHLAKA_F,MISPAR_AOBD_F,MSYMH_F,MK_T_HAPARIT_F,t.SUG_HAZMANA_F,t.MISPAR_HAZMANA_F'')'
----------------------------------------------------------------------
This openquery is working as well but when I"m trying to use case statement instead of the 'MAHLAKA' field I'm getting weird results.
All I want to do is to replace the 'MAHLAKA' in the openquery to the following case statement:
Case When MAHLAKA Is null then MISPAR_MAHLAKA_F else MAHLAKA end
but it's doesn't work, and I'm getting the following message :

Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string '
select Null as TAARICH_BIZUA_F,case when STATUS_DIVUACH_S_A_F = '9' then 'E' when STATUS_DIVUACH_S_A_F = '2' then 'M' end,
case when MAHLAKA_F is null then MAHLAKA_F else MISPAR_MAHLAKA_F end,MISPAR_AOBD_F,ltrim(rtrim(MSYMH_F)),rtrim(MK_T_HAPARIT_F),t.SUG_HAZMANA_F,t.MISPAR_HAZMANA_F,sum(SHAOT_BEFOAL_F)
from ormat_erp.dbo.PCS_SHOP_BMTRANS t left join ormat_erp.dbo.PCS_PRODUCT_BMROUTE p
on (t.MISPAR_HAZMANA_F=p.MISPAR_HAZMANA_F and t.SUG_HAZMANA_F =p.SUG_HAZMANA_F and t.MISPAR_SHALAV_HAZMANA_F=p.MISPAR_SIDURI_SLB_F and t.PIZUL_SHALAV_HAZMANA_F=p.MISPAR_PIZUL_SLB_F)
where ltrim(rtrim(MSYMH_F)) = '0007023'
AND (STATUS_DIVUACH_S_A_F = '9' or STATUS_DIVUACH_S_A_F = '2')
AND SUG_TNOAH_F = '?'
AND (BITUL_TIKUN_TNOAH_F != '?' and BITUL_TIKUN_TNOAH_F != 'D')
AND (TAARICH_BIZUA_F between 20090105 and 20090105 )
group by STATUS_DIVUACH_S_A_F,MISPAR_MAHLAKA_F,MAHLAKA_F,MISPAR_AOBD_F,MSYMH_F,MK_T_HAPARIT_F,t.SUG_HAZMANA'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '
select Null as TAARICH_BIZUA_F,case when STATUS_DIVUACH_S_A_F = '9' then 'E' when STATUS_DIVUACH_S_A_F = '2' then 'M' end,
'.

If someone can help me... Thanks.

Kokkula
Starting Member

41 Posts

Posted - 2009-04-20 : 04:08:49

Hello

Try this


set @sql = 'select *
from openquery(['+@remote_server+'],''
select Null as TAARICH_BIZUA_F,case when STATUS_DIVUACH_S_A_F = ''9'' then ''E'' when STATUS_DIVUACH_S_A_F = ''2'' then ''M'' end,
Case When MAHLAKA Is null then MISPAR_MAHLAKA_F else MAHLAKA end ,MISPAR_AOBD_F,ltrim(rtrim(MSYMH_F)),rtrim(MK_T_HAPARIT_F),t.SUG_HAZMANA_F,t.MISPAR_HAZMANA_F,sum(SHAOT_BEFOAL_F)
from '+@data_source+'.dbo.PCS_SHOP_BMTRANS t left join '+@data_source+'.dbo.PCS_PRODUCT_BMROUTE p
on (t.MISPAR_HAZMANA_F=p.MISPAR_HAZMANA_F and t.SUG_HAZMANA_F =p.SUG_HAZMANA_F and t.MISPAR_SHALAV_HAZMANA_F=p.MISPAR_SIDURI_SLB_F and t.PIZUL_SHALAV_HAZMANA_F=p.MISPAR_PIZUL_SLB_F)
where ltrim(rtrim(MSYMH_F)) = '''+@tNum+'''
AND (STATUS_DIVUACH_S_A_F = ''9'' or STATUS_DIVUACH_S_A_F = ''2'')
AND SUG_TNOAH_F = '''+@sug+'''
AND (BITUL_TIKUN_TNOAH_F != '''+@kod_bitul+''' and BITUL_TIKUN_TNOAH_F != ''D'')
AND (CAST(TAARICH_BIZUA_F AS NVARCHAR) between '''+CONVERT(VARCHAR(8), @sdate, 112)+''' and '''+CONVERT(VARCHAR(8), @edate, 112)+''' )
group by STATUS_DIVUACH_S_A_F,MISPAR_MAHLAKA_F,MAHLAKA_F,MISPAR_AOBD_F,MSYMH_F,MK_T_HAPARIT_F,t.SUG_HAZMANA_F,t.MISPAR_HAZMANA_F'')'

Thanks,
Pavan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources