我正在尝试向现有的sql查询添加一个条件,但我不是sql方面的Maven&这里需要一些帮助。
我有下面的sql查询&我当前有parnt\u sls\u order\u line\u key,我正在为两个业务单元id('cspbu','tmgbu')提取它。在现有查询中,我尝试添加一个条件,如:
如果在cspbu和tmgbu中都存在parnt\u sls\u order\u line\u key,则输出或忽略。
SELECT
BV_PRODUCTS.PRODUCT_ID,
BV_FISCAL_DAY_TO_YEAR.FISCAL_YEAR_NUMBER_INT,
BV_FISCAL_DAY_TO_YEAR.FISCAL_QUARTER_ID,
SUM(CASE WHEN DT_MT_RSTD_BKGS_MEASURE_IE_BE.SERVICE_FLG = 'N' THEN DT_MT_RSTD_BKGS_MEASURE_IE_BE.EXTENDED_QUANTITY*COALESCE(BV_BE_HIER_PRDT_FAM_ALLOC_INT.PRDT_FAMILY_ALLOCATION_PCT,1)*COALESCE(BV_BE_HIER_PRDT_FMLY_ALLOC_EXT.PRDT_FAMILY_ALLOCATION_PCT,1) ELSE 0 END) Quanity
,
sum(CASE WHEN DT_MT_RSTD_BKGS_MEASURE_IE_BE.SERVICE_FLG = 'N' THEN DT_MT_RSTD_BKGS_MEASURE_IE_BE.COMP_US_NET_PRICE_AMOUNT*COALESCE(BV_BE_HIER_PRDT_FAM_ALLOC_INT.PRDT_FAMILY_ALLOCATION_PCT,1)*COALESCE(BV_BE_HIER_PRDT_FMLY_ALLOC_EXT.PRDT_FAMILY_ALLOCATION_PCT,1) ELSE 0 END) Net_Price,
BV_SALES_ORDER_LINE.PARNT_SLS_ORDER_LINE_KEY,
BV_PRODUCTS.BUSINESS_UNIT_ID
FROM
FINANCEBOBVDB.BV_PRODUCTS,
FINANCEBOBVDB.BV_FISCAL_DAY_TO_YEAR,
FINANCEBOBVDB.BV_BE_HIER_PRDT_FAMILY_ALLOC BV_BE_HIER_PRDT_FAM_ALLOC_INT,
FINANCEBOBVDB.BV_BE_HIER_PRDT_FMLY_ALLOC_EXT,
(
SELECT BV_MT_RSTD_BKGS_MEASURE.* , BV_FISCAL_DAY_TO_YEAR.FISCAL_YEAR_QUARTER_NUMBER_INT ,
BV_FISCAL_DAY_TO_YEAR.CALENDAR_DATE
FROM FINANCEBOBVDB.BV_MT_RSTD_BKGS_MEASURE , FINANCEBOBVDB.BV_FISCAL_DAY_TO_YEAR
WHERE BV_MT_RSTD_BKGS_MEASURE.BOOKINGS_PROCESS_DATE = BV_FISCAL_DAY_TO_YEAR.CALENDAR_DATE
AND BV_FISCAL_DAY_TO_YEAR.FISCAL_YEAR_MONTH_INT >= 201001
) DT_MT_RSTD_BKGS_MEASURE_IE_BE,
BV_SALES_ORDER_LINE
WHERE
( BV_BE_HIER_PRDT_FAM_ALLOC_INT.ITEM_KEY=DT_MT_RSTD_BKGS_MEASURE_IE_BE.PRODUCT_KEY AND BV_BE_HIER_PRDT_FAM_ALLOC_INT.FISCAL_YEAR_QUARTER_NUMBER_INT=DT_MT_RSTD_BKGS_MEASURE_IE_BE.FISCAL_YEAR_QUARTER_NUMBER_INT )
AND ( BV_BE_HIER_PRDT_FMLY_ALLOC_EXT.ITEM_KEY=DT_MT_RSTD_BKGS_MEASURE_IE_BE.PRODUCT_KEY AND BV_BE_HIER_PRDT_FMLY_ALLOC_EXT.FISCAL_YEAR_QUARTER_NUMBER_INT=DT_MT_RSTD_BKGS_MEASURE_IE_BE.FISCAL_YEAR_QUARTER_NUMBER_INT )
AND ( DT_MT_RSTD_BKGS_MEASURE_IE_BE.BOOKINGS_PROCESS_DATE=BV_FISCAL_DAY_TO_YEAR.CALENDAR_DATE )
AND ( BV_PRODUCTS.ITEM_KEY=DT_MT_RSTD_BKGS_MEASURE_IE_BE.PRODUCT_KEY )
AND ( BV_SALES_ORDER_LINE.SALES_ORDER_LINE_KEY=DT_MT_RSTD_BKGS_MEASURE_IE_BE.DV_SALES_ORDER_LINE_KEY )
AND ( DT_MT_RSTD_BKGS_MEASURE_IE_BE.PRODUCT_KEY IN ( SELECT ITEM_KEY FROM FINANCEBOBVDB.BV_PRODUCTS H JOIN FINANCEBOBVDB.BV_IAM_TECHNOLOGY_GROUP_LINK S ON H.TECHNOLOGY_GROUP_ID = S.TECHNOLOGY_GROUP_ID WHERE cec_id='meramesh' AND IAM_LEVEL_NUM=1 ) )
AND ( DT_MT_RSTD_BKGS_MEASURE_IE_BE.DV_ATTRIBUTION_CD IN ('ATTRIBUTED','STANDALONE') )
AND
(
DT_MT_RSTD_BKGS_MEASURE_IE_BE.REVENUE_RECOGNITION_FLG IN ( 'Y' )
AND
BV_FISCAL_DAY_TO_YEAR.FISCAL_YEAR_NUMBER_INT IN ( 2018, 2019, 2020 )
AND
BV_PRODUCTS.BUSINESS_UNIT_ID IN ( 'cspbu', 'tmgbu' )
)
GROUP BY
1,
2,
3,
6,
7
1条答案
按热度按时间b4wnujal1#
你是如何将你的行分组的——按
product_id
? 如果只想返回给定product_id
有PARNT_SLS_ORDER_LINE_KEY
两个值都是TMGBU
以及CSPBU
,然后您可以将其添加到查询的末尾:试试看,让我知道。