转换varchar值'exp时转换失败。 upto apr'到数据类型int。 [英] Conversion failed when converting the varchar value 'exp. Upto apr' to data type int.

问题描述

< pre>（ SELECT   distinct  g。*， '  Exp。Upto' +  CONVERT （  VARCHAR （ 3 ），DATEADD（MONTH，-1，（ CONVERT （ varchar （ 11 ）， '  05/01/2018'， 101 ）））， 109 ） AS  '  upto_pretitle'，  -    cast（A.my_NvarcharColumn as INT） 
 '  Exp。在' +  CONVERT 期间（  VARCHAR （ 3 ），（ CONVERT （ DATETIME ，'  05/01/2018'， 101 ））， 109 ） AS  '  during_monthtitle'，isnull（ Null ， 0 ） as  upto_preamt，isnull（ Null ， 0 ） as  during_monthamt 来自 
（ SELECT  e。*  FROM  
（ SELECT   DISTINCT  ddocode，majorheadcode 
，submajorheadcode，minorheadcode，subheadcode，detailedhead，
 so ecode， CASE   WHEN  subsoecode = ' < span class =code-string> 00' 那么 ' '  ELSE  subsoecode  END   AS  subsoe 
  FROM  enggbud 其中金额> 0 和 finyr = '  2018-2019' 和 level = '  02'）e  inner   join  
（ select  abc.circle，abc .div_ddocd，abc.div_nm，efg.cir_ddocd，efg.circlenm 来自 
（选择圈，ddo_cd  as  div_ddocd，division，nm  as  div_nm 来自 orgn_stru 其中 ddo_cd   < span class =code-keyword> not   null  和 division   不  null  
  group   by  circle，ddo_cd，division，nm）abc  left   join  
（ SELECT   DISTINCT  ddocode < span class =code-keyword> AS  cir_ddocd，ddoname  as  circlenm，circle 
  from  ddomaster  WHERE  ddocode   不  null ）efg  ON  abc.circle = efg.circle）f 
  ON  e .ddocode = f.div_ddocd）g  inner   join  
 budhead_mas  ON  g.majorheadcode = budhead_mas.MAJOR 和 g.submajorheadcode = budhead_mas.SUB_MAJOR 和 
 g.minorheadcode = budhead_mas.MINOR 和 g.subheadcode = budhead_mas.SUB_HEAD 和 g.detailedhead = budhead_mas.DET_HEAD 
 和 g.soecode = budhead_mas.OBJ_CD）
  union   all  
 
  SELECT  ddo_cd  as  ddocode，major  as  majorheadcode，sub_major submajorheadcode，minor minorheadcode，sub_head subheadcode，detailed_head  as  detailedhead，oc  as  soecode，soc subsoe，
 SUM（ CASE   WHEN  passingDate> = '  04/01/2018' < span class =code-keyword>和 passingDate< '  05/01/2018' 和 status = '  P' 那么金额< span class =code-keyword> ELSE   0   END ） AS  upto_preamt，
 SUM（ CASE   WHEN 传递日期> ; = '  05/01/2018' 和 passingDate< '  06/01/2018' 和 status = '  P' 那么金额 ELSE   0   END ） < span class =code-keyword> AS  during_monthamt，
 cast（ NULL   as   varchar （ 50 ）） as  upto_pretitle，cast（ NULL   as   varchar （  50 ）） as  during_monthtitle 
 
  FROM  vExpendivision  WHERE  
 status = '  P'  group  按 
 major，sub_major，minor，sub_head ，detailed_head，oc，soc，ddo_cd 

我尝试过：



i想通过union all尝试从两个查询中连接数据

解决方案

< pre>（ SELECT   distinct  g。*，< span class =code-string>'  Exp。最多' +  CONVERT （ VARCHAR （ 3 ），DATEADD（MONTH，-1，（ CONVERT （ varchar （ 11 ），'  05/01/2018'，< span class =code-digit> 101 ）））， 109 ） AS  < span class =code-string>'  upto_pretitle'，  -   强制转换（A.my_NvarcharColumn为INT） 
 '  Exp。在' +  CONVERT 期间（ VARCHAR （ 3） ），（ CONVERT （ DATETIME ，'   05/01/2018'， 101 ））， 109 ） AS  '  during_monthtitle '，isnull（ Null ， 0 ） as  upto_preamt，isnull（ Null ， 0 ） as  during_monthamt 来自 
（ SELECT  e。*  FROM  
（ SELECT   DISTINCT  ddocode，ma jorheadcode 
，submajorheadcode，minorheadcode，subheadcode，detailedhead，
 soecode， CASE   WHEN  subsoecode = '  00' 那么 ' '  ELSE  subsoecode  END   AS  subsoe 
  FROM  enggbud  where  amount> 0 和 finyr = '  2018-2019' 和 level = '  02'）e  inner   join  
（选择 abc.circle，abc.div_ddocd，abc.div_ nm，efg.cir_ddocd，efg.circlenm 来自 
（选择圈，ddo_cd  as  div_ddocd，division，nm  as  div_nm  from  orgn_stru < span class =code-keyword>其中 ddo_cd   不  null  和 division   不  null  
  group  通过 circle，ddo_cd，division，nm）abc  left   join  
（< span class =code-keyword> SELECT   DISTINCT  ddocode  AS  cir_ddocd，ddoname  as  circlenm，ci rcle 
 来自 ddomaster  WHERE  ddocode   不  null ）efg  ON  abc .circle = efg.circle）f 
  ON  e.ddocode = f.div_ddocd）g  inner   join  
 budhead_mas  ON  g.majorheadcode = budhead_mas.MAJOR 和 g.submajorheadcode = budhead_mas.SUB_MAJOR 和 
 g.minorheadcode = budhead_mas.MINOR 和 g.subheadcode = budhead_mas.SUB_HEAD 和 g.detailedhead = budhead_mas.DET_HEAD 
 和 g.soecode = budhead_mas.OBJ_CD）
  union   all  

  SELECT  ddo_cd  as  ddocode，major  as  majorheadcode，sub_major submajorheadcode，minor minorheadcode，sub_head subheadcode，detailed_head  as  detailedhead，oc  as  soecode， soc subsoe，
 cast（ NULL   as   varchar （ 50 ）） as  upto_pretitle，cast（ NULL   as   varchar （ 50 ）） as  during_monthtitle，
 SUM（ CASE   WHEN  passingDate> = '  04/01/2018' 一nd  passingDate< '  05/01/2018' 和 status = '  P' 那么金额< span class =code-keyword> ELSE   0   END ） AS  upto_preamt，
 SUM（ CASE   WHEN 传递日期> ; = '  05/01/2018' 和 passingDate< '  06/01/2018' 和 status = '  P' 那么金额 ELSE   0   END ） < span class =code-keyword> AS  during_monthamt 
 
 
  FROM  vExpendivision  WHERE  
 status = '  P'  group   by  
 major，sub_major，minor，sub_head，detailed_head，oc，soc，ddo_cd 

<pre>(SELECT  distinct g.*, 'Exp. Upto ' + CONVERT(VARCHAR(3), DATEADD (MONTH , -1 ,(CONVERT(varchar(11),'05/01/2018',101))),109) AS 'upto_pretitle',   --cast(A.my_NvarcharColumn as INT) 
	'Exp. During ' + CONVERT(VARCHAR(3), (CONVERT(DATETIME,'05/01/2018',101)),109) AS  'during_monthtitle',isnull(Null,0) as upto_preamt ,isnull(Null,0) as during_monthamt from
	(SELECT e.*   FROM
	(SELECT DISTINCT ddocode,majorheadcode
	,submajorheadcode,minorheadcode,subheadcode,detailedhead,
	soecode, CASE WHEN subsoecode = '00' THEN '' ELSE subsoecode END AS subsoe
	FROM enggbud where amount>0 and finyr='2018-2019' and level='02' ) e inner join 
	(select abc.circle,abc.div_ddocd,abc.div_nm,efg.cir_ddocd,efg.circlenm from 
	(select circle,ddo_cd as div_ddocd,division,nm as div_nm from orgn_stru where ddo_cd is not null and division is not null 
	group by circle,ddo_cd,division,nm)abc left join
	(SELECT DISTINCT ddocode AS cir_ddocd,ddoname as circlenm,circle 
	from ddomaster WHERE ddocode is not null )efg ON abc.circle=efg.circle) f
		ON e.ddocode=f.div_ddocd ) g inner join
		budhead_mas  ON g.majorheadcode=budhead_mas.MAJOR and g.submajorheadcode=budhead_mas.SUB_MAJOR and
		g.minorheadcode=budhead_mas.MINOR and g.subheadcode=budhead_mas.SUB_HEAD and g.detailedhead=budhead_mas.DET_HEAD 
		and g.soecode=budhead_mas.OBJ_CD) 
union all

    SELECT ddo_cd as ddocode,major as majorheadcode,sub_major submajorheadcode,minor minorheadcode,sub_head subheadcode,detailed_head as detailedhead,oc as soecode,soc subsoe,
	SUM(CASE WHEN passingDate >= '04/01/2018'  and passingDate < '05/01/2018' and  status='P' THEN Amount ELSE 0 END)  AS upto_preamt,
    SUM(CASE WHEN passingDate >= '05/01/2018' and passingDate < '06/01/2018' and status='P' THEN Amount ELSE 0 END) AS during_monthamt,
	cast(NULL as varchar(50)) as upto_pretitle, cast(NULL as varchar(50)) as during_monthtitle
	
	 FROM vExpendivision WHERE 
	status='P'  group by 
	major,sub_major,minor,sub_head,detailed_head,oc,soc,ddo_cd

What I have tried:

i want to try join data from two query through union all

解决方案

<pre>(SELECT  distinct g.*, 'Exp. Upto ' + CONVERT(VARCHAR(3), DATEADD (MONTH , -1 ,(CONVERT(varchar(11),'05/01/2018',101))),109) AS 'upto_pretitle',   --cast(A.my_NvarcharColumn as INT) 
	'Exp. During ' + CONVERT(VARCHAR(3), (CONVERT(DATETIME,'05/01/2018',101)),109) AS  'during_monthtitle',isnull(Null,0) as upto_preamt ,isnull(Null,0) as during_monthamt from
	(SELECT e.*   FROM
	(SELECT DISTINCT ddocode,majorheadcode
	,submajorheadcode,minorheadcode,subheadcode,detailedhead,
	soecode, CASE WHEN subsoecode = '00' THEN '' ELSE subsoecode END AS subsoe
	FROM enggbud where amount>0 and finyr='2018-2019' and level='02' ) e inner join 
	(select abc.circle,abc.div_ddocd,abc.div_nm,efg.cir_ddocd,efg.circlenm from 
	(select circle,ddo_cd as div_ddocd,division,nm as div_nm from orgn_stru where ddo_cd is not null and division is not null 
	group by circle,ddo_cd,division,nm)abc left join
	(SELECT DISTINCT ddocode AS cir_ddocd,ddoname as circlenm,circle 
	from ddomaster WHERE ddocode is not null )efg ON abc.circle=efg.circle) f
		ON e.ddocode=f.div_ddocd ) g inner join
		budhead_mas  ON g.majorheadcode=budhead_mas.MAJOR and g.submajorheadcode=budhead_mas.SUB_MAJOR and
		g.minorheadcode=budhead_mas.MINOR and g.subheadcode=budhead_mas.SUB_HEAD and g.detailedhead=budhead_mas.DET_HEAD 
		and g.soecode=budhead_mas.OBJ_CD) 
union all

    SELECT ddo_cd as ddocode,major as majorheadcode,sub_major submajorheadcode,minor minorheadcode,sub_head subheadcode,detailed_head as detailedhead,oc as soecode,soc subsoe,
	cast(NULL as varchar(50)) as upto_pretitle, cast(NULL as varchar(50)) as during_monthtitle,
	SUM(CASE WHEN passingDate >= '04/01/2018'  and passingDate < '05/01/2018' and  status='P' THEN Amount ELSE 0 END)  AS upto_preamt,
    SUM(CASE WHEN passingDate >= '05/01/2018' and passingDate < '06/01/2018' and status='P' THEN Amount ELSE 0 END) AS during_monthamt
	
	
	 FROM vExpendivision WHERE 
	status='P'  group by 
	major,sub_major,minor,sub_head,detailed_head,oc,soc,ddo_cd

这篇关于转换varchar值'exp时转换失败。 upto apr'到数据类型int。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！