Crystal reports query

HI All,

I have a query that i have to run on a crystal reports.  I am getting a wrong alias name.  How can i fix this on crystal report.

WITH TaskHierarchy (name, taskid,parentid,levelnumber,creator,userparameter2,usercomment) AS
(
   -- Base case
   SELECT
      name, taskid,parentid, 1 as levelnumber, creator,userparameter2,usercomment
   FROM task
   WHERE parentid IS NULL

   UNION ALL

   -- Recursive step
   SELECT
     e.name, e.taskid,e.parentid
      eh.levelnumber + 1 AS levelnumber
, e.creator,e.userparameter2,e.usercomment
   FROM task e
      INNER JOIN TaskHierarchy eh ON
         e.parentid = eh.taskID
)

SELECT *
FROM TaskHierarchy
ORDER BY levelnumber
romeiovasuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Put an alias on the first part

WITH TaskHierarchy (name, taskid,parentid,levelnumber,creator,userparameter2,usercomment) AS
 (
    -- Base case
    SELECT
       xx.name,  xx.taskid,parentid, 1 as levelnumber,  xx.creator, xx.userparameter2, xx.usercomment
    FROM task  xx
    WHERE parentid IS NULL

    UNION ALL ...

mlmcc
romeiovasuAuthor Commented:
can you explain me for further please, i couldnt get what you were trying to say.
mlmccCommented:
The first part of your query uses the table TASK without an alias.  Add an alias to that select statement as I showed
Full query with the change

WITH TaskHierarchy (name, taskid,parentid,levelnumber,creator,userparameter2,usercomment) AS
  (
     -- Base case
     SELECT
        xx.name,  xx.taskid,parentid, 1 as levelnumber,  xx.creator, xx.userparameter2, xx.usercomment
     FROM task  xx
     WHERE parentid IS NULL

     UNION ALL

    -- Recursive step
    SELECT
      e.name, e.taskid,e.parentid
       eh.levelnumber + 1 AS levelnumber
 , e.creator,e.userparameter2,e.usercomment
    FROM task e
       INNER JOIN TaskHierarchy eh ON
          e.parentid = eh.taskID
 )

 SELECT *
 FROM TaskHierarchy
 ORDER BY levelnumber 

Open in new window


mlmcc
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

romeiovasuAuthor Commented:
this is the error i am getting.

Error:
Error while refreshing the data provider.

Cause of Error
ORA-32033: unsupported column aliasing
mlmccCommented:
Missing xx on parent id

WITH TaskHierarchy (name, taskid,parentid,levelnumber,creator,userparameter2,usercomment) AS
  (
     -- Base case
     SELECT
        xx.name,  xx.taskid, xx.parentid, 1 as levelnumber,  xx.creator, xx.userparameter2, xx.usercomment
     FROM task  xx
     WHERE xx.parentid IS NULL

     UNION ALL

    -- Recursive step
    SELECT
      e.name, e.taskid,e.parentid
       eh.levelnumber + 1 AS levelnumber
 , e.creator,e.userparameter2,e.usercomment
    FROM task e
       INNER JOIN TaskHierarchy eh ON
          e.parentid = eh.taskID
 )

 SELECT *
 FROM TaskHierarchy
 ORDER BY levelnumber 

Open in new window


mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
romeiovasuAuthor Commented:
ORA-32033: unsupported column aliasing
mlmccCommented:
I don't see it right now.  I'll fire up my oracle at home and see if I can find the issue.

Task has
name, taskid, parentid, creator, userparameter2, usercomment

TaskHierarchy has
taskID, levelnumber

There may be other columns but those are the ones you need.

mlmcc
romeiovasuAuthor Commented:
yes.  even i dont understand why that is happening.
romeiovasuAuthor Commented:
it was actually my fault, i was trying sql query on a oracle database.  Once  i change the query to oracle
select parentid parent, id child
   from table1
connect by prior id = parentid
  start with parentid = 1

it worked.  

Thank you so much for helping me out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.