Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    12

    Unanswered: Creating Views with Order by clause in DB2

    Hey can anyone tell me how to create a view with an order by clause in DB2 UDB V8.2

    Regards,
    Mahesh.B.S

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    CREATE view testview AS
    SELECT * FROM
    (SELECT * FROM emp ORDER BY 1) AS t
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2006
    Posts
    12

    Problem with order by clause in create view

    Quote Originally Posted by Marcus_A
    CREATE view testview AS
    SELECT * FROM
    (SELECT * FROM emp ORDER BY 1) AS t
    Hi Marcus,

    I tried to create a view using the example hint suggested by you as shown below:-

    CREATE VIEW V_IPSTAYDETAILS(IPNUMBER,ADMDATE,NAME,BILLED,OPNO, ROOM,ROOMNO)
    AS SELECT A.IPNO AS IpNumber,A.ADMDATE,NAME,BILLED,A.OPNO,A.ROOM,C.ROO MNO
    FROM
    (SELECT A.IPNO AS IpNumber,A.ADMDATE,NAME,BILLED,A.OPNO,A.ROOM,C.ROO MNO from PMIPDETAILS A,PMOPDETAILS B,PMROOMS C
    WHERE
    A.OPNO = B.OPNO
    AND DISCHARGED = 0
    AND A.ROOM = C.CODE
    AND A.ADMDATE >= '2006-01-04-00.00.00.000000'
    AND A.ADMDATE <= '2006-01-05-00.00.00.000000'
    ORDER BY A.IPNO DESC) AS TEST!

    I got an error message after executing the above query like this:-

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0206N "A.IPNO" is not valid in the context where it is used.
    SQLSTATE=42703

    Kindly provide the correct piece of code to create view in this regard.

    Regards,
    Mahesh.B.S

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    CREATE VIEW V_IPSTAYDETAILS(IPNUMBER,ADMDATE,NAME,BILLED,OPNO, ROOM,ROOMNO)
    AS SELECT test.IpNumber,test.ADMDATE,NAME,BILLED,test.OPNO,t est.ROOM,test.rooMNO
    FROM
    (SELECT A.IPNO AS IpNumber,A.ADMDATE as admdate,NAME,BILLED,A.OPNO as opno,A.ROOM as room,C.ROOMNO as roomno from PMIPDETAILS A,PMOPDETAILS B,PMROOMS C
    WHERE
    A.OPNO = B.OPNO
    AND DISCHARGED = 0
    AND A.ROOM = C.CODE
    AND A.ADMDATE >= '2006-01-04-00.00.00.000000'
    AND A.ADMDATE <= '2006-01-05-00.00.00.000000'
    ORDER BY A.IPNO DESC) AS TEST
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2006
    Posts
    12

    Thanks for the same

    Hi Sathyaram,

    The query is now working.Thanks for the code.

    Regards,
    Mahesh

Posting Permissions

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