sub select? multi-unlinked-table-select 1

[pelajhia]

Well, I don't even know if this is called a sub-select, but hopefully you get the idea... I am trying to create a report using 3 tables that need to pull based on the same user-entered date, but they don't really need to be linked per-se as master to detail tables, or anything like that. In fact I can't figure out a legitimate way to link them for my current purposes.

I just want:
count of date_modified from tablex based on date parameter.
count of date_created from tabley based on date parameter.
count of date_created from tablez based on date parameter.

It seems like this should be easy to do. I saw an example of a sql query set up like so:

select a.datemod_cnt, b.datecreated_cnt,c.datecreated_cnt
(
select count(tablex.date_modified) datemod_cnt
from
tablex
where
tablex.date_modified=?date
)a,
(
select count(tabley.date_created) datecreated_cnt
from tabley
where
tabley.date_created=?date
)b,
(
select count(tablez.date_created) datecreated_cnt
from tablez
where tablez.date_created=?date
)c
order by created_by

Hopefully I have all the commas, etc., in the right places.

Is there some way to do something like this in 8.5 Standard? I also have 9advanced loaded on my pc, but have not loaded SQL Designer due to a conflict with 8.5. Is SQL Designer the only way to go on this besides perhaps a stored procedure? Also, I have used subreports to provide this sort of multi-unlinked-table-select, but I would like to avoid needless querying if possible.
Maybe I am making this way too complicated.
Thanks for any/all help!

 

[synapsevampire]

Hmmm, you might be able to cheat this with a tricky Union, but I wouldn't.

If you're using CR 9 you could use real SQL, the alternative in CR 8.5 and below is as you've discovered, subreports.

And don't use the CR SQL Designer, it's a dying technology.

I would suggest building Views or SP's on the database to promote reusability and maintenance for when you upgrade to CR 9.

-k

 

[pelajhia]

I don't prefer to use SQL Designer, I never have used it, but I could not see where in CR9 I am able to edit the sql myself. I went to the 'show sql query' where I am able to edit a little in 8.5, but I couldn't edit at all in 9. Where do I go to get my hands on the sql?
Thanks!!

 

[synapsevampire]

Use the Add Command in the Database Expert.

Here's a whitepaper describing it:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/Cr_database_menu.pdf

-k

 

[pelajhia]

Thank you so much! That is exactly the clue I needed. I could not find anything on crystal decisions when I searched yesterday using 'edit sql'. I finally concluded that I couldn't edit the sql w/o SQL Designer. Now I have created a report with my fun sql and it appears to be working.
It's not totally clear in the white paper, but with a little fiddling and looking at the screen shots they provide, I was able to make a new odbc connection and then the add command appeared. That's where I was able to copy and paste my sql; I am going to work on figuring out the parameters next, but that does not look too difficult.
THANK YOU!!THANK YOU!!THANK YOU!!