Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    houston
    Posts
    22

    Unanswered: Self Join Creates Redundant Data

    hi guys. i've got a bit of an anomole. i'm calling a stored procedure within a package from Oracle.
    the sql statement uses a self join, since one of the tables is really just a look up table.

    Crystal treats these stored procedures or in the case of oracle just like a table and takes what the reference cursor gives it. i've done this several times, this is just the first time i used a self-join in the query.

    crystal seems to be treating this like redundant info rather than two separate columns and just seems to be re-displaying that first column coming in.
    the colum is labor_level_2 and this runs perfectly from sql+, toad or anywhere else i make the call. i dont know where i would change anything in the query (ref_cursor) since it has to be a self join. am i asking too much from crystal?
    thanks
    rik

    SELECT a.empl_id, a.balance_date, lp.labor_level_2,sum(a.catgry_13)/60, c.labor_level_2
    FROM daily_interval a,
    daily_labor b,
    labor_profile c,
    labor_profile lp,
    empl_profile e,
    empl_labor el

    WHERE a.empl_id = b.empl_id and
    a.empl_id=e.empl_id and
    e.empl_id=el.empl_id
    and a.balance_date = b.balance_date
    and a.start_time = b.start_time
    and a.balance_date BETWEEN i_DateFrom and i_DateTo
    and a.catgry_13 <> 0
    and b.labor_id = c.LABOR_ID
    and el.labor_id=lp.labor_id
    and el.effective_start = (SELECT max (x.effective_start) from
    empl_labor x WHERE el.empl_id=x.empl_id)
    and e.org_lvl_4= i_org_lvl_4
    GROUP BY a.empl_id, a.balance_date,lp.labor_level_2, c.labor_level_2;

  2. #2
    Join Date
    Oct 2003
    Location
    houston
    Posts
    22

    redundant data

    Once again, i figured this one out myself. i have to assume that not many are using crystal as i am - Calling a ref-cursor from oracle.
    anyway here goes.
    Crystal Treats the sTored procedure like a table, even though in reality its a select statement into a ref cursor datatype.
    the only problem i had was if i used a self join, or multiple joins from other tables with columns of the same name.
    whereas in Oracle i would use an alias (a.myColumn, b.myColumn) when returning this to Crystal, it puts its on alias on the elements. it uses the name of the procedure, then dot, then the column name.
    so, when it sees proc.Col1 & proc.Col1 (same name) it's overwriting that second column with data from the first.
    i received several wise bits of advice from programmers much more advanced than me. Create a temp table that way you can name your columns differently.
    lotsa work to do that.
    well, i stumbled onto the most simple thing this am.
    basically all i do is a.myColumn, b.myColumn as NewName.
    and guess what?
    Crystal complies implicitly.
    nuff said. its the simple simple things in life
    thanks
    rik

Posting Permissions

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