• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 72
  • Last Modified:

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?
0
Roberto Madro R.
Asked:
Roberto Madro R.
  • 3
  • 3
  • 2
  • +1
4 Solutions
 
PortletPaulfreelancerCommented:
The question is not specific enough to narrow down the myriad of choices, here's two...

1. use a subquery

select c1, c2, c3, sq.col1
from table1 t
inner join ( select id, count(*) col1 from sometable group by id) sq on t.id = sq.id

2. or a correlated subquery (less efficient)

select c1, c2, c3, ( select count(*) from sometable st where t.id = st.id) col1
from table1 t
0
 
slightwv (䄆 Netminder) Commented:
We did something similar with Crystal.  We pulled back the entire count on every row using the window function and ignored the value in the main report.

select c1, c2, c3, count(c3) over () total_count from sometable;

You can extend it if necessary using CASE.  You only want the 'Y' values?
select c1, c2, c3, count(case when c3 = 'Y' then 1 end) over () y_count from sometable;
0
 
PortletPaulfreelancerCommented:
ah yes, of course, window functions are are damn fine choice
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Roberto Madro R.Programmer AnalystAuthor Commented:
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.Tables
0
 
PortletPaulfreelancerCommented:
Then I suggest the subquery approach

select t.CustomerID , t.c2, t.c3, sq.numcust
from DeliveryTable t
inner join ( select CustomerID , count(*) numcust from OrderMasterTable group by CustomerID ) sq on t.CustomerID = sq.CustomerID
0
 
slightwv (䄆 Netminder) Commented:
Instead of a picture, can you simplify what you need into a small test case with data and expected results (as text, I can't copy and paste from a picture)?

Then we can provide tested code based on your test case.

Until we get a test case, you can use the window function to "group" as well.  It is called PARTITION.

select c1, c2, c3, count(c3) over (partition by customerid) total_count from sometable;
0
 
mlmccCommented:
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
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
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.
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now