1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query Works in SQL Developer, but Not in Page

Discussion in 'SQL PL/SQL' started by cjakins, Sep 10, 2010.

  1. cjakins

    cjakins Guest

    I have two queries, one of which is a modification of the other. Query #1 works fine in the web page. Query #2 works fine in SQLDeveloper, but throws an error in the web page for some reason. The .jsp code that outputs the data from the query is identical in both pages. The .jsp exception says there are invalid column names. Through testing, I've narrowed them down to be the "LINKDATE" and "FORMATDATE" column names.

    Keep in mind, these same two column names are used without error in page 1, but with the expanded query in page two, they cause an error.

    I have no idea why. In fact, the modified second query is something someone else came up with as I'm not really a programmer. Just enough to be confused!

    The queries:

    1.
    Code (SQL):
    SELECT * FROM(SELECT ID, TITLE, TO_CHAR(RELDATE, 'YYMMDD') AS LINKDATE, TO_CHAR(RELDATE, 'Mon DD, YYYY') AS FORMATDATE, CATEGORY, FILE_EXT, EXTRACT (HOUR FROM duration) || ' hr ' || EXTRACT (MINUTE FROM duration) || ' min ' || EXTRACT(SECOND FROM duration) || ' sec' AS DURATION, SHORTNAME, SHORTDESC, BOOKMARKS, SCRNSIZE FROM PIO.VIDEOS WHERE CATEGORY='kp' ORDER BY RELDATE DESC) WHERE rownum<=12
    2.
    Code (SQL):
    SELECT * FROM PIODBA.TV23_VIDEOS WHERE CATEGORY ='kp' AND ID NOT IN(SELECT ID FROM(SELECT ID, TITLE, TO_CHAR(RELDATE, 'YYMMDD') AS LINKDATE, TO_CHAR(RELDATE, 'Mon DD, YYYY') AS FORMATDATE, CATEGORY, FILE_EXT, EXTRACT (HOUR FROM duration) || ' hr ' || EXTRACT (MINUTE FROM duration) || ' min ' || EXTRACT(SECOND FROM duration) || ' sec' AS DURATION, SHORTNAME, SHORTDESC, BOOKMARKS, SCRNSIZE FROM PIO.VIDEOS WHERE CATEGORY='kp' ORDER BY RELDATE DESC) WHERE rownum<=12) ORDER BY RELDATE DESC
    The .jsp code fragments that call the data but cause error only with query #2:

    Code (Text):
    <%=(((Videos_data = Videos.getObject("LINKDATE"))==null || Videos.wasNull())?"":Videos_data)%>
    Code (Text):
    <%=(((Videos_data = Videos.getObject("FORMATDATE"))==null || Videos.wasNull())?"":Videos_data)%>
    What am I doing wrong? If you know and answer, I'd really appreciate not only knowing what to change, but the "why" or explanation of how my version is incorrect. That way I can learn. :)

    Thanks in advance for any help.