asked on

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

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

ASKER CERTIFIED SOLUTION

Shiv

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shiv

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 McCracken

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

mlmcc