PLEASE, how to use simple query !!!?

[Slingky]

help !

i'm reporting off an old DOS ACCPAC database.
it doesn't support inner join...

so i have to use only a.field1 = b.field2... blabla...

how to force simple query ???

 

[synapsevampire]

What does "force simple query" supposed to mean?

An inner join is

What does force a simple query mean?

Are you using a bundled Crystal product within ACCPAC?

a.field1 = b.field2 is an inner join.

Try posting technical information using real technical terms.

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[Slingky]

ok, sorry.

if i link two tables using standard equal join type.

i get "Unexpected extra token: INNER"

so if i change manually the sql query from


SELECT "OE61BHED"."Order #", "AR61ACST"."Name"
FROM "OE61BHED" "OE61BHED" INNER JOIN "AR61ACST" "AR61ACST" ON "OE61BHED"."Cust #"="AR61ACST"."Cust #"

to


SELECT "OE61BHED"."Order #", "AR61ACST"."Name"
FROM "OE61BHED" "OE61BHED", "AR61ACST" "AR61ACST"
WHERE "OE61BHED"."Cust #"="AR61ACST"."Cust #"


all is ok...

so, how to force Crystal Reports to use the second syntax.
plus, i'm in crystal adv dev 10, manual editing of sql is not allowed. i have to created "command" statement and that change all the way how to call fields...

 

[kskid]

This is Crystal Report Basics

Create a new report using Report Expert
[ol]
[li]Under the data tab, Add "OE61BHED" and "AR61ACST" and click next.[/li]
[li]Under the Linking Tab, link "OE61BHED"."Cust #" to "AR61ACST"."Cust #". Click next[/li]
[li]Under the data tab, simply click on the data fields you want to display[/li]
[li]Click finish since this is the extent of what you have shown on your post[/li]
[/ol]

 

[synapsevampire]

What happens if you paste into the Add Command the following:

SELECT "OE61BHED"."Order #", "AR61ACST"."Name"
FROM "OE61BHED" "OE61BHED", "AR61ACST" "AR61ACST"
WHERE "OE61BHED"."Cust #"="AR61ACST"."Cust #"

Perhaps there's another type of connectivity available, were we to learn the database and connectivity type.

-k

 

[kskid]

Forgot one thing...

To verify, click on Database->Show SQL Query and you should see the results of the Report Expert that I just sent you through. It should look very similar to your SQL

SELECT "OE61BHED"."Order #", "AR61ACST"."Name"
FROM "OE61BHED" "OE61BHED", "AR61ACST" "AR61ACST"
WHERE "OE61BHED"."Cust #"="AR61ACST"."Cust #

 

[Slingky]

not working guys...
same thing.

me too i forgot to mention i had to use "OLE DB (ADO)"
cause of a syntax problem.
so in new connection, i took

-> Microsoft OLE DB Provider for ODBC Drivers

i just want crystal reports 10 to generate to syntax without the word INNER !!!

cause INNER syntax... is not supported by the drivers.

 

[Slingky]

wichitakid, syntax created is this:

SELECT "OE61BHED"."Order #", "AR61ACST"."Name"
FROM "OE61BHED" "OE61BHED" INNER JOIN "AR61ACST" "AR61ACST" ON "OE61BHED"."Cust #"="AR61ACST"."Cust #"


How to tell crystal to only use = syntax...

(not something like odbcregs where we have to join a registry value to tell crystal to write a particular syntax for a particular driver ?)

 

[kskid]

Since I don't have CR10, I am totally out of my environment on what you are doing or where it resides but are you trying to create a stored procedure or placing the SQL in a VB statement?

If so, you might get better help in Crystal Reports 2 Data Access forum.

 

[Slingky]

no no, i just create standard rpt files.

at this point i'm already blocked.
so for now, i create the report using the command statement so i write my sql statement directly...but it's a bit of a pain compared to the automatism that i found usually in crystal over other databases...

i was just hoping for a solution to get std queries...

thanks

 

[synapsevampire]

Again:

What happens if you paste into the Add Command the following:

SELECT "OE61BHED"."Order #", "AR61ACST"."Name"
FROM "OE61BHED" "OE61BHED", "AR61ACST" "AR61ACST"
WHERE "OE61BHED"."Cust #"="AR61ACST"."Cust #"

-k

 

[Slingky]

it works...it's what i said...

i HAVE to use the "command" to make it work.

but how to force crystal to use that KIND of syntax each time i add a table to the report and link it to another one ?

 

[synapsevampire]

I guess "i have to created "command" statement and that change all the way how to call fields..." was supposed to be interpreted as that?

I don't think you can force Crystal to do that.

Be thankful that it works at all, it sounds like you're reporting off of something around 10 years old...

-k

 

[Slingky]

yeah....i was trying to avoid buying the odbc link upgrade!

thanks for all your support.

i really appreciate.

 

[kskid]

I've been working in systems a loooooong time and have worked and/or heard of many databases but ACCPAC is a new one to me.

 

[Slingky]

ACCPAC is an accounting system.

and they provide an ODBC link to access their DOS accounting software files... ouah!!!!!!

check here;

http://www.accpac.com/

in Canada, it's the 1st accounting solution for small and medium business since many years...

and it costs a lot to upgrade to windows, it's why some company wait before changing something which is working goog.