Convert this to Crystal Query or SQL Query to be read in Cyrstal

goretailpro
goretailpro used Ask the Experts™
on
I am trying to put this in SQL express field in Crystal 2008... How can I go about doing that formatting it correctly?
SELECT

    RV_DAYS.A_MONTH3,COUNT(*) AS T_XCOUNT,

    SUM(FACTHRODETAIL.TIME_QUANTITY) AS Hours,COUNT(DISTINCT RO_NO) AS ROs,COUNT(DISTINCT DIM_TECH.TECH) AS NumberofTECHs,
    DIM_STORE.fDescription,DIM_STORE.fCode

FROM

    DIM_DETAILTYPE,FACTHRODETAIL,DIM_STORE,RV_DAYS,DIM_TECH

WHERE

     FACTHRODETAIL.DETAILTYPE=DIM_DETAILTYPE.fId

    AND DIM_STORE.fId=FACTHRODETAIL.FSTOREID

    AND RV_DAYS.JUL_DATE=FACTHRODETAIL.RODATE

    AND DIM_TECH.SK_TECH=FACTHRODETAIL.SKTECH

-- AND DIM_STORE.fCode='002'

    AND DIM_DETAILTYPE.fType='Labors'

    AND (RV_DAYS.JUL_DATE>='2009/06/01') AND (RV_DAYS.JUL_DATE<='2009/06/30')

GROUP BY

  DIM_STORE.fDescription,DIM_STORE.fCode,RV_DAYS.A_MONTH3

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
are you creating a report with this query ?
if you r making a new report using this query:

 then you can add this query on "Add Command"

to accomplish this ..see below

create new report > report wizard > select create new connection >check odbc(rdo) or whatever u usually do for other reports > once you connect..you can see "ADD command" on top of table..see attached image

for sql expression field :
add-command.png

Commented:
you have to split your in order to put formula /sql expression field (i don't know whether it is available 2008) ...but you can achieve / get only one field from a formula..I believe your query needs above solution where you need not change your code if the code syntax same as back end database syntax ..if not and if u wanna convert

1. all sums can be done on the report sections :

  example display all fields in detail and the select field you wanna summarize and right click on that field > insert summary > choose sum/count> then goup/report level

2. all statements in where clause goes to filter
   menu> report> record> place all your where clause with crystal syntax..let me know if you want this

3. for groups.. you can create groups in crystal by group expert and insert summaries of corresponding fields..
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are you getting an error when you enter it into Crystal?

mlmcc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial