Home » SQL & PL/SQL » SQL & PL/SQL » ordering by a non-database item
ordering by a non-database item [message #207993] Thu, 07 December 2006 13:28 Go to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
Hi everybody

I want to order by emp.dsp_last_name which is obviously a non database item made up of last_name concatenate first name. Where do I put the order by clause?

BEGIN
  DECLARE
    CURSOR C IS
      SELECT NVL(CORP.REGIST_NAME,IND.LAST_NAME||', '||IND.FIRST_NAME)
      FROM   	INDIVIDUAL IND,
		CORPORATION CORP,
		TAX_PAYER TP
      WHERE  	TP.TAX_PAYER_NO = :EMP.TAX_PAYER_NO
      AND	IND.TAX_PAYER_NO(+) = TP.TAX_PAYER_NO
      AND	CORP.TAX_PAYER_NO(+) = TP.TAX_PAYER_NO        
  BEGIN
    OPEN C;
    FETCH C
    INTO   :EMP.DSP_LAST_NAME;
         
    IF C%NOTFOUND THEN
      RAISE NO_DATA_FOUND;
    END IF;
    CLOSE C;
  EXCEPTION
    WHEN OTHERS THEN
      CGTE$OTHER_EXCEPTIONS;
  END;
END;
Re: ordering by a non-database item [message #208004 is a reply to message #207993] Thu, 07 December 2006 15:17 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Try adding PERSON_NAME as shown below.

BEGIN
  DECLARE
    CURSOR C IS
      SELECT NVL(CORP.REGIST_NAME,IND.LAST_NAME||', '||IND.FIRST_NAME) PERSON_NAME 
      FROM   	INDIVIDUAL IND,
		CORPORATION CORP,
		TAX_PAYER TP
      WHERE  	TP.TAX_PAYER_NO = :EMP.TAX_PAYER_NO
      AND	IND.TAX_PAYER_NO(+) = TP.TAX_PAYER_NO
      AND	CORP.TAX_PAYER_NO(+) = TP.TAX_PAYER_NO   
      ORDER BY  PERSON_NAME;

  BEGIN
    OPEN C;
    FETCH C
    INTO   :EMP.DSP_LAST_NAME;
         
    IF C%NOTFOUND THEN
      RAISE NO_DATA_FOUND;
    END IF;
    CLOSE C;
  EXCEPTION
    WHEN OTHERS THEN
      CGTE$OTHER_EXCEPTIONS;
  END;
END;


and I think you better check some of your syntax in your cursor. I'm not an expert on cursors as most of my code uses implicit cursors rather than explicit, as yours is. However, if I read the cursor right, you want to provide the "WHERE" clause with a variable value (:EMP.TAX_PAYER_NO) and you're not passing that value to your cursor with the "OPEN" clause.

I do know that the ORDER BY clause will work though as I tested it using our EMPLOYEES table...

select emp_lname||', '||emp_fname EMP_NAME
from employees
where emp_site_code = 160
order by EMP_NAME


HTH,
Ron
Re: ordering by a non-database item [message #208005 is a reply to message #208004] Thu, 07 December 2006 15:48 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Oops, brain cramp. My addition was incorrect.

[Updated on: Thu, 07 December 2006 15:54]

Report message to a moderator

Previous Topic: How to refer to the whole new record (:new) in table trigger
Next Topic: Materialized view refresh (merged 2 threads)
Goto Forum:
  


Current Time: Sat Dec 10 11:11:52 CST 2016

Total time taken to generate the page: 0.16423 seconds