Toad World® Forums

Describe object window rendering view incorrectly

Toad for Oracle
#1

/* Formatted on 8/18/2020 9:31:43 AM (QP5 v5.360) */
CREATE OR REPLACE FORCE VIEW SYSADM.PS_HHC_PLYACCESS
(
OPRID,
OPRDEFNDESC,
HHC_LOGIN_DATE,
HHC_HOUR,
LOGINATTEMPTS
)
BEQUEATH DEFINER
AS
SELECT X.OPRID,
X.OPRDEFNDESC,
X.LOGIN_DATE,
TO_CHAR (
CAST (
(TO_TIMESTAMP (
TO_CHAR (X.LOGIN_DATE, 'mm/dd/yyyy')
|| ' '
|| X.LOGIN_HR
|| ':00:00',
'mm/dd/yyyy hh24:mi:ss'))
AS TIME

after word "AS TIME" the remaining part of the view does not format correctly it is left on the same line and does not display in the f4 describe window viewable area... All out other views show properly and show all the text on in the window formatted correctly. I have no custom formatting options setup.

                                                                                                                                  )  ,  'HH12 AM'         )  LOGIN_HR         ,  X . NUM_LOGINS           FROM (       SELECT     A . OPRID      ,  B . OPRDEFNDESC            ,  TO_DATE       ( TO_CHAR       ( A . LOGINDTTM          ,  'mm/dd/yyyy'            )  ,  'mm/dd/yyyy'            )  LOGIN_DATE           ,  TO_CHAR       ( CAST    ( ( A . LOGINDTTM         )  AS   TIME    )  ,  'HH24'      )  LOGIN_HR         ,  COUNT     ( * )  NUM_LOGINS           FROM     PSACCESSLOG            A  ,  PSOPRDEFN          B  WHERE      A . OPRID      =  B . OPRID      AND    A . PT_SIGNON_TYPE               =  1  AND    A . LOGINDTTM          >=   ( SYSDATE        -  INTERVAL         '1'    DAY   )  GROUP      BY   A . OPRID     ,  B . OPRDEFNDESC           ,  TO_DATE       ( TO_CHAR       ( A . LOGINDTTM         ,  'mm/dd/yyyy'            ) ,  'mm/dd/yyyy'            ) ,  TO_CHAR       ( CAST    ( ( A . LOGINDTTM         )  AS   TIME    ) ,  'HH24'      )  ORDER      BY   3  DESC    ,  4  DESC     )  X;

CREATE OR REPLACE PUBLIC SYNONYM PS_HHC_PLYACCESS FOR SYSADM.PS_HHC_PLYACCESS;

#2

Can you post the entire view DDL?

Or can you reproduce it with this:

select
TO_CHAR(CAST((TO_TIMESTAMP(TO_CHAR(sysdate, 'mm/dd/yyyy') || ' ' || '17' || ':00:00', 'mm/dd/yyyy hh24:mi:ss')) AS TIME), 'HH24')
from dual

That seems to format OK for me.

#3

When replacing AS TIME by AS TIME3 I'm getting a nicely formatted statement:

CREATE OR REPLACE FORCE VIEW SYSADM.PS_HHC_PLYACCESS
(
   OPRID,
   OPRDEFNDESC,
   HHC_LOGIN_DATE,
   HHC_HOUR,
   LOGINATTEMPTS
)
BEQUEATH DEFINER
AS
   SELECT X.OPRID,
          X.OPRDEFNDESC,
          X.LOGIN_DATE,
          TO_CHAR (
             CAST (
                (TO_TIMESTAMP (
                       TO_CHAR (X.LOGIN_DATE, 'mm/dd/yyyy')
                    || ' '
                    || X.LOGIN_HR
                    || ':00:00',
                    'mm/dd/yyyy hh24:mi:ss'))
                   AS TIME3),
             'HH12 AM')   LOGIN_HR,
          X.NUM_LOGINS
     FROM (  SELECT A.OPRID,
                    B.OPRDEFNDESC,
                    TO_DATE (TO_CHAR (A.LOGINDTTM, 'mm/dd/yyyy'), 'mm/dd/yyyy')
                       LOGIN_DATE,
                    TO_CHAR (CAST ((A.LOGINDTTM) AS TIME3), 'HH24')
                       LOGIN_HR,
                    COUNT (*)
                       NUM_LOGINS
               FROM PSACCESSLOG A, PSOPRDEFN B
              WHERE     A.OPRID = B.OPRID
                    AND A.PT_SIGNON_TYPE = 1
                    AND A.LOGINDTTM >= (SYSDATE - INTERVAL '1' DAY)
           GROUP BY A.OPRID,
                    B.OPRDEFNDESC,
                    TO_DATE (TO_CHAR (A.LOGINDTTM, 'mm/dd/yyyy'),
                             'mm/dd/yyyy'),
                    TO_CHAR (CAST ((A.LOGINDTTM) AS TIME3), 'HH24')
           ORDER BY 3 DESC, 4 DESC) X

John, do you think this is a parser problem?

Thanks,
Andre

#4

Hi Andre,
"AS TIME" is part of the CAST command (not a column alias), so having TIME3 there is not valid. It does seem like a parser problem.

-John

#5

I created issues QP-3742 for this one.

Scott, thanks for reporting!
Andre

#6

Has been fixed in component qp5.dll version 5.363 .