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?
SELECT a.empl_id, a.balance_date, lp.labor_level_2,sum(a.catgry_13)/60, c.labor_level_2
FROM daily_interval a,
WHERE a.empl_id = b.empl_id and
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.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;
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