Posts

Oracle Fusion : SQL Query to Retrieve Shopping Category Hierarcy

-
You can use below SQL query to list down all shopping cateogry hierarcy (both browsing and purchasing ones) and description for English and Turkish languages. Please feel free to contact me if you relaize any mistake. SELECT prct.CATEGORY_NAME PARENT_CATEGORY_NAME_TR , prce.CATEGORY_NAME PARENT_CATEGORY_NAME_US , pch.PURCHASING_CAT_FLAG , CASE WHEN pch.PURCHASING_CAT_FLAG = 'Y' THEN ( SELECT ect.CATEGORY_NAME FROM EGP_CATEGORIES_TL ect WHERE pch.child_category_id = ect.category_id AND ect.language = 'TR' ) ELSE ( SELECT prct.CATEGORY_NAME FROM POR_BROWSE
Post a Comment
Read more

How to transpose / convert columns and rows into single column?

-
Image
When you use Excel worksheet, sometimes, you will meet this problem: how could you convert or transpose a range of data into a single column? (See the following screenshots:) Now, I introduce three quick tricks for you to solve this problem. SOLUTION  The following long formula can help you quickly transpose a range of data into a column, please do as this: 1. First, define a range name for your range of data, select the range data that you want to convert, right click and choose  Define Name  form the context menu. In the  New Name  dialog box, enter the range name you want. Then click  OK . See screenshot: 2. After specify the range name, then click a blank cell, in this example, I will click cell E1, and then input this formula: =INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1) . Note :  MyData  is the range name of the selected data, you can change it as you need. 3. Then drag the formula down to the cel
Post a Comment
Read more

How to compare data in Excel?

-
Image
This example shows two ways to  compare text  in  Excel . One is case-sensitive and one is case-insensitive. 1. Use the EXACT function (case-sensitive). 2. Use the formula =A1=B1 (case-insensitive).
Post a Comment
Read more

How to convert rows into comma separated values in SQL Oracle (4 ways to convert)

-
Image
I have this requirement many times while coding interfaces in oracle apps. Every time, I use different options.    Here, I tried to list out the four different ways of achieving the same. Let’s take the well-known SCOTT schema for our example. The requirement is to show the manager name along with sub-ordinate names in a comma separated list. So first, let us simply join the tables to see the records, OPTION 1: LISTAGG function ( Tested in Oracle 11g) SELECT     manager . ename manager ,           LISTAGG ( suboridinates . ename , ',' )   WITHIN   GROUP   (order   by suboridinates . ename )  suboridinates_list      FROM  emp suboridinates ,  emp manager     WHERE  manager . empno  =  suboridinates . mgr     GROUP   BY    manager . ename ORDER   BY  manager . ename ; OPTION 2: WM_CONCAT function (Tested in 11g version) Caution: This function is undocumented and unsupported by Oracle SELECT     manager . ename manager ,          
Post a Comment
Read more