SQL Syntax?

[joeg23]

Hi,

Using CR xi with an Excel 8.0 ADO Database connection.

I am trying to write my first CR SQL Statement and can't figure out the proper syntax to connect to a table. Can someone please help me...i've searched everywhere and can't figure it out.

I'm trying something like

Select '<field name> from Database explorer' from ???? (I don't know how to put in the table name correctly - I've tried many formats that don't work)

Thoughts?

 

[Turkbear]

Hi,
Why write your own?

Connect to the Datasource, select the table and place the desired fields in your report - CR will write the Sql needed..( You can see it by using the Database..Show Sql menu choice.)







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[joeg23]

Hi,

I need to write my own SQL statement because I have 3 distinct columns with the same data elements. The only way I could find to combine them into one element on a report (to summarize) was to create a Command Object through a SQL statement...then I could Union the elements together into one.

Thx,
Joe

 

[Turkbear]

Hi,
It is possible that SQL will not work against an Excel table ( not sure ).

In general it would be
Select f1, f2 from table1
union
Select f1,f2 from table2

But, with Excel ( which is not a standard database), I am not sure it would work..

If you have Access, put the data there...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[joeg23]

Thanks. Could I also ask what the proper syntax is???

File Name: C:\<file path>\Working Copy.xls
Table Name: TestDB

Here's what I've tried so far:

Select * From "C:\<file path>\Working Copy.xls"."TestDB"

Select * From 'C:\<file path>\Working Copy.xls'.'TestDB'

Select * From 'Working Copy.xls'.'TestDB'

Select * From "Working Copy.xls"."TestDB"

Could you tell me:

a) Are any of these the proper Syntax for Excel?
a) Are any of these the proper Syntax for Access?

Sorry if this is really basic, I know SQL, but not the specifics in CR XI (which i'm new to)

Thanks!
Joe

 

[Turkbear]

Hi,
No..First connect to the Excel spreadsheet using CR's Access/Excel DAO ( or the ADO) connection, then use the Add Command to write your SQL...What that would be is not clear..
Maybe just:
Select * from TestDB ( the connection handles the location and asumes a data area defined as TestDB)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Or use the ODBC connectivity.

And Turk is correct, for each worksheet within that Crystal sees as a valid datasource, you cabn do a select * from <datasource>.

At connect time using Add Command you'll see the names there.

-k