Emulate a Crystal Report using SQL Query.
I have a Crystal report with one field based on a Subreport, in the subreport I do a count and only pull that COUNT into the main report, replacing the whole report with SQL is straightforward, but I'm questioning my approach to replacing the subreport part with a subquery as the results I got this far are sketchy. How would you approach it?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PortletPaul
ah yes, of course, window functions are are damn fine choice
ASKER
To help explain better I'm including this graphic, I can select & manipulate info from the "DeliveryTable" & "CustomerInfoTable" and what have you, but I want to embed the total number of Orders by counting them and by Grouping On the CustomerID from the OrderMasterTable, then plug that number in my query of the other tables.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Does this have anything to do with Crystal Reports other than you are trying to match the results of an existing report?
Do you want a query you can use as a Command in Crystal?
mlmcc
Do you want a query you can use as a Command in Crystal?
mlmcc
ASKER
No mlmcc, my crystal runs fine, except it's far reaching and takes long time to finish running, that's why I opted to base it on a sql query.
ASKER
Many thanks experts, the collective input from both was valuable even if I didn't use either of your solutions for this exact project, I, however used an old script you slightwv helped me on, and it's goes as follows;
With
A As(
select column A, column B, from table X, where X = Y, Group By XX...),
B As(
select column C, column D from table Y, where N like 'abc123"),
Select
A.A,
A.B,
B.C,
B.D
From A
Inner Join B on A.A = B.C
........
Many thanks for your help.
With
A As(
select column A, column B, from table X, where X = Y, Group By XX...),
B As(
select column C, column D from table Y, where N like 'abc123"),
Select
A.A,
A.B,
B.C,
B.D
From A
Inner Join B on A.A = B.C
........
Many thanks for your help.