CODEWITHSUNDEEP
sqloracle-database
Slim
Slim

Reputation: 1744

Can't get the row number

I have this code in a package:

    BEGIN
      IF user_status_i = 'all' THEN
        OPEN l_get_data FOR

        SELECT 
           row_number() OVER (ORDER BY u.us_fullname) rn,
           a2.av_value as first_name, a3.av_value as last_name, a4.av_value as email, a5.av_value as phone, 
           u.us_id, u.us_fullname, a6.av_value as user_type, a7.av_value as line_manager_1, 
           a8.av_value as line_manager_2, a9.av_value as line_manager_3, a10.av_value as line_manager_4, 
           a11.av_value as line_manager_5, u.us_flags, a12.av_value as business_unit, 
           a13.av_value as position, a14.av_value as middlename
           FROM us_user u

                 INNER JOIN av_attrvalue a1 
                     ON a1.AV_TOKEN = 'USER:'||u.US_ID AND a1.AV_AP_ID='InSign' AND a1.av_at_id = 'Code' AND a1.av_value = id_i
                 INNER JOIN av_attrvalue a2 
                     ON a2.AV_TOKEN = 'USER:'||u.US_ID AND a2.AV_AP_ID='InSign' AND a2.av_at_id = 'FIRSTNAME' 
                 INNER JOIN av_attrvalue a3 
                     ON a3.AV_TOKEN = 'USER:'||u.US_ID AND a3.AV_AP_ID='InSign' AND a3.av_at_id = 'LASTNAME' 
                 INNER JOIN av_attrvalue a4 
                     ON a4.AV_TOKEN = 'USER:'||u.US_ID AND a4.AV_AP_ID='InSign' AND a4.av_at_id = 'ADDR' 
                 LEFT OUTER JOIN av_attrvalue a5 
                     ON a5.AV_TOKEN = 'USER:'||u.US_ID AND a5.AV_AP_ID='InSign' AND a5.av_at_id = 'MobilePhone' 
                 INNER JOIN av_attrvalue a6 
                     ON a6.AV_TOKEN = 'USER:'||u.US_ID AND a6.AV_AP_ID='InSign' AND a6.av_at_id = 'Type' AND a6.av_value != 'trAgent'
                 LEFT OUTER JOIN av_attrvalue a7 
                     ON a7.AV_TOKEN = 'USER:'||u.US_ID AND a7.AV_AP_ID='InSign' AND a7.av_at_id = 'Line1'
                 LEFT OUTER JOIN av_attrvalue a8 
                     ON a8.AV_TOKEN = 'USER:'||u.US_ID AND a8.AV_AP_ID='InSign' AND a8.av_at_id = 'Line2' 
                 LEFT OUTER JOIN av_attrvalue a9 
                     ON a9.AV_TOKEN = 'USER:'||u.US_ID AND a9.AV_AP_ID='InSign' AND a9.av_at_id = 'Line3' 
                 LEFT OUTER JOIN av_attrvalue a10 
                     ON a10.AV_TOKEN = 'USER:'||u.US_ID AND a10.AV_AP_ID='InSign' AND a10.av_at_id = 'Line4' 
                 LEFT OUTER JOIN av_attrvalue a11 
                     ON a11.AV_TOKEN = 'USER:'||u.US_ID AND a11.AV_AP_ID='InSign' AND a11.av_at_id = 'Line5' 
                 LEFT OUTER JOIN av_attrvalue a12 
                     ON a12.AV_TOKEN = 'USER:'||u.US_ID AND a12.AV_AP_ID='InSign' AND a12.av_at_id = 'Unit' 
                 LEFT OUTER JOIN av_attrvalue a13 
                     ON a13.AV_TOKEN = 'USER:'||u.US_ID AND a13.AV_AP_ID='InSign' AND a13.av_at_id = 'Position'
           LEFT OUTER JOIN av_attrvalue a14
                     ON a14.AV_TOKEN = 'USER:'||u.US_ID AND a14.AV_AP_ID='InSign' AND a14.av_at_id = 'MIDDLENAME' 
                 INNER JOIN hds_hub_companies hc ON hc.company_id = a1.av_value   

WHERE rn BETWEEN (pageN_i * records_i) - records_i + 1 AND (pageN_i * records_i);



      END IF;

I'm trying to get the row number, but when I'm executing it I'm getting 2 errors:

Error(1323,5): PL/SQL: SQL Statement ignored

and

Error(1361,14): PL/SQL: ORA-00904: "RN": invalid identifier

I'm fighting with this since yesterday, I'm sure there is somthing small that I'm missing

Upvotes: 0

Views: 109

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

The alias name rn is not available in the same statement scope. You need to use derived table as shown below

SELECT * FROM
(
SELECT 
           row_number() OVER (ORDER BY u.us_fullname) rn,
           a2.av_value as first_name, a3.av_value as last_name, a4.av_value as email, a5.av_value as phone, 
           u.us_id, u.us_fullname, a6.av_value as user_type, a7.av_value as line_manager_1, 
           a8.av_value as line_manager_2, a9.av_value as line_manager_3, a10.av_value as line_manager_4, 
           a11.av_value as line_manager_5, u.us_flags, a12.av_value as business_unit, 
           a13.av_value as position, a14.av_value as middlename
           FROM us_user u

                 INNER JOIN av_attrvalue a1 
                     ON a1.AV_TOKEN = 'USER:'||u.US_ID AND a1.AV_AP_ID='InSign' AND a1.av_at_id = 'Code' AND a1.av_value = id_i
                 INNER JOIN av_attrvalue a2 
                     ON a2.AV_TOKEN = 'USER:'||u.US_ID AND a2.AV_AP_ID='InSign' AND a2.av_at_id = 'FIRSTNAME' 
                 INNER JOIN av_attrvalue a3 
                     ON a3.AV_TOKEN = 'USER:'||u.US_ID AND a3.AV_AP_ID='InSign' AND a3.av_at_id = 'LASTNAME' 
                 INNER JOIN av_attrvalue a4 
                     ON a4.AV_TOKEN = 'USER:'||u.US_ID AND a4.AV_AP_ID='InSign' AND a4.av_at_id = 'ADDR' 
                 LEFT OUTER JOIN av_attrvalue a5 
                     ON a5.AV_TOKEN = 'USER:'||u.US_ID AND a5.AV_AP_ID='InSign' AND a5.av_at_id = 'MobilePhone' 
                 INNER JOIN av_attrvalue a6 
                     ON a6.AV_TOKEN = 'USER:'||u.US_ID AND a6.AV_AP_ID='InSign' AND a6.av_at_id = 'Type' AND a6.av_value != 'trAgent'
                 LEFT OUTER JOIN av_attrvalue a7 
                     ON a7.AV_TOKEN = 'USER:'||u.US_ID AND a7.AV_AP_ID='InSign' AND a7.av_at_id = 'Line1'
                 LEFT OUTER JOIN av_attrvalue a8 
                     ON a8.AV_TOKEN = 'USER:'||u.US_ID AND a8.AV_AP_ID='InSign' AND a8.av_at_id = 'Line2' 
                 LEFT OUTER JOIN av_attrvalue a9 
                     ON a9.AV_TOKEN = 'USER:'||u.US_ID AND a9.AV_AP_ID='InSign' AND a9.av_at_id = 'Line3' 
                 LEFT OUTER JOIN av_attrvalue a10 
                     ON a10.AV_TOKEN = 'USER:'||u.US_ID AND a10.AV_AP_ID='InSign' AND a10.av_at_id = 'Line4' 
                 LEFT OUTER JOIN av_attrvalue a11 
                     ON a11.AV_TOKEN = 'USER:'||u.US_ID AND a11.AV_AP_ID='InSign' AND a11.av_at_id = 'Line5' 
                 LEFT OUTER JOIN av_attrvalue a12 
                     ON a12.AV_TOKEN = 'USER:'||u.US_ID AND a12.AV_AP_ID='InSign' AND a12.av_at_id = 'Unit' 
                 LEFT OUTER JOIN av_attrvalue a13 
                     ON a13.AV_TOKEN = 'USER:'||u.US_ID AND a13.AV_AP_ID='InSign' AND a13.av_at_id = 'Position'
           LEFT OUTER JOIN av_attrvalue a14
                     ON a14.AV_TOKEN = 'USER:'||u.US_ID AND a14.AV_AP_ID='InSign' AND a14.av_at_id = 'MIDDLENAME' 
                 INNER JOIN hds_hub_companies hc ON hc.company_id = a1.av_value   
) T
WHERE rn BETWEEN (pageN_i * records_i) - records_i + 1 AND (pageN_i * records_i);

Upvotes: 1

TechDo
TechDo

Reputation: 18629

You need to make it as a block to work, since alias name is not accessible in the same block.

select * From(
 SELECT 
     row_number() OVER (ORDER BY u.us_fullname) rn,
     a2.av_value as first_name, a3.av_value as last_name, a4.av_value as email, a5.av_value as phone, 
     u.us_id, u.us_fullname, a6.av_value as user_type, a7.av_value as line_manager_1, 
     a8.av_value as line_manager_2, a9.av_value as line_manager_3, a10.av_value as line_manager_4, 
     a11.av_value as line_manager_5, u.us_flags, a12.av_value as business_unit, 
     a13.av_value as position, a14.av_value as middlename
     FROM us_user u

           INNER JOIN av_attrvalue a1 
               ON a1.AV_TOKEN = 'USER:'||u.US_ID AND a1.AV_AP_ID='InSign' AND a1.av_at_id = 'Code' AND a1.av_value = id_i
           INNER JOIN av_attrvalue a2 
               ON a2.AV_TOKEN = 'USER:'||u.US_ID AND a2.AV_AP_ID='InSign' AND a2.av_at_id = 'FIRSTNAME' 
           INNER JOIN av_attrvalue a3 
               ON a3.AV_TOKEN = 'USER:'||u.US_ID AND a3.AV_AP_ID='InSign' AND a3.av_at_id = 'LASTNAME' 
           INNER JOIN av_attrvalue a4 
               ON a4.AV_TOKEN = 'USER:'||u.US_ID AND a4.AV_AP_ID='InSign' AND a4.av_at_id = 'ADDR' 
           LEFT OUTER JOIN av_attrvalue a5 
               ON a5.AV_TOKEN = 'USER:'||u.US_ID AND a5.AV_AP_ID='InSign' AND a5.av_at_id = 'MobilePhone' 
           INNER JOIN av_attrvalue a6 
               ON a6.AV_TOKEN = 'USER:'||u.US_ID AND a6.AV_AP_ID='InSign' AND a6.av_at_id = 'Type' AND a6.av_value != 'trAgent'
           LEFT OUTER JOIN av_attrvalue a7 
               ON a7.AV_TOKEN = 'USER:'||u.US_ID AND a7.AV_AP_ID='InSign' AND a7.av_at_id = 'Line1'
           LEFT OUTER JOIN av_attrvalue a8 
               ON a8.AV_TOKEN = 'USER:'||u.US_ID AND a8.AV_AP_ID='InSign' AND a8.av_at_id = 'Line2' 
           LEFT OUTER JOIN av_attrvalue a9 
               ON a9.AV_TOKEN = 'USER:'||u.US_ID AND a9.AV_AP_ID='InSign' AND a9.av_at_id = 'Line3' 
           LEFT OUTER JOIN av_attrvalue a10 
               ON a10.AV_TOKEN = 'USER:'||u.US_ID AND a10.AV_AP_ID='InSign' AND a10.av_at_id = 'Line4' 
           LEFT OUTER JOIN av_attrvalue a11 
               ON a11.AV_TOKEN = 'USER:'||u.US_ID AND a11.AV_AP_ID='InSign' AND a11.av_at_id = 'Line5' 
           LEFT OUTER JOIN av_attrvalue a12 
               ON a12.AV_TOKEN = 'USER:'||u.US_ID AND a12.AV_AP_ID='InSign' AND a12.av_at_id = 'Unit' 
           LEFT OUTER JOIN av_attrvalue a13 
               ON a13.AV_TOKEN = 'USER:'||u.US_ID AND a13.AV_AP_ID='InSign' AND a13.av_at_id = 'Position'
     LEFT OUTER JOIN av_attrvalue a14
               ON a14.AV_TOKEN = 'USER:'||u.US_ID AND a14.AV_AP_ID='InSign' AND a14.av_at_id = 'MIDDLENAME' 
           INNER JOIN hds_hub_companies hc ON hc.company_id = a1.av_value   
)x
WHERE rn BETWEEN (pageN_i * records_i) - records_i + 1 AND (pageN_i * records_i);

Upvotes: 1

Related Questions