ORACLE ROWNUM

Question

I have an employee table called ID_EMPLOYEE_MASTER with the fields

EMP_CODE,
EMP_NAME,
EMP_JOIN_DATE,
EMP_STATUS,
EMP_BASIC,
EMP_ACCOUNT,
EMP_BANK

I want to query all the employees whose status is 'P' ie..PRESENT,THEIR BANK ACCOUNT ie.. EMP_ACCOUNT, THEIR BASIC ie EMP_BASIC,THEIR NAME ie.. EMP_NAME, then give a sequence number, then their EMP_CODE

I have tried the following query:

SELECT'01'||TO_CHAR(SYSDATE,'MMYY')||RPAD(EMP_ACCOUNT,16,'0')||
LPAD(EMP_BASIC,18,'0')||RPAD(EMP_NAME,32,'0')||LPAD(ROWNUM,4,'0')||
LPAD(EMP_CODE,10,'0') AS " " 
FROM ID_EMPLOYEE_MASTER WHERE EMP_STATUS='P' 
                        AND EMP_BANK='BM_CB' ORDER BY EMP_JOIN_DATE,ROWNUM

I would like the format of the display to be:

0115090023194700084016000000000002112000DILIPKUMARAG00000000000000000000010000000029

The problem is the result doesn't get display properly.

If Dilip Kumar is the oldest employee, his name gets displayed first but his sequence number doesn't get displayed as 0001 but it comes out as 0013.

The other problem is salary, I would like it to be displayed in OMANI RIALS and some BAISA, which is 3 places after the decimal, but I'm not able to get the DAISA displayed, only RIALS get displayed. The output I get is like the following:

0115090023194700084016000000000000002112DILIPKUMARAG0000000000000000000130000000029

Please help me...:(

Answer 1

ROWNUM is assigned as rows are retrieved, before the ORDER BY is applied. So they won't come out in the sequence you want. There are a couple of different tricks you can use.

(1) Nested queries

SELECT <your long expression here> FROM (
  SELECT *
    FROM id_employee_master
  ORDER BY emp_join_date
  )

The ROWNUMs in the outer query will be assigned as rows are returned from the innery query, so they will come out in the order you want. This is kind of a hack.

(2) Analytic function

Instead of ROWNUM, use "ROW_NUMBER() OVER ( ORDER BY emp_join_date )". This will assign numbers based on that explicit ordering. This is a much cleaner way of doing it.

On the other problem ... I guess EMP_BASIC is the salary? If it's a NUMBER type then you are implicitly converting it to a string, and the default formatting will drop insignificant digits. You want to use an explicit TO_CHAR call to specify the exact formatting you want.