SQL & PL/SQL :: Arranging Data In Required Format Using Decode By Hardcoding All Values
Feb 26, 2012
I have a sample table where I am arranging data in required format using 'decode' by hardcoding all values, how can we do same using pivot method, can pivot be only used when we want aggregate values in oracle?Also can't we do what i did, using generic max(decode) with select query, if it were a large table (i don't know which would be pivot column here?) ?
i like to display a Table data like the below format,
Output:
EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 00094 122 153 145 224 245 545 114 544 444 111 555 222 00095 233 123 145 224 205 545 114 444 444 111 555 222 00096 163 123 145 224 215 545 114 551 444 111 555 222 00097 163 0 145 224 215 545 114 551 444 111 555 222 conditions:
where condition: where year = 2007
Table Structure:
create table HR_PAYSLIP ( EMP_NO VARCHAR2(6) not null, YEAR NUMBER(4) not null, MONTH NUMBER(2) not null, BASIC_PAY NUMBER(9,2), )
Insert Command;
INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY) VALUES(00046, 2007, 1, 2314);
Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month
begin insert into plc_dw_dry_run_fic_rfsh values (424740,'1','','LTML','48000'); insert into plc_dw_dry_run_fic_rfsh values (424736,'1','','LTML','32000'); insert into plc_dw_dry_run_fic_rfsh values (424738,'1','','LTML','128000'); insert into plc_dw_dry_run_fic_rfsh values (424783,'1','','LTML','96000'); insert into plc_dw_dry_run_fic_rfsh values (424789,'2','','LTML','96000'); insert into plc_dw_dry_run_fic_rfsh values (424750,'1',198,'LTML','10000'); insert into plc_dw_dry_run_fic_rfsh values (424760,'1',199,'LDFM','20000'); insert into plc_dw_dry_run_fic_rfsh values (424770,'1','','LTML','192000'); end;
Rules --------- 1) First next_cpn_lvl_id1 should be considered (next_intrvl_code can be anything), if next_cpn_lvl_id1 is 198 or 199 then new_ltr_code should have 1a.
2) If next_cpn_lvl_id1 is not 198 or 199, then next_intrvl_code should be considered
a) if next_intrvl_code is LTML and mod of next_intrvl_value and 96000 is zero then new_ltr_code should be 1a b) if next_intrvl_code is LTML and mod of next_intrvl_value and 48000 is zero then new_ltr_code should be 1b
3) If 1 & 2 are not satisfied, ltr_code should be assigned to new_ltr_code.
While am working with Decode just found something, why it is returning like this.
I Ran this query where i have used all the date format as DD/MM/YYYY SELECT DECODE(TO_DATE('25/04/2001','DD/MM/YYYY') , TO_DATE('01/01/1900','DD/MM/YYYY'), NULL , TO_DATE('25/04/2001','DD/MM/YYYY')) TEST FROM DUAL
Quote: TEST ------------- 25-APR-01
But the resule i got in DD-MON-YY
i tried searching this forum with searching strings Decode date format.. or similer i found nothing.
This is my first post in this portal. I want display the details of emp table.. for that I am using this SQL statement.
select * from emp where mgr=nvl(:mgr,mgr);
when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it isdisplaying all the records except the mgr with null values.
1)I want to display all the records when I won't give any input including nulls 2)I want to display all the records who's mgr is null
Is there any way to incorporate to include all these in a single query..
CREATE TABLE "TEST_JET" ("K1" NUMBER, "K2" NUMBER, "K3" NUMBER, "K4" VARCHAR2(1)) ; REM INSERTING into TEST_JET Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'I'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'U'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'D'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,2,'U'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,2,'D'); Insert into TEST_JET (K1,K2,K3,K4) values (1,3,5,'I'); Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'U'); Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'D'); Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'T'); [code]....
based on the above result set , for a particular group ,only that op will be retained which comes out in the query . say for example , we have got 1,2,3,'D' for group 1,2,3
now since we have got the D Operation from the above query , i don't need the other two rows .i.e. (1,2,3,'I'); (1,2,3,'U');
what is the best way to delete the data we don't want retaining the rows we want ,using a single sql statement . Also , for the result set row 7,7,7,T I first need to delete the group containing T operation, and insert two new rows .i.e. 7,7,7,D and 7,7,7,I .
CASE expects data type consistency, DECODE not expecting.
Obviously both functions handling data types are different let it be
SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual;
RESULT --- 2 SQL> select case 2 2 when 1 then 1 3 when 2 then 2 4 else 3 5 end "RESULT" from dual;
RESULT 2 SQL> select case 2 2 when 1 then 1 3 when '2' then 2 4 else 3 5 end "RESULT" from dual;
ERROR at line 3: ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
know cause of error here????I mean " every time case exp checking data type consistency"
my thought is
I am trying to get same output but different methods. Yes, clearly states this is one of the Oracle bug!
I want to know how oracle handles here ???? i mean 3rd query.Purely i am testing this function with dual(dummy) table... obviously, no possibilities for different data type.next one i am not sure about Oracle compares int variables to int variables, char variables to char variables,
I think so .... that's why oracle throws error. am i right ??ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
see this query
SQL> select case 2 2 when 1 then 1 3 when '2' then 2 4 else 3 5 end "RESULT" from dual;
// clearly i am stating what's error on 3rd line ?????Even if I refer reference books some concepts are blind to understand.
Data Type Consistency CASE and Decode...CASE expects data type consistency, DECODE not expecting.Obviously both functions handling data types are different..let it be
SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual; [color=red]RESULT --- 2 SQL> select case 2 2 when 1 then 1 3 when 2 then 2 4 else 3 5 end "RESULT" from dual; [code]...
I am trying to get same output but different methods. Yes, clearly states this is one of the Oracle bug! Oracle compares int variables to int variables, char variables to char variables, If any discrepancy between the two data types, then the query will fail but here not different data types.
Even if I refer reference books some concepts are blind to understand.
How can i apply currency format to the select list values queried from database tables. The data is stored in database in number format. The user wants to see 1000 separator commas in the list item values.
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.
I have a query on displaying data as per my requirement. I have created a table called sales it has four columns
create table sales(country,state,district,sales); and am inserting some same data
insert into sales('india','TN','Chennai',100); insert into sales('india','TN','KPURAM',120); insert into sales('india','TN','Bangalore',35); insert into sales('india','ANDR','Guinder',100); insert into sales('india','ANDR','Nellai',76); insert into sales('london','city-a','xstreet',89); insert into sales('london','city-a','binroad',100);
select * from sales;
country state district sales india TN chennai 100 india TN KPURAM 120 india TN Bangalore 35 india ANDR Guinder 100 india ANDR Nellai 76 london city-a xstreet 89 london city-a binroad 100
the data is displayed in this format. How i am trying to display data.
I have one table storing all the information about employye, i have to develop three different reports based on the same table with different groupings , is there a easy way to call each format differently using single interface ,something like ref cursor.I dont want to create two or 3 different reports , instead choose the format.
I want to print data from a clob in XML format and use the following
PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is xmlstr varchar2(32767); line varchar2(32767); cnt NUMBER;
[Code]...
However the length of the clob is 13832630 which is too large for a VARCHAR2. Thus my output of line is cut off at 4000 characters. How can i increase this... Do i overlook something here in my code?
I have a table where multiple combination of records are store and i want to display data in range format as below, there is any way to group data as below.
CREATE TABLE DATA1 ( ID NUMBER(6), DAT_ID NUMBER(6), RNK NUMBER(2) ); Insert into DATA1 (ID, DAT_ID, RNK)
[code]....
now after having this data from "data1" table , we need to get the row from "data2" table.in "data2" table there are total 6 combination on basis of "POS,ORDER" [ there are only 2 "ORDER" i.e. 'F' and 'S' , where as POS value can be changed, BUT THE "POS,ORDER" COMBINATION WILL HAVE ONLY 6 UNIQUE COMBINATION. ] so, for "POS and DAT_ID" combination we need to get the lowest rank data first, if that is not present then get the other rank given in "DATA1" table and so on and if no rank is present then select the NULL row row data from "DATA2" table for ex: in DATA1 table for count(*)>1 and id=1, we have data as
-------------- IDDAT_IDRNK
11231 11242 11253 --------------
so, in "DATA2" table, first we will see for "POS and ORDR" combination which DAT_ID is present, i.e. in case od POS=11 and ordr=F, we will select
111231FD1
as it is having lowest rank in "DATA1" table and it is present in "DATA2" table,
for POS=12 and ordr=F, we will select
121242FD1
as we don't have "123 and 1" in "DATA2" table so we will select the next rank given in "DATA1" ( i.e. 124 and 2 ), similarly , for POs=31 and ordr=S, we will select, as this is the next available rank and DAT_ID present in "DATA2" table
1D131S1253
and if there is no rank present from "DATA1" table in "DATA2" table then we will select the NULL row, i.e. for POS=21 and ordr=F, we need to select :
1D121F
"there will be 6 row for each id"
the output we want is : ----------------------------------------- IDNAMEPOSORDER DAT_IDRNK 1D111F1231 1D112F1242 1D121F 1D12321S1231 1D2322S1242
generating an xml file using the below data.Table name is T_Data having 4 columns as given below with some data.
Region Divison District Store ---------- ----------------------- ----------- ----------- Northwest Northern California San Jose SJStore1 Northwest Northern California San Jose SJStore2 Northwest Northern California North LA LAStore1 Northwest Northern California North LA LAStore2 Northwest Northern California North LA LAStore3
I want to generate an XML File using SQL/XML Functions and the XML File should look like as given below.
i have data in date format for in time actually it should be 9:30 am and out time at 2:30 pm
SELECT MIN(IN_TIME) AS IN_TIME,MAX(OUT_TIME) AS OUT_TIME FROM EMP_ATND_DETAIL AS ET, EMP_ATND_INFO AS EAI WHERE ET.EMP_ATND_INFO_ID=EAI.EMP_ATND_INFO_ID AND ATND_DATE = '2006-10-25' AND EMP_ID =24
I don´t care about the order of the values in the row. In other words, I want to get disjoint sets of data connected by any of both values.Every pair in the input table is unique.
I have seen in the web that it is possible to do using connect by and hierarchical retrieving but I've been trying to make a lot of combinationts and I can reproduce the output.
how to get data from a Table in Date and Time format? Curently I'm using To_TIMESTAMP for a date range.
Here is the code Snippet -
Select * FROM TABLE_Name WHERE COUNTRY= 'IN' AND CALC_DATETIME between TO_TIMESTAMP('2013/06/05', 'YYYY/MM/DD HH:MI:SS') AND TO_DATE ('2013/07/08', 'YYYY/MM/DD HH:MI:SS')
