Solved

need to add a case statement

Posted on 2014-01-31
13
388 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
  • 7
  • 6
13 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Comment Utility
I've requested that this question be deleted for the following reason:

I want to make this as private
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 

Author Comment

by:pardeshirahul
Comment Utility
i am sorry for that i didn't knew that
0
 
LVL 76

Expert Comment

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

Author Comment

by:pardeshirahul
Comment Utility
Yes
and i accepted your solution in the second one
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
thats fine with me
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
I don't have any clue I accepted the answer and requested to delete the other
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
so what should I do now
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now