pardeshirahul
asked on
need to add a case statement
I have the following query
SELECT
legal_entity_name, legal_entity_identifier, operating_unit, functional_currency_code,
transaction_class, transaction_source
,transaction_type, trx_date, gl_date, tax_invoice_date, bill_to_customer_name
,bill_to_cust_number, bill_to_site_number, cust_bill_to_add1, cust_bill_to_add2, cust_bill_to_add3
,cust_bill_to_add4, cust_bill_to_city, cust_bill_to_zip, cust_bill_to_state, cust_bill_to_country,
bill_to_site_payment_term
,ship_to_customer_name, ship_to_cust_number, ship_to_site_number, cust_ship_to_add1,
cust_ship_to_add2
,cust_ship_to_add3, cust_ship_to_add4, cust_ship_to_city, cust_ship_to_zip, cust_ship_to_state
,cust_ship_to_country, DECODE(bill_to_cust_tax_re f,NULL,bil l_to_cust_ tax_ref,'' ''||
bill_to_cust_tax_ref) bill_to_cust_tax_ref, payment_terms, invoice_currency_code, conversion_type
,trx_number, complete_flag, line_number, description, quantity
,unit_selling_price, extended_amount, tax_classification_code, tax_rate, tax_extended_amount
,entered_gross_inv_amount, functional_inv_amount funcational_inv_amount, functional_tax_amount
funcational_tax_amount, functional_gross_inv_amoun t ,trx_created_by ,line_line_type, tax_account, revenue_account,
revenue_natural_account_de sc, receivable_account,tax_law _ref, tax_law_desc, tax_exemption_text, org_id
,customer_trx_id, customer_trx_line_id, tax_customer_trx_line_id, exchange_rate,
functional_currency_precis ion
,printing_original_date,pu rchase_ord er, purchase_order_date, purchase_order_revision,
sales_order_source
,sales_order, sales_order_date, sales_order_line, sales_order_revision,
fr_transaction_class,perio d_name, period_year, quarter_num,special_custom er_type, header_comments, header_context_value,
header_attribute1, header_attribute2,
header_attribute3, header_attribute4, header_attribute5, header_attribute6, header_attribute7,
header_attribute8, header_attribute9, header_attribute10, header_attribute11, header_attribute12,
header_attribute13, header_attribute14, header_attribute15,
line_context_value,line_at tribute1,l ine_attrib ute2,line_ attribute3 ,line_attr ibute4,lin e_attribut e5, line_attribute6,line_attri bute7,
line_attribute8, line_attribute9, line_attribute10, line_attribute11,
line_attribute12, line_attribute13, line_attribute14, line_attribute15,
original_transaction_numbe r, original_transaction_date, original_trx_tax_invoice_d ate, DECODE
(global_context_value,'JE. PL.ARXTWMA I.CLE_TRX_ INFO',TO_D ATE(global _attribute 14,'YYYY/M M/DD HH24:MI:SS'),NULL) sales_date, reference,
gta_vat_invoice_number, gta_vat_invoice_date, gta_vat_net_amount, gta_vat_tax_amount, gta_vat_total_amount,tax_r ate_code,
tax_rate_name,tax2_rate_co de, tax2_rate_name,tax2_rate,t ax2_extend ed_amount, tax3_rate_ code,
tax3_rate_name,tax3_rate,t ax3_extend ed_amount, functional _tax2_amou nt,functio nal_tax3_a mount,tax2 _account,
tax3_account,tax2_customer _trx_line_ id,tax3_cu stomer_trx _line_id,t ax_natural _account_d esc,tax2_n atural_acc ount_desc,
tax3_natural_account_desc, receivable _natural_a cct_desc,
GLOBAL_CONTEXT_VALUE,GLOBA L_ATTRIBUT E1,GLOBAL_ ATTRIBUTE2 ,GLOBAL_AT TRIBUTE3,G LOBAL_ATTR IBUTE4,GLO BAL_ATTRIB UTE5,GLOBA L_ATTRIBUT E6,GLOB
AL_ATTRIBUTE7,GLOBAL_ATTRI BUTE8,GLOB AL_ATTRIBU TE9,GLOBAL _ATTRIBUTE 10,GLOBAL_ ATTRIBUTE1 1,GLOBAL_A TTRIBUTE12 ,GLOBAL_AT TRIBUTE13, GLOBAL_
ATTRIBUTE14,GLOBAL_ATTRIBU TE15
FROM
XXX WHERE legal_entity_id = :legal_entity_id_param
AND operating_unit = NVL(:operating_unit_param, operating_ unit)
AND NVL(tax_classification_cod e,'##') = NVL(:tax_classification_co de_param,N VL(tax_cla ssificatio n_code,'## '))
AND invoice_currency_code = NVL(:entered_currency_para m,invoice_ currency_c ode)
AND (bill_to_customer_name||' - '||bill_to_cust_number) = NVL(:bill_to_customer_para m,
bill_to_customer_name||' - '||bill_to_cust_number)
AND complete_flag = 'Y'
i want to select GLOBAL_ATTRIBUTES only when the GLOBAL_CONTEXT_VALUE= 'JE.RU.ARXTWMAI.CLE_INV_IN FO'
i dont want to add the filter in the where clause as this query is used by many reports
SELECT
legal_entity_name, legal_entity_identifier, operating_unit, functional_currency_code,
transaction_class, transaction_source
,transaction_type, trx_date, gl_date, tax_invoice_date, bill_to_customer_name
,bill_to_cust_number, bill_to_site_number, cust_bill_to_add1, cust_bill_to_add2, cust_bill_to_add3
,cust_bill_to_add4, cust_bill_to_city, cust_bill_to_zip, cust_bill_to_state, cust_bill_to_country,
bill_to_site_payment_term
,ship_to_customer_name, ship_to_cust_number, ship_to_site_number, cust_ship_to_add1,
cust_ship_to_add2
,cust_ship_to_add3, cust_ship_to_add4, cust_ship_to_city, cust_ship_to_zip, cust_ship_to_state
,cust_ship_to_country, DECODE(bill_to_cust_tax_re
bill_to_cust_tax_ref) bill_to_cust_tax_ref, payment_terms, invoice_currency_code, conversion_type
,trx_number, complete_flag, line_number, description, quantity
,unit_selling_price, extended_amount, tax_classification_code, tax_rate, tax_extended_amount
,entered_gross_inv_amount,
funcational_tax_amount, functional_gross_inv_amoun
revenue_natural_account_de
,customer_trx_id, customer_trx_line_id, tax_customer_trx_line_id, exchange_rate,
functional_currency_precis
,printing_original_date,pu
sales_order_source
,sales_order, sales_order_date, sales_order_line, sales_order_revision,
fr_transaction_class,perio
header_attribute1, header_attribute2,
header_attribute3, header_attribute4, header_attribute5, header_attribute6, header_attribute7,
header_attribute8, header_attribute9, header_attribute10, header_attribute11, header_attribute12,
header_attribute13, header_attribute14, header_attribute15,
line_context_value,line_at
line_attribute8, line_attribute9, line_attribute10, line_attribute11,
line_attribute12, line_attribute13, line_attribute14, line_attribute15,
original_transaction_numbe
(global_context_value,'JE.
gta_vat_invoice_number, gta_vat_invoice_date, gta_vat_net_amount, gta_vat_tax_amount, gta_vat_total_amount,tax_r
tax_rate_name,tax2_rate_co
tax3_rate_name,tax3_rate,t
tax3_account,tax2_customer
tax3_natural_account_desc,
GLOBAL_CONTEXT_VALUE,GLOBA
AL_ATTRIBUTE7,GLOBAL_ATTRI
ATTRIBUTE14,GLOBAL_ATTRIBU
FROM
XXX WHERE legal_entity_id = :legal_entity_id_param
AND operating_unit = NVL(:operating_unit_param,
AND NVL(tax_classification_cod
AND invoice_currency_code = NVL(:entered_currency_para
AND (bill_to_customer_name||' - '||bill_to_cust_number) = NVL(:bill_to_customer_para
bill_to_customer_name||' - '||bill_to_cust_number)
AND complete_flag = 'Y'
i want to select GLOBAL_ATTRIBUTES only when the GLOBAL_CONTEXT_VALUE= 'JE.RU.ARXTWMAI.CLE_INV_IN
i dont want to add the filter in the where clause as this query is used by many reports
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>I want to make this as private
What do you think making it 'private' means?
All it does is not make it invisible to search engines. Anyone on the site can still see it.
I could have taken care of deindexing it for you. A new question wasn't necessary.
What do you think making it 'private' means?
All it does is not make it invisible to search engines. Anyone on the site can still see it.
I could have taken care of deindexing it for you. A new question wasn't necessary.
ASKER
i am sorry for that i didn't knew that
So, are you now going to delete the other one?
ASKER
Yes
and i accepted your solution in the second one
and i accepted your solution in the second one
>>and i accepted your solution in the second one
You accepted this one and the other one is still open.
If what I posted answered the question and now this one has a lot of extra posts, can you accept the other one and then I'll just delete this one.
You accepted this one and the other one is still open.
If what I posted answered the question and now this one has a lot of extra posts, can you accept the other one and then I'll just delete this one.
ASKER
thats fine with me
It has been a few hours.
OK, the other one is still open.
What do you want to do here?
OK, the other one is still open.
What do you want to do here?
ASKER
I don't have any clue I accepted the answer and requested to delete the other
You first requested this one to be deleted:
http:#a39825142
Then accepted this one:
http:#a39825105
The other one is still open:
https://www.experts-exchange.com/questions/28353614/How-to-write-A-case-statement.html
http:#a39825142
Then accepted this one:
http:#a39825105
The other one is still open:
https://www.experts-exchange.com/questions/28353614/How-to-write-A-case-statement.html
ASKER
so what should I do now
>>so what should I do now
Let it go... There have been other posts in the other question. This one has been de-indexed from search engines.
If you have your answer, just go ahead and close the other question as you see fit.
If you need additional information, post it over there.
Let it go... There have been other posts in the other question. This one has been de-indexed from search engines.
If you have your answer, just go ahead and close the other question as you see fit.
If you need additional information, post it over there.
ASKER
I want to make this as private