Nishant.Chauhan80 Ответов: 1

Ошибка преобразования в тип varchar значение 'эксп. До apr' до типа данных 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. 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


Что я уже пробовал:

я хочу попробовать объединить данные из двух запросов через union all

1 Ответов

Рейтинг:
10

Nishant.Chauhan80

<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


Richard Deeming

Если вы хотите отредактировать свой вопрос, Нажмите зеленую ссылку "улучшить вопрос" и обновите свой вопрос.

НЕ опубликуйте свое обновление как "решение".

И НЕ отметьте свое обновление как "принятое решение".

Nishant.Chauhan80

извини, Ричард, я останусь, вспомни в следующий раз.