꿈꾸는 개발자, DBA 커뮤니티 구루비

변수에 값을 할당하는 select 문에 데이터검색작업을 같이할수있나요?

by 지메이비 MSSQL [2022.08.04 17:32:52]

계산식에 행을 구분하고싶어서 ROW_NUMBER() 을 쓰고싶은데 변수값할당하는 SELECT문에 같이 못써서

같이쓸수잇는 방법이있나요?

by 마농 [2022.08.04 17:39:50]

질문을 이해하는게 어려워요.
원본 테이블 대비 원하는 결과표로 질문 가능한가요?

by 지메이비 [2022.08.04 17:43:11]

select @C_lossQty=(sum(upQty)-sum(lossQty)) from stockDay_LossUp with(NOLOCK)
	where dealerCode=@A_DealerCode
	and materialCode=@A_MaterialCode
	and stockDay between @C_StartDay and @A_CurrentDay

코드는 이렇구요..

이 계산식이 조금문제가있어서

행을 구분하고싶어서 row_number을 쓰고싶은데

변수에 값을 할당하는 SELECT 문은 데이터 검색 작업과 함께 사용할 수 없습니다.
라는 에러가떠서 다른방법이 있나 알아보고있습니다

by 마농 [2022.08.04 17:56:08]

방법은 있겠지만 질문이 어렵습니다. (이해 불가)
"행을 구분하고싶어서 row_number을 쓰고싶은데" 라는 질문은 이해하기 어렵습니다.
'행을 구분하고싶어서" 는 "어떠어떠한 조건의 자료를 뽑고싶다" 형태로 구체적이어 합니다.
'row_number을 쓰고싶은데" 처럼 특정 방법을 고정할 필요는 없을 것 같습니다.
목적만 명확하게 설명하시면 됩니다. 도구를 한정지을 필요는 없습니다.
원본 대비 결과표로 질문 가능한가요?

현재 쿼리의 문제점이 무엇인지?
어떤 조건을 추가하고 싶은지?
를 구체적으로 설명해 주세요.

by 지메이비 [2022.08.05 09:27:59]

이게 표인데요 sort컬럼에 출고나 조정은 계산할 때 이상이 없는데 입고만 들어가면 계산이 이상하게 틀어집니다..

그래서 입고만 뺏더니 계산은 잘되어서 입고가 문제인가 파악중입니다.

그래서 전에 말씀해주셨던것처럼 행을 구별하기 위해 이 결과표에 row_number로 행을 구별해봤는데 stockQty가 스칼라함수로 나타내서 그런지 효과가 없더라구요.

스칼라함수 안에서 구별을 해야할거같은데 저런에러가 떠서 못하고있습니다.

ALTER          FUNCTION [dbo].[UF_STOCKQTY](
	@A_DealerCode char(4),
	@A_CurrentDay char(8) ,
	@A_MaterialCode varchar(8)
)
RETURNS numeric(13,2)
AS
BEGIN
	declare @N_preStockQty numeric(13,2)

	--이전달을 구한다.
	declare @C_PreMonth varchar(6)
	set @C_PreMonth=''
	
	select @C_PreMonth=convert(varchar(6),dateadd(MONTH,-1,@A_CurrentDay),112)

	--이전달의 기말 재고를 구한다.
	declare @C_lastQty numeric(13,2)
	set @C_lastQty=0

	select @C_lastQty=isNull(stockQty,0)+isNull(upQty,0)-isNull(lossQty,0)
	from stockMonth with(NOLOCK)
	where dealerCode=@A_DealerCode
	and stockMonth=@C_PreMonth
	and materialCode=@A_MaterialCode

	--당월의 시작일을 구한다.
	declare @C_StartDay varchar(8)
	set @C_StartDay=''
	
	select @C_StartDay=substring(@A_CurrentDay,1,6)+'01'

	--출고수량
	declare @C_outQty numeric(13,2)
	set @C_outQty=0

	select @C_outQty=sum(materialqty-rtnQty) from orders with(NOLOCK)
	where dealerCode=@A_DealerCode
	and isWareHouse='W'
	and storeCode<>'9999'
	and materialCode=@A_MaterialCode
	and stockinDay between @C_StartDay and @A_CurrentDay
	group by materialCode

	--입고수량
	declare @C_inQty numeric(13,2)
	set @C_inQty=0

	select @C_inQty=sum(materialqty-rtnQty) from orders with(NOLOCK)
	where dealerCode=@A_DealerCode
	and materialCode=@A_MaterialCode
	and stockinDay between @C_StartDay and @A_CurrentDay
	and isWareHouse='W'
	and storeCode='9999'
	group by materialCode
/******대신푸드용 2010.10.19 **************/
	declare @D_inQty numeric(13,2)
	
	select @D_inQty=isNull(sum(materialqty-rtnQty),0) from orders with(NOLOCK)
	where dealerCode=@A_DealerCode
	and materialCode=@A_MaterialCode
	and stockinDay between @C_StartDay and @A_CurrentDay
	and isWareHouse='D'
	and refStoreCode is not null
	and storeCode='9999'
	group by materialCode


	set @C_inQty=@C_inQty+@D_inQty

/***************************************/

	--로스수량
	declare @C_lossQty numeric(13,2)
	set @C_lossQty=0

	select @C_lossQty=(sum(upQty)-sum(lossQty)) from stockDay_LossUp with(NOLOCK)
	where dealerCode=@A_DealerCode
	and materialCode=@A_MaterialCode
	and stockDay between @C_StartDay and @A_CurrentDay

	select @N_preStockQty=isNull(@C_lastQty,0)+isNull(@C_inQty,0)-isNull(@C_outQty,0)+isNull(@C_lossQty,0)
	
	
	-- @C_lastQty : 전월 재고  5
	-- @C_inQty   : 해당 월 초부터 입고일자까지 입고 수량  0
	-- @C_outQty  : 출고수량(창고출고로 발주한 수량)  3
	-- @C_lossQty : 로스수량(upQty - lossQty)  -3 

	Return isNull(@N_preStockQty,0)
END

함수코드는 이거입니다.

by 마농 [2022.08.05 10:40:18]

일자가 중복되는 부분 때문이 아닐까? 추측되는데요. (제 추측이 맞나요?)
일자만 조건으로 주어져서는 중복행을 걸러낼 수 없습니다.
원본테이블에서 같은 일자에 중복입력된 행들의 입력 순서를 결정지을 다른 항목이 필요합니다.
꼭 함수를 사용해야 하나요?
그냥 테이블에서 직접 분석함수를 이용해 조회하면 이런 일이 없을 텐데요.
이 함수를 호출하는 쿼리를 볼 수 있나요?

by 지메이비 [2022.08.05 10:57:38]

select  
			dealerCode,
			'전월' as gubun,
			'' as clientName,
			stockMonth as stockinday,
			materialCode,
			dbo.uf_materialName(dealerCode,materialCode) as materialName,
			stockQty+upQty-lossQty  as upQty,
			stockAmt  as upAmt,
			'0' as lossQty,
			'0' as lossAmt,
			stockQty+upQty-lossQty as stockQty,
			'1-전월' as sort,
			'' as storeType  
		from stockMonth
		where dealerCode = @A_dealerCode
		and stockMonth = substring(convert(char(30),DATEADD(MONTH,-1,@A_StockDay),112),1,6)
		and materialCode  = @A_materialCode
		union all
		select 
			dealerCode,
			'입고' as gubun,
			dbo.uf_sellerName(dealerCode,sellerCode) as sellerName ,
			stockinday,
			materialCode,
			dbo.uf_materialName(dealerCode,materialCode),
			isnull(materialQty-rtnQty,'0') as upQty,
			sellingAmt as upAmt,
			'0' as lossQty,
			'0' as lossAmt,
			dbo.UF_STOCKQTY(dealerCode,stockinday,materialCode) as stockQty,
			'2-입고' as sort,
			storeType 
		from orders
		where dealerCode = @A_dealerCode
		and stockinday between subString(@A_stockDay,1,6)+'01' and @A_stockDay
		and materialCode  = @A_materialCode
		and isWareHouse = 'W'
		and StoreCode = '9999'
		union all
		select 
			dealerCode,
			'출고',
			dbo.uf_storeName(dealerCode,storeCode) as storeName,
			stockinday,
			materialCode,
			dbo.uf_materialName(dealerCode,materialCode),
			'0' as upQty,
			'0' as upAmt,
			isnull(sum(materialQty)-sum(rtnQty),'0') as lossQty,
			isnull(sum(buyingAmt),'0') as lossAmt,
			dbo.UF_STOCKQTY(dealerCode,stockinday,materialCode) as stockQty,
			'3-출고' as gubun,
			storeType
		from orders
		where dealerCode = @A_dealerCode
		and stockinday between subString(@A_stockDay,1,6)+'01' and @A_stockDay
		and materialCode  = @A_materialCode
		and isWareHouse = 'W'
		and StoreCode <>'9999'
		group by dealerCode,materialCode,stockinday,storeCode,storeType
		union all
		select 
			dealerCode,
			'조정',
			'' as clientName,
			stockday,
			materialCode,
			dbo.uf_materialName(dealerCode,materialCode),
			upQty as upQty,
			'0' as upAmt,
			lossQty,
			'0' as lossAmt,
			dbo.UF_STOCKQTY(dealerCode,stockday,materialCode) as stockQty,
			'4-조정', 
			'' as storeType
		from stockDay_LossUp
		where dealerCode = @A_dealerCode
		and stockday between subString(@A_stockDay,1,6)+'01' and @A_stockDay
		and materialCode  = @A_materialCode
		order by stockinday

이쿼리가 함수를 호출하는 쿼리입니다.

옛날 쿼리문을 조금수정하는거라 쉽지가않네요ㅠㅠ

by 마농 [2022.08.05 10:46:24]

보여주신 표가?
- 원본인가요? 결과인가요? 원본대비 결과표로 보여주실 수 없나요? 이미지가 아닌 텍스트로.
- 뭐가 문제인가요? 문제에 대한 설명이 필요합니다.

by 지메이비 [2022.08.05 11:00:17]

원본입니다.

dealerCode   gubun   clientName   stockinday   materialCode   materialName   upQty   upAmt   lossQty   lossAmt   stockQty   sort   storeType
1001 출고 매출처1 20200301   2202043    (테스트용)감자   0.00 0   120.00 218182   -120.00   3-출고
1001 출고 매출처1 20200302   2202043    (테스트용)감자   0.00 0   1.00 1818   -121.00   3-출고
1001 출고 매출처1 20200303   2202043    (테스트용)감자   0.00 0   1.00 1818   -122.00   3-출고
1001 출고 나실로암 20200319   2202043    (테스트용)감자   0.00 0   2.00 3636   -124.00   3-출고
1001 조정 20200321   2202043    (테스트용)감자   7.00 0   5.00 0   -122.00   4-조정
1001 조정 20200324   2202043    (테스트용)감자   0.00 0   4.00 0   -126.00   4-조정
1001 입고 대왕식품 20200324   2202043    (테스트용)감자   5.00 105750   0.00 0   -126.00   2-입고   NULL
1001 입고 대왕식품 20200325   2202043    (테스트용)감자   10.00 7273   0.00 0   -129.00   2-입고   NULL
1001 출고 다새솔fs 20200325   2202043    (테스트용)감자   0.00 0 3.00   2182   -129.00   3-출고
1001 조정 20200327   2202043    (테스트용)감자   10.00 0 0.00   0   -119.00   4-조정
1001 조정 20200328   2202043    (테스트용)감자   5.00 0 0.00   0   -114.00   4-조정
1001 조정 20200329   2202043    (테스트용)감자   5.00 0 7.00   0   -116.00   4-조정
1001 입고   대왕식품 20200329   2202043    (테스트용)감자   8.00 5818 0.00   0   -116.00   2-입고   NULL
1001 입고   대왕식품 20200330   2202043    (테스트용)감자   10.00 7273   0.00   0   -118.00   2-입고   NULL
1001 조정 20200330   2202043    (테스트용)감자   5.00 0 7.00   0   -118.00   4-조정

제가원하는결과표입니다.

dealerCode   gubun   clientName   stockinday   materialCode   materialName   upQty   upAmt   lossQty   lossAmt   stockQty   sort   storeType
1001 출고 매출처1 20200301   2202043    (테스트용)감자   0.00 0   120.00 218182   -120.00   3-출고
1001 출고 매출처1 20200302   2202043    (테스트용)감자   0.00 0   1.00 1818   -121.00   3-출고
1001 출고 매출처1 20200303   2202043    (테스트용)감자   0.00 0   1.00 1818   -122.00   3-출고
1001 출고 나실로암 20200319   2202043    (테스트용)감자   0.00 0   2.00 3636   -124.00   3-출고
1001 조정 20200321   2202043    (테스트용)감자   7.00 0   5.00 0   -122.00   4-조정
1001 조정 20200324   2202043    (테스트용)감자   0.00 0   4.00 0   -126.00   4-조정
1001 입고 대왕식품 20200324   2202043    (테스트용)감자   5.00 105750   0.00 0   -121.00   2-입고   NULL
1001 입고 대왕식품 20200325   2202043    (테스트용)감자   10.00 7273   0.00 0   -111.00   2-입고   NULL
1001 출고 다새솔fs 20200325   2202043    (테스트용)감자   0.00 0 3.00   2182   -114.00   3-출고
1001 조정 20200327   2202043    (테스트용)감자   10.00 0 0.00   0   -104.00   4-조정
1001 조정 20200328   2202043    (테스트용)감자   5.00 0 0.00   0   -99.00   4-조정
1001 조정 20200329   2202043    (테스트용)감자   5.00 0 7.00   0   -101.00   4-조정
1001 입고   대왕식품 20200329   2202043    (테스트용)감자   8.00 5818 0.00   0   -93.00   2-입고   NULL
1001 입고   대왕식품 20200330   2202043    (테스트용)감자   10.00 7273   0.00   0   -83.00   2-입고   NULL
1001 조정 20200330   2202043    (테스트용)감자   5.00 0 7.00   0   -85.00   4-조정

마농님이 알려주신대로 날짜때문에 중복되는거 같아서 함수에서 뭘 해보려고해도 방법이떠오르지 않습니다..

by 마농 [2022.08.05 13:13:17]

잘못 나온 결과를 원본이라고 하면 안되죠.
중복 레코드 간 순서를 결정 할 수 있는 날짜 외 추가 항목이 필요합니다.
보여주신 자료에는 그게 없구요.

by 마농 [2022.08.05 13:21:47]

WITH t_order
(dealerCode, gubun, stockinday, materialCode, upQty, lossQty, seq) AS
(
          SELECT 1001, '출고', '20200301', '2202043',  0.00, 120.00, 1
UNION ALL SELECT 1001, '출고', '20200302', '2202043',  0.00,   1.00, 1
UNION ALL SELECT 1001, '출고', '20200303', '2202043',  0.00,   1.00, 1
UNION ALL SELECT 1001, '출고', '20200319', '2202043',  0.00,   2.00, 1
UNION ALL SELECT 1001, '조정', '20200321', '2202043',  7.00,   5.00, 1
UNION ALL SELECT 1001, '조정', '20200324', '2202043',  0.00,   4.00, 1
UNION ALL SELECT 1001, '입고', '20200324', '2202043',  5.00,   0.00, 2
UNION ALL SELECT 1001, '입고', '20200325', '2202043', 10.00,   0.00, 1
UNION ALL SELECT 1001, '출고', '20200325', '2202043',  0.00,   3.00, 2
UNION ALL SELECT 1001, '조정', '20200327', '2202043', 10.00,   0.00, 1
UNION ALL SELECT 1001, '조정', '20200328', '2202043',  5.00,   0.00, 1
UNION ALL SELECT 1001, '조정', '20200329', '2202043',  5.00,   7.00, 1
UNION ALL SELECT 1001, '입고', '20200329', '2202043',  8.00,   0.00, 2
UNION ALL SELECT 1001, '입고', '20200330', '2202043', 10.00,   0.00, 1
UNION ALL SELECT 1001, '조정', '20200330', '2202043',  5.00,   7.00, 2
)
SELECT dealerCode, gubun, stockinday, materialCode, upQty, lossQty, seq
     , SUM(upQty - lossQty) OVER(PARTITION BY dealerCode, materialCode
                                     ORDER BY stockinday, seq) stockQty
  FROM t
 WHERE dealerCode   = 1001
   AND materialCode = '2202043'
;

by 지메이비 [2022.08.05 14:42:29]

답변 감사합니다..!!

근데 어떻게 적용해야할지 감이안잡혀요 ..

by 마농 [2022.08.08 09:48:25]

도움을 드리고자 하는데 있어서 정보가 부족하여 필요한 정보를 요청하면.
필요한 정보가 댓글로 달리길 원하는데 그렇질 않네요.
정보가 있어야 도움을 드리는데. 필요한 정보를 주질 않고 있네요.
원본 테이블들의 정보를 알아야 합니다.
다음은 이미 위에서 요청한 정보들입니다.
원본 테이블에서 정렬 순서를 결정지을 항목이나, PK 정보 등이 필요합니다.
함수를 호출하는 쿼리를 보고 싶습니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변