Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

need to add a case statement

Posted on 2014-01-31
13
Medium Priority
?
466 Views
Last Modified: 2014-01-31
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
Comment
Question by:pardeshirahul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39825105
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
 

Author Comment

by:pardeshirahul
ID: 39825142
I've requested that this question be deleted for the following reason:

I want to make this as private
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39825134
>>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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:pardeshirahul
ID: 39825139
i am sorry for that i didn't knew that
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39825151
So, are you now going to delete the other one?
0
 

Author Comment

by:pardeshirahul
ID: 39825183
Yes
and i accepted your solution in the second one
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39825188
>>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
 

Author Comment

by:pardeshirahul
ID: 39825217
thats fine with me
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39825628
It has been a few hours.

OK, the other one is still open.

What do you want to do here?
0
 

Author Comment

by:pardeshirahul
ID: 39825653
I don't have any clue I accepted the answer and requested to delete the other
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39825659
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
 

Author Comment

by:pardeshirahul
ID: 39825664
so what should I do now
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39825742
>>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

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question