Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2013
    Posts
    46

    Unanswered: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries

    Hi everyone

    I am trying to use order by but I am getting the error in the title.

    The data I am pulling is correct I just cant figure out how to order by the last 8 numbers that is my NUMBER column. I tried adding FOR XML AUTO to my last line in my query :

    From AP_DETAIL_REG where AP_BATCH_ID = 1212 and NUMBER is not null order by NUMBER FOR XML AUTO) as Temp(DATA) where DATA is not null

    but no change same error.
    Output:
    1234567890000043321092513 00050020

    Select DATA from(
    select '12345678'+
    left( '0', 10-len(cast ( CONVERT(int,( INV_AMT *100)) as varchar))) +
    cast (CONVERT(int,(INV_AMT*100)) as varchar) +
    left('0',2-len(CAST (MONTH(DATE) as varchar(2))))+
    CAST (MONTH(DATE) as varchar(2)) +
    left('0',2-len(CAST (day(CHECK_DATE) as varchar(2)))) +
    CAST (day(DATE) as varchar(2))+right(cast
    (year(DATE)
    as nvarchar(4)),2)+' 0'+cast (NUMBER as varchar) +
    right(SPACE(37),37-LEN(cast (NUMBER as varchar) )) as 'DATA'
    From AP_DETAIL_REG where AP_ID = 1212 and NUMBER is not null

    UNION ALL
    select '12345678'+left( '0', 10-len(cast (convert(varchar, COUNT(*)) as varchar))) +
    cast (convert(varchar, COUNT(*)) as varchar)+left( '0', 10-len(cast ( CONVERT(int,(sum(INV_AM)*100)) as varchar))) +
    cast (CONVERT(int,(sum(INV_AM)*100)) as varchar) as 'DATA'
    From AP_DETAIL_REG where AP_ID = 1212 and NUMBER is not null ) as Temp where DATA is not null


    Thank you for your help!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't see any ORDER BY in your posted code.
    Why do you need to order the data in your view or subquery?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries
    These things are all tables. Tables have no ordering by definition. Therefore, an ORDER BY clause (which is part of the CURSOR declaration, not DML) makes no sense.
    Your posting is COBOL written with T-SQL. That language uses sequential files and treats all data as strings. You also use a weird mix of CAST() and CONVERT(); why?

  4. #4
    Join Date
    Feb 2013
    Posts
    46
    This is an old stored procedure and I received a request to order the data by the last 8 numbers in ascending order. The application that uses the stored procedure creates a txt file this is why is in string format.
    I did try to use order by:
    From AP_DETAIL_REG where AP_BATCH_ID = 1212 and NUMBER is not null order by NUMBER FOR XML AUTO) as Temp(DATA) where DATA is not null
    and I am getting the same error.

  5. #5
    Join Date
    Feb 2013
    Posts
    46
    Thank you all for your help I solved my problem.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    How about this in a VIEW or CTE?

    I received a request to order the data by the last 8 numbers in ascending order.
    REVERSE ((SUBSTRING (REVERSE(foobar), 1, 8)))

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Or in SQL Server:
    Code:
    Right(foobar, 8)
    Alternative
    Code:
    SubString(foobar, Len(foobar) - 8, 8)
    George
    Home | Blog

Posting Permissions

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