This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jul 26, 2013 7:47 AM by Greg.Spall Go to original post RSS
  • 15. Re: Using trunc as difference measure btwn two dates
    robleh7 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points