Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

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_ref,NULL,bill_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_amount ,trx_created_by ,line_line_type, tax_account, revenue_account,

revenue_natural_account_desc, 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_precision
                        ,printing_original_date,purchase_order, purchase_order_date, purchase_order_revision,

sales_order_source
                        ,sales_order, sales_order_date, sales_order_line, sales_order_revision,

fr_transaction_class,period_name, period_year, quarter_num,special_customer_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_attribute1,line_attribute2,line_attribute3,line_attribute4,line_attribute5, line_attribute6,line_attribute7,

line_attribute8, line_attribute9, line_attribute10, line_attribute11,
                         line_attribute12, line_attribute13, line_attribute14, line_attribute15,            
                         original_transaction_number, original_transaction_date, original_trx_tax_invoice_date, DECODE

(global_context_value,'JE.PL.ARXTWMAI.CLE_TRX_INFO',TO_DATE(global_attribute14,'YYYY/MM/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_rate_code,

tax_rate_name,tax2_rate_code, tax2_rate_name,tax2_rate,tax2_extended_amount,tax3_rate_code,

tax3_rate_name,tax3_rate,tax3_extended_amount,functional_tax2_amount,functional_tax3_amount,tax2_account,

tax3_account,tax2_customer_trx_line_id,tax3_customer_trx_line_id,tax_natural_account_desc,tax2_natural_account_desc,
                         tax3_natural_account_desc,receivable_natural_acct_desc,
GLOBAL_CONTEXT_VALUE,GLOBAL_ATTRIBUTE1,GLOBAL_ATTRIBUTE2,GLOBAL_ATTRIBUTE3,GLOBAL_ATTRIBUTE4,GLOBAL_ATTRIBUTE5,GLOBAL_ATTRIBUTE6,GLOB

AL_ATTRIBUTE7,GLOBAL_ATTRIBUTE8,GLOBAL_ATTRIBUTE9,GLOBAL_ATTRIBUTE10,GLOBAL_ATTRIBUTE11,GLOBAL_ATTRIBUTE12,GLOBAL_ATTRIBUTE13,GLOBAL_

ATTRIBUTE14,GLOBAL_ATTRIBUTE15
                  FROM
                  XXX                  WHERE legal_entity_id = :legal_entity_id_param
                  AND      operating_unit = NVL(:operating_unit_param,operating_unit)
                                    AND NVL(tax_classification_code,'##') = NVL(:tax_classification_code_param,NVL(tax_classification_code,'##'))
                  AND invoice_currency_code = NVL(:entered_currency_param,invoice_currency_code)
                  AND (bill_to_customer_name||' - '||bill_to_cust_number) = NVL(:bill_to_customer_param,

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_INFO'


i dont want to add the filter in the where  clause as this  query is used by many reports
0
pardeshirahul
Asked:
pardeshirahul
  • 7
  • 6
1 Solution
 
slightwv (䄆 Netminder) Commented:
Please provide a simplified test case with sample data and expected results.

Do you mean you don't want the column to appear in the result set at all or just to contain nulls?

If nulls:
case when GLOBAL_CONTEXT_VALUE= 'JE.RU.ARXTWMAI.CLE_INV_INFO' then GLOBAL_ATTRIBUTES end

If you want the column removed from the result set:  You need dynamic SQL.
0
 
pardeshirahulAuthor Commented:
I've requested that this question be deleted for the following reason:

I want to make this as private
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
pardeshirahulAuthor Commented:
i am sorry for that i didn't knew that
0
 
slightwv (䄆 Netminder) Commented:
So, are you now going to delete the other one?
0
 
pardeshirahulAuthor Commented:
Yes
and i accepted your solution in the second one
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
pardeshirahulAuthor Commented:
thats fine with me
0
 
slightwv (䄆 Netminder) Commented:
It has been a few hours.

OK, the other one is still open.

What do you want to do here?
0
 
pardeshirahulAuthor Commented:
I don't have any clue I accepted the answer and requested to delete the other
0
 
slightwv (䄆 Netminder) Commented:
You first requested this one to be deleted:
http:#a39825142

Then accepted this one:
http:#a39825105

The other one is still open:
http://www.experts-exchange.com/Database/Oracle/Q_28353614.html
0
 
pardeshirahulAuthor Commented:
so what should I do now
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now