Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Query that will not allow parameters

I have a query with uses a Union Value - i want to add a a parameter to select the date range based on the Recontact but it wont work and displays no error jsut the read error line - can anyone help?

query is:

select

       q.ClgCode as JdtNum,

       q.Account as Account,

       q.debet as Debit,

       q.credit as Credit,

       q.U_IIS_prjPCode as Project,

       q.lastName as LineMemo,

       convert(varchar(max),datepart(YYYY,getdate())) + '-' + convert(varchar(max),datepart(MM,getdate())) + '-' + convert(varchar(max),datepart(DD,getdate())) as RefDate,

       q.lastName as Ref1,

       q.CalcMe as Ref2,

       q.ClgCode as Ref3Line,

       q.Recontact

from (

SELECT

       T1.[empID],

       T1.[lastName],

       T1.[firstName],

       T1.[jobTitle],

       T0.[ClgCode],

       T0.[CardCode],

       T0.[Recontact],

       T0.[Closed],

       T0.[DocNum],

       T0.[Details],

       T0.[BeginTime],

       T0.[ENDTime],

       T0.[Duration],

       T0.[U_IIS_prjAST],

       T0.[U_IIS_prjASD],

       T0.[U_IIS_prjAFD],

       T0.[U_IIS_prjAFT],

       T0.[U_IIS_prjPCode],

       T0.[U_IIS_prjItemC] as CalcMe,

       T0.[U_IIS_prjBUCost],

       T0.[U_IIS_prjAUCost],

       T0.[U_IIS_prjBCost],

       T0.[U_IIS_prjACost],

       T0.[U_IIS_prjSONum],

       T0.[U_IIS_prjSOlinep] as debet,

       0 as credit,

       T3.[ItmsGrpCod],

       T3.[ItmsGrpNam],

       T3.SaleCostAc as Account,

       T2.[ItemCode],

       T2.[ItemName],

       CASE WHEN T0.[Duration] > 0  THEN  T0.[U_IIS_prjSOlinep]/  T0.[Duration] ELSE T0.[U_IIS_prjSOlinep] END as 'Rate'

FROM OCLG T0 

INNER JOIN OHEM T1 ON T0.[AttendEmpl] = T1.[empID] and T1.[U_IIS_ISchedType] = 'Equipment'

INNER JOIN OITM T2 ON T1.[U_Linked_ItemCode]  = T2.[ItemCode]

INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod]

INNER JOIN OPRJ T4 ON T4.PrjCode = T0.[U_IIS_prjPCode]

LEFT JOIN JDT1 T5 ON T5.[Ref3Line] = convert(varchar(max),T0.ClgCode)

LEFT JOIN OJDT T6 ON T6.TransId = T5.TransId and t6.TransType = 30

WHERE T0.closed = 'N'

and (T4.U_BPCode <> 'C58030' or  T4.U_BPCode iS NULL)

and t6.TransId is null

UNION ALL

SELECT

       T1.[empID],

       T1.[lastName],

       T1.[firstName],

       T1.[jobTitle],

       T0.[ClgCode],

       T0.[CardCode],

       T0.[Recontact],

       T0.[Closed],

       T0.[DocNum],

       T0.[Details],

       T0.[BeginTime],

       T0.[ENDTime],

       T0.[Duration],

       T0.[U_IIS_prjAST],

       T0.[U_IIS_prjASD],

       T0.[U_IIS_prjAFD],

       T0.[U_IIS_prjAFT],

       T0.[U_IIS_prjPCode],

       T2.U_IIS_Calcme as calcme,

       T0.[U_IIS_prjBUCost],

       T0.[U_IIS_prjAUCost],

       T0.[U_IIS_prjBCost],

       T0.[U_IIS_prjACost],

       T0.[U_IIS_prjSONum],

       0 as debit,

       T0.[U_IIS_prjSOlinep] as credit,

       T3.[ItmsGrpCod],

       T3.[ItmsGrpNam],

       T3.RevenuesAc as Account,

       T2.[ItemCode],

       T2.[ItemName],

       CASE WHEN T0.[Duration] > 0  THEN  T0.[U_IIS_prjSOlinep]/  T0.[Duration] ELSE T0.[U_IIS_prjSOlinep] END as 'Rate'

FROM OCLG T0 

INNER JOIN OHEM T1 ON T0.[AttendEmpl] = T1.[empID] and T1.[U_IIS_ISchedType] = 'Equipment'

INNER JOIN OITM T2 ON T1.[U_Linked_ItemCode]  = T2.[ItemCode]

INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod]

INNER JOIN OPRJ T4 ON T4.PrjCode = T0.[U_IIS_prjPCode]

LEFT JOIN JDT1 T5 ON T5.[Ref3Line] = convert(varchar(max),T0.ClgCode)

LEFT JOIN OJDT T6 ON T6.TransId = T5.TransId and t6.TransType = 30

WHERE T0.closed = 'N'

and (T4.U_BPCode <> 'C58030' or  T4.U_BPCode iS NULL)

and t6.TransId is null

) as q

where q.debet > 0 or q.credit > 0 and q.Recontact >= [%0] and q.Recontact <= [%1]

order by q.ClgCode, Account

Former Member
replied

You MUST create the parameters in the Command Editor - it will not "see" the parameters that have been created in the report.  You will then add them to the query using the Crystal {?Parameter} syntax.  If the parameter is a string, you need to enclose it in single quotes.

The Command Editor only allows you to set a very limited number of properties.  However, you can edit the properties in the report after it's been created in the Command Editor to do thing like make it dynamic.  NOTE:  Parameters used in a command MUST have a value - they cannot be null.

-Dell

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question