Currently I have this in the employee table:
Employee_idStart Date(YYYMM)Term(months)Monthly Salary
1200801122000
220050562500
Is it possible to display the above information like this:
Employee_idStart Date(YYYMM)Term(months)Monthly Salary
1200801122000
1200802122000
1200803122000
1200804122000
[code]....
I have to breakdown the sum of qty into each line as single piece ,as example below how to achieve this using sql as i am doing it using a procedure.
CREATE TABLE OT_CUT_ITEM
(
CTI_SYS_ID NUMBER,
CTI_ITEM_CODE VARCHAR2(30 BYTE),
CTI_LEN NUMBER,
CTI_QTY NUMBER
);
[code]....
output required is
CTI_SYS_ID CTI_ITEM_CODE CTI_LEN CTI_QTY
---------- ------------------------------ ---------- ----------
1 A 7000 1
1 A 7000 1
1 A 7000 1
1 A 7000 1
2 B 6000 1
[code]....
I want to create a strung together list of ATM IDs for each ATM Location (as one ATM Location(City) can have many ATMs(term ids) this is to allow transaction facts to be not broken down on several lines depending on how many term ids there are for that ATM Location (whenever a new ATM is set-up, a new row is created in the ATM table).
I know I can string it together using a function but I do not have rights to do it so I created SQL in which I feed in the ATM Location as a parameter. I want to do this for ALL ATMs but that is taking forever - is there any way to optimize the below code.
Select max(term_id),atm_location from (Select
(SYS_CONNECT_BY_PATH(TERM_ID,' ' ) ) term_id,atm_location
from
(select term_id , atm_location
from ATM_TABLE
order by term_id asc
)
Start with TERM_ID IN (Select max(Term_ID) from ATM_TABLE group by
ATM_LOCATION )
I have this code which simply gives me the count of term.
SELECT
OUA_ID
,COALESCE (MAX( DECODE( TERM, 0603, TERM_COUNT, NULL ) ), 0 ) "TERM_0603_CNT"
,COALESCE (MAX( DECODE( TERM, 0702, TERM_COUNT, NULL) ), 0 ) "TERM_0702_CNT"
,COALESCE (MAX( DECODE( TERM, 0705, TERM_COUNT, NULL ) ), 0 ) "TERM_0705_CNT"
,COALESCE (MAX( DECODE( TERM, 0707, TERM_COUNT, NULL ) ), 0 ) "TERM_0707_CNT"
FROM(
SELECT DISTINCT
OUA_ID, TERM_COUNT, TERM
FROM(
SELECT OUA_ID, TERM ,PROVIDER_CDE, COUNT(*) TERM_COUNT
FROM TABLE
WHERE PROVIDER_CDE = 'BILL'
GROUP BY OUA_ID, TERM, PROVIDER_CDE)) GROUP BY OUA_ID
Gives:
OUA_IDTERM_0603_CNTTERM_0702_CNTTERM_0705_CNTTERM_0707_CNT
32710 0 3 7 0
45726 2 0 1 0
Is there a way i could used the rank function (with decode) to only display the 1ST (First) and 2nd(Second) most used term per OUA_ID?
So i get this:
OUA_ID1st MOST USED2nd MOST USED
3271 705 702
45726 603 705