1 2 Previous Next 16 Replies Latest reply: Jul 26, 2013 9:47 AM by Greg Spall Go to original post RSS
      • 15. Re: Using trunc as difference measure btwn two dates
        robleh7

        Gregg, what I have figured on this is the following:

         

        The revised query below gets 728 records but instead of giving me difference in days I simply output the columns themselves. I will have to research what the prevous person did in MS SQL Server more closely:

         

        SELECT id, objid, createdate,

        trunc(end_date -createdate) - trunc(start_date -createdate)  as difference

        FROM HNEMap, greggs_date

        where active =1;

         

        But maybe you can be of help with an ORA 942 error I'm getting. You see I've been tasked with converting many queries in MS SQL Server to their Oracle equivalent.

         

        The query below fails with ORA 942, But I can't see why every single table exists and I don't believe it's a matter of privileges.

        SELECT  IdMap.ObjId, IdMap.Id AS Mrn, IdMap.AsgnAuthorityId,IdMap.SystemId,HNEAudit.MsgControlId,HNEAudit.CreateDate,HNEAudit.HNEAuditID

        FROM IdMap, HNEMap,  greggs_dict_triggers, HNeAudit

        WHERE ((IdMap.Type='MRN')  AND (IdMap.Active=1) AND (HNEMap.Active=1)  AND (IdMap.ObjId = HNEMap.ObjId)

        AND ((IdMap.SystemId=50440692) Or (IdMap.SystemId=161983401)) AND (IdMap.Id=greggs_dict_triggers.MRN) AND (IdMap.AsgnAuthorityId=greggs_dict_triggers.AsgnAuthorityId) AND (HNEAudit.ObjId=IdMap.ObjId) )

        ORDER BY HNEAudit.HNEAuditID

        • 16. Re: Using trunc as difference measure btwn two dates
          Greg Spall

          To "see" a table you need the following:

           

          a) you need to own the table, or

          b) you have a private synonym pointing to the table in another schema, or

          c) a public synonym points to the table in another schema.

           

          In any case, you also need privileges on the table in question.

           

          without more information, no I can't help any more.

           

          Try showing results of these queries:

           

          select owner, object_name, object_type from dba_objects
          where object_name in ( 'IDMAP', 'HNEMAP',  'GREGGS_DICT_TRIGGERS', 'HNEAUDIT', 'IdMap', 'HNEMap',  'HNeAudit' );
          
          1 2 Previous Next