Add Command/Field Explorer

[richiew13]

I have a crystaluser.com training guide. In the lesson about using the Add Command prompt, it tells me to access the sample database with a simple SQL statement in the Add Command function.

In my guide, after I add the Command, it shows 4 fields with their original names as the titles. ("Customer Name", "Region", etc.)

But when I run the exact same SQL command as in the book, my 4 fields are titled "Expr1000", "Expr1001" etc.

Why the difference?

I am using CR XI. The book is supposedly using CR v.9 if that makes a difference.

 

[synapsevampire]

Rather than talk about it, post what you used as the add command, and the type of database.

An Add Command is the same as a SQL Query.

-k

 

[lbass]

I think you will get the "expr 1000" when you create a summary within the command without naming it, so you might try something like:

Select max(table.`date`) as maxdate, table.`customer` //etc.

-LB

 

[richiew13]

I used this SQL query in the Add Command dialogue:

SELECT "Customer Name", "Region", "Country", "Last Year's Sales"
FROM Customer


I did this on the Xtreme Database example that came with Crystal XI.

 

[synapsevampire]

Perhaps the "Last Year's Sales" is illegal.

As LB states, alias the field:

SELECT "Customer Name", "Region", "Country", "Last Year's Sales" Sales
FROM Customer

My Customer table doesn't have a last tears sales so I can't test.

-k

 

[lbass]

If you're using the Xtreme database, I think the syntax would be:

SELECT Customer.`Customer Name`, Customer.`Region`, Customer.`Country`, Customer.`Last Year's Sales`
FROM `Customer` Customer

-LB

 

[synapsevampire]

You can use the "" in a Command object for Access as well, but LB is correct, that is how Crystal builds it out, try it.

-k

 

[richiew13]

lbass, thanks. I copied your version and pasted it and it worked. I know very little about SQL queries.

It looks like your version has two changes from mine (which came straight from my book). You added: Customer. in front of each field, and you used " instead of '.

What does each of those things do?

Do you think my textbook just has the wrong code in there, or has something changed between CR-9 and CR-XI?

 

[lbass]

I'm not an expert in this area either, but the "Customer" in front of each field refers to the table name (actually the alias table name as indicated in the "From" line--the table name is in `` and the alias immediately follows). The marks aren't actually apostrophes--I don't know what they are called, but the mark is found on a key in the upper left of (mine, anyway) the numeric row.

The punctuation and syntax that is appropriate in the SQL query varies based on your datasource and your connectivity. If you are not sure, it might help to look at a report using the same datasource->database->show SQL query. Observe the punctuation and syntax there, and then use the same in the command object.

-LB

 

[synapsevampire]

The character is a single open quote.

I'm surprised that the training guide had it wrong though, there may be something else amiss here.

-k