Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

need to add a case statement

Posted on 2014-01-31
13
Medium Priority
?
495 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 78

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 78

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

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

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 78

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 78

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 78

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 78

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

916 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