PL/SQL --may be a loop question?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: PL/SQL --may be a loop question?

  1. #1
    Join Date
    Feb 2001
    Posts
    44

    Red face

    I have two columns, one column with DATE datatype and second column with NUMBER datatype. On third column I want this to be happen.

    Column 1 Column 2 Column3
    ----------- ------------ ------------
    02-01-02 1:00pm 1000 1
    02-01-02 1:00pm 1000 2
    02-01-02 1:00pm 1000 3
    02-10-02 2:00pm 2000 1
    02-10-02 2:00pm 2000 2
    02-14-02 4:00pm 3000 1
    02-28-02 5:00pm 4000 1
    02-01-02 1:00pm 1000 4

    Do you see what's going on. If the rows have same column 1 and column 2 value, I want column 3 to be increment like 1,2,3 etc....

    How can this be done when Column 1 datatype and Column 2 datatype is different?

    I think I need a procedure here...but I have no clue here...please help...=)

  2. #2
    Join Date
    Feb 2001
    Posts
    44
    oh this is not clear....

    Column 1 supposed to have date and time

    Column 2 supposed to have number like 1000,2000, etc..

    Column 3 will be the new column that I want to populate...

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Code:
    SELECT
    	COL1	,
    	COL2	,
    	ROW_NUMBER()
    		OVER	(
    			PARTITION BY
    				COL1,
    				COL2
    			ORDER BY
    				???
    			)
    		AS	COL3
    FROM
    	T1
    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Feb 2001
    Posts
    44
    Got the following error.

    select issuedeltas_createdate, problem_link,
    row_number over
    ( partition by issuedeltas_createdata, problem_link order by problem_link)
    as newcol from ph_new
    /


    ERROR at line 3:
    ORA-00923: FROM keyword not found where expected

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    First, this will only work under 8i (8.1.6.2 or later, to be exact)
    Second, it will not work in PL/SQL directly - you will have to use Dynamic SQL withing PL/SQL.
    Third, you missed the () after the call to the ROW_NUMBER () function.
    Fourth, the ORDER BY cannot be on the same column as a PARTITION BY. The question is: Within each partition (where the first 2 columns are the same), how do you want to order the rows? If you don't care, then omit the ORDER BY clause entirely.

    HTH,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Feb 2001
    Posts
    44
    got it. But I have another question. How about in the following case.

    Column 1 Column 2 Column 3
    02-20-02 1000 1
    02-20-02 1000 1
    02-20-02 1000 1
    02-21-02 2000 2
    02-21-02 2000 2
    02-22-02 3000 3
    02-22-02 3000 3
    02-23-02 4000 4


    how about this?...

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Code:
    SELECT
    	COL1	,
    	COL2	,
    	RANK()
    		OVER	(
    			ORDER BY
    				COL1,
    				COL2
    			)
    		AS	COL3
    FROM
    	T1
    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Change RANK() to DENSE_RANK() in Chris's last example....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Shoot! Had them flipped again! I always do that!

    Personally, I think it's Oracle's definitions that are backwards.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width