Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Unanswered: Beginner Oracle Syntax Question...

    I am trying to create a computed item in my result set that would return blanks for all record except records created yesterday.

    This is what I tried.

    if (Created == SYSDATE(-1)) {'pa$$w0rd'}

    And then for today

    if (Created == SYSDATE) {'1'}

    if (Created == SYSDATE) {'pa$$w0rd'}

    if (Created == 'Sysdate') {'New Users'} else {'Existing User'}


    Any help would be appreciated. Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    That doesn't look like any oracle syntax that I have seen. Are you sure that you are in the correct forum? Are you trying to do a select that will only return rows that were created yesterday?

    select col1,col2,col3
    from my_table
    where trunc(created) = trunc(sysdate-1);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2006
    Posts
    5
    Yea, its actually a reporting tool (HPS) attached to oracle. So this is the results section, im trying to add a computed item to this section. Unfortunately the Syntac is a little different then the SQL section where you set the limits and request.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    So you, actually, need help about the "HPS" reporting tool (I'm sorry, I've never heard of it), not about Oracle. Right? Is there "HPS"-related forum somewhere? Perhaps you'll get the answer there.

  5. #5
    Join Date
    Apr 2006
    Posts
    5
    Well, it uses Oracle syntax for the computed item. For example when we used SQL Server SYSDATE had to be Getdate. Think Crystal Reports connected to a Oracle DB. Right now I have a field that pulls all users, but I want it to return either New User or Existing User. So if the user created date is yesterday I want it to equal 'New User' else 'Existing User'

    HPS stands for Hyperion something Suite.

    I used this
    if (Created == 'SYSDATE=yesterday') {'New Users'} else {'Existing User'}

    but someone told me I can't use text (yesterday) I need to use -1.

    So I am using this, if (Created == 'SYSDATE-1') {'New Users'} else {'Existing User'}

    but it is not reading the created field from yesterday as a new user, still using an existing user.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hyperion Performance Suite, perhaps?

    I *think* I understand what you'd like to get. I'd say that SYSDATE is the problem here, as it contains both date and time. So, when you use pure SYSDATE, it equals (at the moment, dd.mm.yyyy hh24:mi) 19.07.2007. 19:07. If you subtract 1 from it, you'll get "yesterday" at the same time - 18.07.2007. 19:07.

    It means that you'd get "New Users" only if those users were created yesterday at 19:07 (and it's not likely to happen very often, right?).

    In Oracle, you'd use the TRUNC function with the sysdate to obtain only date: TRUNC(SYSDATE). Now your IF might look as follows:
    Code:
    IF (Created == 'TRUNC(SYSDATE) - 1') {'New User'} ELSE {'Existing User'}
    Please, keep in mind that I have no knowledge about the HPS and this might not work either.

  7. #7
    Join Date
    Apr 2006
    Posts
    5
    Yes Hyperion Performance Suite.

    getting an error missing; before statement

    using both

    IF (Created == 'TRUNC(SYSDATE) - 1') {'New User'} ELSE {'Existing User'}

    IF (Created == TRUNC(SYSDATE) - 1) {'New User'} ELSE {'Existing User'}

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something's missing? A semi-colon?

    I've already said that I don't know HPS's syntax, but - shouldn't IF have THEN and END IF somewhere? Could you check the syntax? For example
    Code:
    IF (Created == 'TRUNC(SYSDATE) - 1')  THEN
       {'New User'};
    ELSE 
       {'Existing User'};
    END IF;

Posting Permissions

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