Control sort in union all

If you have a union all select statement and you want to have the results from the first select statement first and second statement second, etc..... here what you can do. let's say we are getting the employee names from different tables select employee_name from company1 union all select employee_name from company2 union all select employee_name from company3 union all select employee_name from company4 union all select employee_name from company5 order by 1; In this select statement all employee will be mixed from all companies sorted by name, but what if you want to have the name of the employee from company1 first and then from company2 and then from company3 etc..... Here what you can do select 'A',employee_name from company1 union all select 'B',employee_name from company2 union all select 'C',employee_name from company3 union all select 'D', employee_name from company4 union all select 'E', employee_name from company5 order by 1,2; This way the employee will be sorted by name from company1 first and then second, etc.... If you have the select statement in SQL*Plus and you don't want to show this extra column here what you can do: column sort1 noprint --- this will hide column sort1 select 'A' sort1,employee_name from company1 union all select 'B' sort1,employee_name from company2 union all select 'C' sort1,employee_name from company3 union all select 'D' sort1, employee_name from company4 union all select 'E' sort1, employee_name from company5 order by 1,2;

"The WIP work order associated with this transaction is currently locked and being updated by another user"

One of the thing that causes this Lock is because "Inventory Manager" and "WIP Mass Load" are updating the same job. If you are submitting online IM (mtl_online_transaction_pub.process_online) to process material transactions inside your code and you got the lock message, you can solve it by checking the running concurrent request, if WIP Mass Load is one of them you keep looping till it's complete then you can run mtl_online_transaction_pub.process_online declare v_program varchar2(2000); -------------get all running concurrent requests cursor c4 is select substr(fcrv.program,1,40) program from apps.fnd_conc_req_summary_v fcrv where fcrv.phase_code = 'R' and fcrv.program like 'WIP Mass Load (WIP Mass Load)'; begin while 1 = 1 loop v_program := null; open c4; fetch c4 into v_program; close c4; if(v_program is null) then exit; end if; end loop; end; ------------------after that you can call mtl_online_transaction_pub.process_online

Ask your code to wait

If for some reason you want for your code to wait for certain period of time, you can use dbms_lock.sleep(100); , in order to test this you can use the code below, from the test table you can see how much the code waited between the two insert statements. create table test (date1 date); begin insert into test values (sysdate); dbms_lock.sleep(100); insert into test values (sysdate); commit; end; select * from test;

Sorting dates using only month and year

In PL/SQL in order to show only the month and year of a date, you need to convert the date to char, once you do that the order by get messed up because it won't recognize the field as a date anymore so SEP-2006 will come before OCT-2016 if you are using order by desc. To solve this issue you can do the following select DISTINCT to_char(x_date, 'MON-YYYY') DISPLAY, to_char(x_date, 'YYYY-MM') HIDDEN order by to_char(x_date, 'YYYY-MM') DESC;

Oracle Application - Alert

Every time you use the Alert manager to disable and enable an alert the database trigger associated with the alert got recreated and inside the database trigger there's a submit to a concurrent request to perform the actions associated with the alert.
You can check those concurrent request from the Alert Manager responsibility - Request - View.
If the alert action is not working check the parameters that passed to the concurrent request on the database trigger.

dividing by zero

If you are dividing to a column or sum in your select statement, and there's a chance that the column or the sum result might be a zero an error is going to occur on your select statement; to prevent the error and get a null result you can do the following

select sum(x)/sum(y) from dual; ----- result an error if sum(y) is zero

select sum(x)/decode (sum(y),0,null,sum(y)) from dual --- result is null if sum(y) is zero.

By using the decode, in case of zero the null is going to replace the zero or otherwise take the sum. dividing by null is null.

Graduation

I finally graduated from Marshall University www.marshall.edu college of information technology and engineering
I earned my masters degree in information systems after four years of part time study, full time employement and having two babies.

APEX Application Presentation

I presented my capstone project for defense. The project used Orace XE and APEX 3.2 to build a reference management system for students, faculty and researchers in a university environment. I'll post more about the application.

Making html button behave like hyperlink

I was trying to do so today for my eSurvey pages and I found it very simple

you just put the hyperlink before the button:



<p>
<a href="http://your link">
<input type="button" name="test">
</p>

Radio Group

• Radio groups are logical containers of radio buttons, so it doesn’t appear in the canvas only the radio buttons would appear in the canvas.
• Selection of one radio button deselects the previously selected button.
• To assign a NULL value, leave the radio button value property blank
• If the database column has a value that not one of the radio button value we should include this extra value in the Mapping OF Other values property even if it’s null otherwise when we execute query no records are returned.
• Can be database item or non-database item.

List Item

• Short list of values that the user can choose from.
• Once value can be selected.
• Can be a database or non-database item
• Each List Box must have an associated list of values (which can be hard-coded into the list box item, or dynamically attached from a Record Group).
• Properties:
• Element in list: the display value and database value pairs for the list.
• Mapping of Other Values Controls how records queried in the database will be handled if the value in the list column does not match the value of an item currently in the list.
• List item values: how each item in the list is represented in the database?
• Initial Value: must be one of list item values
• List Boxes come in several flavors
• Pop list the standard list box, which initially displays a single value that can be expanded to view the rest of the list
• T-List Can be sued when there are only a few items to display.
• Combo Box They are like the Pop list but they allow the user to type in a value that is not in the list and have that value saved to the database

Check Box

• Check boxes are useful for storing Yes/No, True/False, and On/Off-type values.
• It is not appropriate to use a check box to enable entry and update of more than two values in an item.
• Properties:
• Value when checked
• Value when unchecked
• Check box mapping of Other Values