Join Field Added to Select?

[Former Member]

Hello,

I'm have a report which joins three tables based on criteria passed in. One of the parameters is included in a WHERE clause to identify records in a specific date range. Crystal Report automatically adds the fields in the WHERE clause to my SELECT, causing duplicates to appear in the recordset. Is this expected? Is thre anyway around this?

I don't use the date field in the report at all, just in the SQL. I already tried the DISTINCT option. It removed some records but not all. Any suggestions would be greatly, greatly, appreciated!

[Former Member · Accepted solution]

"automatically adds the fields in the WHERE clause to my SELECT"????

Post your SQL query

Jyothi

[Former Member · Accepted solution]

Hi Jyothi,

Thanks for reply. I want to do something like the following:

SELECT DISTINCT NAME, ADDRESS FROM EMPLOYEE WHERE HIRE_DATE >= '20090101'

However, if I check the SQL query (Database -> Show SQL Query...) I get the following:

SELECT DISTINCT NAME, ADDRESS, HIRE_DATE FROM EMPLOYEE WHERE HIRE_DATE >= '20090101'

The hire date is automatically included in the SELECT statement, even though I only want the NAME and ADDRESS fields.

[Former Member · Accepted solution]

can you see Hire Date under database fields as well?

Which version of crystal you are using?

Jyothi

[Former Member · Accepted solution]

Yes I can see the field listed under Database Fields. However I do not want to since I don't use it in my report. I only use it in the WHERE clause to filter the data.

Crystal Version: 11.5.8.826

[Former Member · Accepted solution]

Are you using SQL command or added Employee table to the report?

Jyothi

[Former Member · Accepted solution]

Sorry for not being specific. I added the table to the report, did not use SQL command. Thank you for your time.

[Former Member · Accepted solution]

In that case try using SQL command with distinct , as posted above.

Regards,

Jyothi

[Former Member · Accepted solution]

I tried that, but then I ran into issues with parameters - different topic.

Does anyone why the fields used in the WHERE clauses gets added to the SELECT statement?

[Former Member · Accepted solution]

If you add table to report, Crystal will have all the fields from the table. where did you add "Where Clause", if you used table directly.

Whats the problem with parameters? If Hire date is a datetime type , you parameter should also be of same type and same format.

Its always better to use SQL command to tables.

Jyothi

[Former Member · Accepted solution]

I used the SELECT expert to define the WHERE clause.

I don't see the logic behind adding all the fields from the WHERE clause into the SELECT. Now I can't even use a GROUP BY to filter out the duplicates. Might be an issue with design, but I would think we could get around it by just removing the field in the WHERE clause from the SELECT portion of the statement.

As for the parameters, I tried to use a function ToText(, "yyyyMMdd") in the command SQL, but it didn't work.

[Former Member · Accepted solution]

Its a feature of crystal to add fields in SQL query..below is the description for SHOW SQL Query dialog.

"SELECT lists fields used in the report, including those in formulas, totals, sort, selection and ranges. FROM lists tables in the report followed by aliases. WHERE specifies field ranges and link between tables. ORDER BY is sort order"

you can't use totext in SQL command, but you can in select expert

Whats your database? whats the format of hire_date

Jyothi

[Former Member · Accepted solution]

I'm using an Oracle database.

I think you've helped me see a different approach, if I use a SQL command, I should be able to use Oracle functions to format the date parameter instead of using the Crystal functions right?

Hmm... This may be it... Thanks!

However, still don't understanding the reasoning to why the developers opted to automatically add the fields in the WHERE to the SELECT.

[Former Member · Accepted solution]

Hi,

Yes command is a better approach for records.

I think answer to your question why date field is being displayed in query is that--in reports we use select expert to display records like where clause e.g. in ur case where date field> value.

But for this,we have to select that field then only we can say field selected satisfies this condition(like < ,>, = ,between).

I think that is the reason.

Regards,

Misra P.