CODEWITHSUNDEEP
sqloracle-databaserange
Trung Tran
Trung Tran

Reputation: 13721

How to select range of records in Oracle

I am trying to select a range of records using a SQL Query:

SELECT COMPANY_ID as SEQ_NO, NULL URBAN_CODE
FROM (SELECT COMPANY_ID as SEQ_NO, NULL URBAN_CODE, ROWNUM num from COMPANY_TABLE)
WHERE num > 1 and num < 9

The error I keep getting is "URBAN_CODE": invalid identifier

I saw this solution from several SO pages but for some reason I keep getting this error.. can someone help? Thanks!

Upvotes: 0

Views: 993

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

The code you showed can't complain about URBAN_CODE. But it will give you ORA-00904: "COMPANY_ID": invalid identifier, so maybe you've swapped the column names over in your post.

You're aliasing your COMPANY_ID column in your inline view, but then still trying to refer to the original unaliased name in the outer query. You can either use the alias, or define it later:

SELECT COMPANY_ID as SEQ_NO, NULL URBAN_CODE
FROM (SELECT COMPANY_ID, ROWNUM num from COMPANY_TABLE)
WHERE num > 1 and num < 9

or

SELECT SEQ_NO, URBAN_CODE
FROM (SELECT COMPANY_ID as SEQ_NO, NULL URBAN_CODE, ROWNUM num from COMPANY_TABLE)
WHERE num > 1 and num < 9

There's not much point having the URBAN_CODE in the inline view either if it's always null; doesn't really hurt, but you aren't using that generated (null) value in the outer query.

You're also using ROWNUM without doing any ordering, so your results will be indeterminate. You really need another layer of inline view:

SELECT COMPANY_ID as SEQ_NO, NULL as URBAN_CODE
FROM (
  SELECT COMPANY_ID, ROWNUM num
  FROM (
    SELECT COMPANY_ID
    FROM COMPANY_TABLE
    ORDER BY COMPANY_ID -- or whatever order you want to impose
  )
)
WHERE num > 1 and num < 9;

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93704

If am not wrong you need to use alias name in outer query.

You cannot use company_Id in outer query because you have given a alias name in sub-select you need to use that.

SELECT SEQ_NO, URBAN_CODE
FROM (SELECT COMPANY_ID as SEQ_NO, NULL as URBAN_CODE, ROWNUM num from COMPANY_TABLE)
WHERE num > 1 and num < 9

Upvotes: 2

Related Questions