How to create a "new vale" into a existing field

Hi SONU,

Query:

SELECT Query_Base.Cuentas_Maestro_Numero_cuenta

,Query_Base.Cuentas_Maestro_Profit_Center

,Query_Base.Trading_Partner

,Query_Base.Partner_Profit_Ctr

,Query_Base.Cuentas_MaestroTransType

,Query_Base.profit_center_local_currency

,Query_Base.Cuentas_Maestro_Curr_key

,Query_Base.Functional_Area

,Query_Base.Cuentas_Maestro_Controlling_Area

,Query_Base.Fiscal_Year

,Query_Base.Debit_Credit_Ind

,Query_Base.Origin_Object_num

,Query_Base.Elimination_PrCtr

,Query_Base.[Transaction]

,Query_Base.Receiver_object_type

,Query_Base.Partner_Func_Area

,Query_Base.Plant

,Query_Base.Period

,Query_Base.Trans_Type_Desc

,Query_Base.Descripcion_cuenta

,Query_Base.Controlling_Area_Controlling_Area_Desc

,Query_Base.Functional_area_Desc

,Query_Base.Tipo_de_costo

,Query_Base.Origin_Object_description

,Query_Base.Periodo

,Query_Base.Average

,Query_Base.SPOT

,Query_Base.Calculo_Tasa

,Query_Base.Origin_Object_description_Receiver

,Query_Base.[Short _Text_Elimination]

,Query_Base.[Cross _Div _Biz_Elimination]

,Query_Base.[Business _Area_Elimination]

,Query_Base.[Business _Line_Elimination]

,Query_Base.[Spiridon _2019_Elimination]

,Query_Base.[Business _Subsegment_Elimination]

,Query_Base.Funcionalidad_Elimination

,Query_Base.[Nombre_ Profit _Center_Elimination]

,Query_Base.[Short _Text_Origen]

,Query_Base.[Cross _Div _Biz_Origen]

,Query_Base.[Business _Area_Origen]

,Query_Base.[Business _Line_Origen]

,Query_Base.[Spiridon _2019_Origen]

,Query_Base.[Business _Subsegment_Origen]

,Query_Base.[Nombre_ Profit _Center_Origen]

,Query_Base.Funcionalidad_Origen

,Query_Base.[Short _Text_Partner]

,Query_Base.[Cross _Div _Biz_Partner]

,Query_Base.[Business _Area_Partner]

,Query_Base.[Business _Line_Partner]

,Query_Base.[Spiridon _2019_Partner]

,Query_Base.[Business _Subsegment_Partner]

,Query_Base.[Nombre_ Profit _Center_Partner]

,Query_Base.Funcionalidad_Partner

,Query_Base.Functional_area_Desc_Partner

,Query_Base.Tipo_de_costo_Partner

CASE

WHEN Q.COA_Level_2 = 'Financial income, net Split'

AND Query_Base.Trading_Partner <> '0'

THEN 'Financial income, net Below Profit'

WHEN Q.COA_Level_2 = 'Financial income, net Split'

AND Query_Base.Trading_Partner = '0'

THEN 'Financial income, net'

ELSE Q.COA_Level_2

END AS COA2

,CASE

WHEN Query_Base.Cuentas_Maestro_Numero_cuenta >= '50000000'

AND Query_Base.Cuentas_Maestro_Numero_cuenta <= '59999999'

THEN 'Own Business (Rev outside SHS)'

ELSE

CASE

WHEN Query_Base.Tipo_de_costo = 'NULL'

THEN

CASE

WHEN Q.COA_Level_2 = 'Financial income, net Split'

AND Query_Base.Trading_Partner <> '0'

THEN 'Financial income, net Below Profit'

WHEN Q.COA_Level_2 = 'Financial income, net Split'

AND Query_Base.Trading_Partner = '0'

THEN 'Financial income, net'

ELSE Q.COA_Level_2

END

END AS PNL

,Q.COA_Level_3 AS COA3

FROM [1RQ_BPCSQL1].[dbo].[Query_Base]

LEFT OUTER JOIN [1RQ_BPCSQL1].[dbo].[Account_Master_Group] Q ON [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta >= Q.inicio

AND [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta <= Q.fin

WHERE isnull(TRY_CAST([1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta AS INT), 0) BETWEEN 50000000

AND 89999999

AND [1RQ_BPCSQL1].dbo.Query_Base.Fiscal_Year >= 2010

AND [1RQ_BPCSQL1].dbo.Query_Base.period BETWEEN 0

AND 12

UNION

SELECT Query_Base.Cuentas_Maestro_Numero_cuenta

,Query_Base.Cuentas_Maestro_Profit_Center

,Query_Base.Trading_Partner

,Query_Base.Partner_Profit_Ctr

,Query_Base.Cuentas_MaestroTransType

,Query_Base.profit_center_local_currency

,Query_Base.Cuentas_Maestro_Curr_key

,Query_Base.Functional_Area

,Query_Base.Cuentas_Maestro_Controlling_Area

,Query_Base.Fiscal_Year

,Query_Base.Debit_Credit_Ind

,Query_Base.Origin_Object_num

,Query_Base.Elimination_PrCtr

,Query_Base.[Transaction]

,Query_Base.Receiver_object_type

,Query_Base.Partner_Func_Area

,Query_Base.Plant

,Query_Base.Period

,Query_Base.Trans_Type_Desc

,Query_Base.Descripcion_cuenta

,Query_Base.Controlling_Area_Controlling_Area_Desc

,Query_Base.Functional_area_Desc

,Query_Base.Tipo_de_costo

,Query_Base.Origin_Object_description

,Query_Base.Periodo

,Query_Base.Average

,Query_Base.SPOT

,Query_Base.Calculo_Tasa

,Query_Base.Origin_Object_description_Receiver

,Query_Base.[Short _Text_Elimination]

,Query_Base.[Cross _Div _Biz_Elimination]

,Query_Base.[Business _Area_Elimination]

,Query_Base.[Business _Line_Elimination]

,Query_Base.[Spiridon _2019_Elimination]

,Query_Base.[Business _Subsegment_Elimination]

,Query_Base.Funcionalidad_Elimination

,Query_Base.[Nombre_ Profit _Center_Elimination]

,Query_Base.[Short _Text_Origen]

,Query_Base.[Cross _Div _Biz_Origen]

,Query_Base.[Business _Area_Origen]

,Query_Base.[Business _Line_Origen]

,Query_Base.[Spiridon _2019_Origen]

,Query_Base.[Business _Subsegment_Origen]

,Query_Base.[Nombre_ Profit _Center_Origen]

,Query_Base.Funcionalidad_Origen

,Query_Base.[Short _Text_Partner]

,Query_Base.[Cross _Div _Biz_Partner]

,Query_Base.[Business _Area_Partner]

,Query_Base.[Business _Line_Partner]

,Query_Base.[Spiridon _2019_Partner]

,Query_Base.[Business _Subsegment_Partner]

,Query_Base.[Nombre_ Profit _Center_Partner]

,Query_Base.Funcionalidad_Partner

,Query_Base.Functional_area_Desc_Partner

,Query_Base.Tipo_de_costo_Partner

,COA_Level_2 AS COA2

,COA_Level_3 AS COA3

,NULL AS PNL

FROM [1RQ_BPCSQL1].[dbo].[Query_Base]

LEFT OUTER JOIN [1RQ_BPCSQL1].[dbo].[Account_Master_Group2] ON [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta >= [1RQ_BPCSQL1].dbo.Account_Master_Group2.inicio

AND [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta <= [1RQ_BPCSQL1].dbo.Account_Master_Group2.fin

WHERE [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta IN (

'R1110110'

,'R1111300'

,'R3310000'

,'R9999900'

)

AND [1RQ_BPCSQL1].dbo.Query_Base.Fiscal_Year >= 2010

AND [1RQ_BPCSQL1].dbo.Query_Base.period BETWEEN 0 AND 12

OR [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta LIKE ('S6%')

AND [1RQ_BPCSQL1].dbo.Query_Base.Fiscal_Year >= 2010

AND [1RQ_BPCSQL1].dbo.Query_Base.period BETWEEN 0 AND 12

TABEL STRUCTURE:

KEEP ATENT,

Thnaks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramon Quintana7y ago
Hi SONU,

Query:

SELECT Query_Base.Cuentas_Maestro_Numero_cuenta
,Query_Base.Cuentas_Maestro_Profit_Center
,Query_Base.Trading_Partner
,Query_Base.Partner_Profit_Ctr
,Query_Base.Cuentas_MaestroTransType
,Query_Base.profit_center_local_currency
,Query_Base.Cuentas_Maestro_Curr_key
,Query_Base.Functional_Area
,Query_Base.Cuentas_Maestro_Controlling_Area
,Query_Base.Fiscal_Year
,Query_Base.Debit_Credit_Ind
,Query_Base.Origin_Object_num
,Query_Base.Elimination_PrCtr
,Query_Base.[Transaction]
,Query_Base.Receiver_object_type
,Query_Base.Partner_Func_Area
,Query_Base.Plant
,Query_Base.Period
,Query_Base.Trans_Type_Desc
,Query_Base.Descripcion_cuenta
,Query_Base.Controlling_Area_Controlling_Area_Desc
,Query_Base.Functional_area_Desc
,Query_Base.Tipo_de_costo
,Query_Base.Origin_Object_description
,Query_Base.Periodo
,Query_Base.Average
,Query_Base.SPOT
,Query_Base.Calculo_Tasa
,Query_Base.Origin_Object_description_Receiver
,Query_Base.[Short _Text_Elimination]
,Query_Base.[Cross _Div _Biz_Elimination]
,Query_Base.[Business _Area_Elimination]
,Query_Base.[Business _Line_Elimination]
,Query_Base.[Spiridon _2019_Elimination]
,Query_Base.[Business _Subsegment_Elimination]
,Query_Base.Funcionalidad_Elimination
,Query_Base.[Nombre_ Profit _Center_Elimination]
,Query_Base.[Short _Text_Origen]
,Query_Base.[Cross _Div _Biz_Origen]
,Query_Base.[Business _Area_Origen]
,Query_Base.[Business _Line_Origen]
,Query_Base.[Spiridon _2019_Origen]
,Query_Base.[Business _Subsegment_Origen]
,Query_Base.[Nombre_ Profit _Center_Origen]
,Query_Base.Funcionalidad_Origen
,Query_Base.[Short _Text_Partner]
,Query_Base.[Cross _Div _Biz_Partner]
,Query_Base.[Business _Area_Partner]
,Query_Base.[Business _Line_Partner]
,Query_Base.[Spiridon _2019_Partner]
,Query_Base.[Business _Subsegment_Partner]
,Query_Base.[Nombre_ Profit _Center_Partner]
,Query_Base.Funcionalidad_Partner
,Query_Base.Functional_area_Desc_Partner
,Query_Base.Tipo_de_costo_Partner
,
CASE
WHEN Q.COA_Level_2 = 'Financial income, net Split'
AND Query_Base.Trading_Partner <> '0'
THEN 'Financial income, net Below Profit'
WHEN Q.COA_Level_2 = 'Financial income, net Split'
AND Query_Base.Trading_Partner = '0'
THEN 'Financial income, net'
ELSE Q.COA_Level_2
END AS COA2
,CASE
WHEN Query_Base.Cuentas_Maestro_Numero_cuenta >= '50000000'
AND Query_Base.Cuentas_Maestro_Numero_cuenta <= '59999999'
THEN 'Own Business (Rev outside SHS)'
ELSE
CASE
WHEN Query_Base.Tipo_de_costo = 'NULL'
THEN
CASE
WHEN Q.COA_Level_2 = 'Financial income, net Split'
AND Query_Base.Trading_Partner <> '0'
THEN 'Financial income, net Below Profit'
WHEN Q.COA_Level_2 = 'Financial income, net Split'
AND Query_Base.Trading_Partner = '0'
THEN 'Financial income, net'
ELSE Q.COA_Level_2
END
END
END AS PNL
,Q.COA_Level_3 AS COA3
FROM [1RQ_BPCSQL1].[dbo].[Query_Base]
LEFT OUTER JOIN [1RQ_BPCSQL1].[dbo].[Account_Master_Group] Q ON [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta >= Q.inicio
AND [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta <= Q.fin
WHERE isnull(TRY_CAST([1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta AS INT), 0) BETWEEN 50000000
AND 89999999
AND [1RQ_BPCSQL1].dbo.Query_Base.Fiscal_Year >= 2010
AND [1RQ_BPCSQL1].dbo.Query_Base.period BETWEEN 0
AND 12
UNION
SELECT Query_Base.Cuentas_Maestro_Numero_cuenta
,Query_Base.Cuentas_Maestro_Profit_Center
,Query_Base.Trading_Partner
,Query_Base.Partner_Profit_Ctr
,Query_Base.Cuentas_MaestroTransType
,Query_Base.profit_center_local_currency
,Query_Base.Cuentas_Maestro_Curr_key
,Query_Base.Functional_Area
,Query_Base.Cuentas_Maestro_Controlling_Area
,Query_Base.Fiscal_Year
,Query_Base.Debit_Credit_Ind
,Query_Base.Origin_Object_num
,Query_Base.Elimination_PrCtr
,Query_Base.[Transaction]
,Query_Base.Receiver_object_type
,Query_Base.Partner_Func_Area
,Query_Base.Plant
,Query_Base.Period
,Query_Base.Trans_Type_Desc
,Query_Base.Descripcion_cuenta
,Query_Base.Controlling_Area_Controlling_Area_Desc
,Query_Base.Functional_area_Desc
,Query_Base.Tipo_de_costo
,Query_Base.Origin_Object_description
,Query_Base.Periodo
,Query_Base.Average
,Query_Base.SPOT
,Query_Base.Calculo_Tasa
,Query_Base.Origin_Object_description_Receiver
,Query_Base.[Short _Text_Elimination]
,Query_Base.[Cross _Div _Biz_Elimination]
,Query_Base.[Business _Area_Elimination]
,Query_Base.[Business _Line_Elimination]
,Query_Base.[Spiridon _2019_Elimination]
,Query_Base.[Business _Subsegment_Elimination]
,Query_Base.Funcionalidad_Elimination
,Query_Base.[Nombre_ Profit _Center_Elimination]
,Query_Base.[Short _Text_Origen]
,Query_Base.[Cross _Div _Biz_Origen]
,Query_Base.[Business _Area_Origen]
,Query_Base.[Business _Line_Origen]
,Query_Base.[Spiridon _2019_Origen]
,Query_Base.[Business _Subsegment_Origen]
,Query_Base.[Nombre_ Profit _Center_Origen]
,Query_Base.Funcionalidad_Origen
,Query_Base.[Short _Text_Partner]
,Query_Base.[Cross _Div _Biz_Partner]
,Query_Base.[Business _Area_Partner]
,Query_Base.[Business _Line_Partner]
,Query_Base.[Spiridon _2019_Partner]
,Query_Base.[Business _Subsegment_Partner]
,Query_Base.[Nombre_ Profit _Center_Partner]
,Query_Base.Funcionalidad_Partner
,Query_Base.Functional_area_Desc_Partner
,Query_Base.Tipo_de_costo_Partner
,COA_Level_2 AS COA2
,COA_Level_3 AS COA3
,NULL AS PNL
FROM [1RQ_BPCSQL1].[dbo].[Query_Base]
LEFT OUTER JOIN [1RQ_BPCSQL1].[dbo].[Account_Master_Group2] ON [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta >= [1RQ_BPCSQL1].dbo.Account_Master_Group2.inicio
AND [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta <= [1RQ_BPCSQL1].dbo.Account_Master_Group2.fin
WHERE [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta IN (
'R1110110'
,'R1111300'
,'R3310000'
,'R9999900'
)
AND [1RQ_BPCSQL1].dbo.Query_Base.Fiscal_Year >= 2010
AND [1RQ_BPCSQL1].dbo.Query_Base.period BETWEEN 0 AND 12
OR [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta LIKE ('S6%')
AND [1RQ_BPCSQL1].dbo.Query_Base.Fiscal_Year >= 2010
AND [1RQ_BPCSQL1].dbo.Query_Base.period BETWEEN 0 AND 12

TABEL STRUCTURE:

KEEP ATENT,

Thnaks

0
Sonu Gupta7y ago
Can you, provide me table structure as well Query, so i modified according that.
0