How to modify SQL

system · September 22, 2009, 3:23pm

Hi
I would like to modify Crystal SQL. How to do this? Actually Crystal SQL having DISTINCT but I do not want DISTINCT. I am using Crystal XI and report is generating from universe.

Philip (BOB member since 2006-03-02)

system · September 22, 2009, 6:17pm

When using a universe, you lose your ability to directly change the SQL in CR command - you will need to make changes to your universe so that it correctly derives the SQL for you. So if your distinct is in a derived table (for example), you will need to update that derived table in the universe. If however, you simply want to include duplicate rows in your SQL that has been correctly derived from the universe, then go to the database expert and edit command. On the screen in the lower left, click the properties box and then select ‘Yes’ you do want duplicate rows. This will get rid of the DISTINCT.

steve (BOB member since 2009-01-16)

system · September 22, 2009, 7:33pm

Thanks. But I do not see Properties Box under Edit command.

Philip (BOB member since 2006-03-02)

system · September 29, 2009, 6:59pm

This is killing me. Anyway to rid off DISTINCT?

Philip (BOB member since 2006-03-02)

system · September 29, 2009, 11:18pm

You said that you’re using a Universe as datasource. Go Edit Command, it will launch the Query Panel. There should be a Properties box on the lower left hand side of the Query Panel Window, click that and you should see – “Data - Your Query may return duplicate rows. Do you want to retrieve duplicate rows in the final result set?” Select “Yes”. This will effectively remove the “Distinct” from your query.

AnthonyJ (BOB member since 2006-06-30)

system · September 30, 2009, 12:33am

I went to Crystal Report/Data Expert/Edit Command it will opening Query Panel with SQL, Union icon. But I do not see any properties box left hand corner of Query Panel. I am using Crystal XI. If possible could you please send me screen shot for query panel?

Philip (BOB member since 2006-03-02)

system · September 30, 2009, 3:34am

I don’t know if the properties tab exists in CR XI, but it does in CR XIR2 and 2008 versions. In any case, I am attaching a screenshot of the query panel in 2008 version using the eFashion universe as demo. Notice that the lower left hand has the properties tab I’m talking about.

AnthonyJ (BOB member since 2006-06-30)

system · September 30, 2009, 9:22am

Thanks Anthony. I think properties tab is missing on XI.

Philip (BOB member since 2006-03-02)