Crystal: Getting Select Expert to Match the Programming

Hello:

Below is the programming that the attached Crystal report is based on.  Also below is the Select Expert programming that I have tried and failed at.  I need to get the Select Expert to match the "where" of the programming.  Please help.

FYI ME97707.ME_User_Defined means "Editor".

Thanks!

TBSupport



ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                     and ME97708.ME_User_Defined = ''
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined = ''
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE = '01/01/1900'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97708.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE = '01/01/1900'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'
                      UNION  ALL
                      SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                     and ME97708.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE = '01/01/1900'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97708.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE = '01/01/1900'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'


{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status}
or
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status} and
{CARBON.CloseDate} = {?Close Date} and
isnull({CARBON.CloseDate})
or
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = date(1900,1,1) and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status}
or
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = date(1900,1,1) and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status} and
{CARBON.CloseDate} = {?Close Date} and
isnull({CARBON.CloseDate})
and
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor} = '' and
{CARBON.Status} = {?Status}
and
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = '' and
{CARBON.Status} = {?Status} and
{CARBON.CloseDate} = {?Close Date} and
isnull({CARBON.CloseDate})
and
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = date(1900,1,1) and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status}
and
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = date(1900,1,1) and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status} and
{CARBON.CloseDate} = {?Close Date} and
isnull({CARBON.CloseDate})
CARBON-COMMISSION.rpt
LVL 1
TBSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TBSupportAuthor Commented:
Oh, and one thing that I need help with is that I don't know whether to tell users to enter "blanks" in the parameters for things like no editors or null document dates.

TBSupport
0
TBSupportAuthor Commented:
Actually, I had to revise the programming slightly:

SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                     and ME97708.ME_User_Defined = ''
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined = ''
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE = '01/01/1900'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                     and ME97708.ME_User_Defined = ''
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE = '01/01/1900'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined = ''
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'
                      UNION  ALL
                      SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                     and ME97708.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE = '01/01/1900'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97708.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE = '01/01/1900'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'
0
mlmccCommented:
What issue are you having?

Where are you getting the script for the SQL?  Is it a view in the database or stored procedure?

I can see one issue with the select expert.  The closed date cannot be NULL and match the date that should be an OR inside ( ).  Also you must test for NULL first.  When Crystal encounter an evaluation involving a NULL value, unless it is a test for NULL, Crystal will halt evaluation of the formula and return with indeterminate results.

(
 isnull({CARBON.CloseDate})
OR
{CARBON.CloseDate} = {?Close Date}
)


Also the last half of the select should be ORs between the major parts.

Since STATUS is based on the closed date, it doesn't make sense to test both fields.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

James0628Commented:
As mlmcc said:
 You need to test a field for nulls first.
 And use OR with the null test (since the date field can't be null _and_ match your parameter), which also means adding some () around the date tests.
 And you have AND between the later groups of tests, instead of OR.

 I would also put () around the groups of tests.  It may not be necessary, but I'd rather have the tests explicitly grouped, and make it obvious when you look at the formula.  For example:
(
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status}
)
or
(
{CARBON.Project} = {?Job} and
 <etc., etc.>

 But, on top of that, your tests just don't make sense.  For example, the first two groups of tests (with the changes mentioned above) are:
(
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status}
)
or
(
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status} and
(IsNull ({CARBON.CloseDate}) or {CARBON.CloseDate} = {?Close Date})
)

 The second group of tests, where you check the date, accomplishes nothing.  You check Project, DocumentDate, Editor and Status in both groups of tests.  If they match the parameters, the first group of tests will be true, and the record will be included.  CR never gets to the second group of tests (or any of the others), so it doesn't matter what's in CloseDate.

 If you don't care about CloseDate, just use the first group of tests, and get rid of the second group.
 If you care about CloseDate, get rid of the first group of tests, and just use the second group.

 Same thing for your other pairs of groups of tests.

 Also, your last two groups of tests seem to be a repeat of two earlier groups.  I suspect that you meant to include {CARBON.Editor}  = '' in those last 2 groups.

 Finally, I think all of those groups could be combined into one set of tests, but I want to see your response to my other comments before I try consolidating them.

 James
0
TBSupportAuthor Commented:
Below is some revised programming, followed by my Select Expert.  Please let me know, if the Select Expert does not look right.

Thanks!

TBSupport



SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                     and ME97708.ME_User_Defined = ''
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined = ''
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE = '01/01/1900'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                     and ME97708.ME_User_Defined = ''
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE = '01/01/1900'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined = ''
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'
UNION  ALL
                      SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                     and ME97708.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE = '01/01/1900'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97708.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE = '01/01/1900'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_User_Defined BETWEEN 'ABR' AND 'YCH'
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'




//testing on 08/14/2015:
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status}
or
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status} and
{CARBON.CloseDate} = {?Close Date} and
isnull({CARBON.CloseDate})
or
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = date(1900,1,1) and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status}
or
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = date(1900,1,1) and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status} and
{CARBON.CloseDate} = {?Close Date} and
isnull({CARBON.CloseDate})
or
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor} = '' and
{CARBON.Status} = {?Status}
or
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = '' and
{CARBON.Status} = {?Status} and
{CARBON.CloseDate} = {?Close Date} and
isnull({CARBON.CloseDate})
or
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = date(1900,1,1) and
{CARBON.Editor}  = '' and
{CARBON.Status} = {?Status}
or
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = date(1900,1,1) and
{CARBON.Editor}  = '' and
{CARBON.Status} = {?Status} and
{CARBON.CloseDate} = {?Close Date} and
isnull({CARBON.CloseDate})
0
James0628Commented:
Did you see my post?  Just checking, since you posted not long after me (and your record selection formula still has some problems that I (and mlmcc) covered).

 James
0
mlmccCommented:
Why do you want the select expert to match the SQL?  Are ou planning to remove the filtering from the SQL?

mlmcc
0
TBSupportAuthor Commented:
Hi mlmcc:

I thought that the Select Expert criteria needed to match SQL, or at least the parmeter fields, in order to give precise return of data when the report is refreshed.  Is that incorrect?

TBSupport
0
mlmccCommented:
No.  If you do all the filtering in the SQL, you don't need anything in the select expert.  The select expert is there to allow you to further filter the data or for those who can't build a query or stored procedure in the database to filter the data they selected through tables.

mlmcc
0
James0628Commented:
What do you mean by "match the SQL"?

 You don't need to do the _same_ filtering in both places (for example, in a view, and in the record selection formula).  But, in your case, you can't do all of the filtering in the SQL.  You have some parameters in the report, and will need to use those in the record selection formula.  If you were using a CR Command, you could use some parameters in that, but not multi-value parameters (assuming that you're still using CR 10).

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

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.