dont know how to make the CR sql use nested selects..

here's a SQL:

SELECT acc.client_number, lin.client_branch, reg.*
FROM CAS_CLIENT_ACCOUNT acc,
CIS_CLIENT_DETAILS det,
(SELECT cht_client_branch.index_field, cht_client_branch.client_branch,
cht_client_branch.region, cht_branch_region.branch_region
FROM cht_branch_region, cht_client_branch
WHERE cht_branch_region.index_field = cht_client_branch.region
AND cht_branch_region.institution_number = cht_client_branch.institution_number
AND cht_branch_region.institution_number = '00000005') reg,
(SELECT MAX(RECORD_DATE), client_number, institution_number, client_branch
FROM CIS_CLIENT_LINKS
WHERE institution_number = '00000005'
GROUP BY client_number, institution_number, client_branch) lin

WHERE
--links section
acc.client_number = det.client_number
AND acc.institution_number = det.institution_number
AND acc.client_number = lin.client_number
AND acc.institution_number = lin.institution_number
AND lin.client_branch = reg.index_field

--restriction section
AND acc.institution_number = '00000005'
AND acc.acct_status = '001'
AND acc.record_type = '002'
AND det.record_type = '002'

as you can see, it uses two nested selects. I'd like to know how i would get this into crystal; in the old versions you could write into the "view sql query" window. in CR10+ this isnt possible

if it's covered in the help manual for CR, please point me there to save yourself some typing :)I've been using database views for this purpose. I've also seen a screen were you could type your own SQL, this could however had been a Beta version (I was not able to find the window today). The great advantage to SQL-editor in version 8.5 is that you could use Crystal Parameters in your syntax and actually limit the value set returned to your computer.

- Jukka|||yeah.. i dont know why they make the sql view as read only in cr10+, but im looking at making a view or stored procedure on the DB server for this report... tsk|||Hi,

I tried the following i made a report selecting a single field from a table called station. Then I included a second table (machinegroup) to the report without any joins to the first table. Finally i created an SQL Expression Field that used the machinegroup table, see code below:

(select "MACHINEGROUP"."MACHINE_GROUP_NAME" from "MACHINEGROUP" "MACHINEGROUP" where "MACHINEGROUP"."MACHINE_GROUP" = "STATION"."MACHINE_GROUP")

Then i got the following SQL generated by crystal

SELECT "STATION"."STATION_CODE"
FROM "STATION" "STATION"

SELECT ((select "MACHINEGROUP"."MACHINE_GROUP_NAME" from "MACHINEGROUP" "MACHINEGROUP" where "MACHINEGROUP"."MACHINE_GROUP" = "STATION"."MACHINE_GROUP"))
FROM "MACHINEGROUP" "MACHINEGROUP", "STATION" "STATION"

This could be a workaround for your problem

- jukka|||I finally managed to find the functionality enabling free SQL-queries. Select from the Database Expert 'Add Command' from the navigation tree to our database. This opens a window where you can type your SQL command and define Crystal parameters. This is supirior to the SQL Editor found in version 10 since parameter values are actually passed to the SQL command.

- Jukka