Left Join Parameter

[Former Member]

Hello.

I'd like to make a report from a query I've already made, the problem is with the parameters, I know how to put parameters in Crystal but not the parameters inside a left join clause, here is the query:

SELECT DISTINCT T0.CardCode,

                                        T0.U_CR CR,

                                        T0.Name Tienda,

                                        T0.U_CIUDAD Ciudad,

                                        T0.Address Direccion,

                                        T1.U_fecha

                                        T1.U_hora

                                        T1.U_Proveedor

                                        ISNULL(T4.firstName,'') + ' ' + ISNULL(t4.middleName,'') + ' ' + ISNULL(t4.lastName,'') Tecnico,

                                        T5.descript Zona,

                                        isnull(T1.callID,0) AS 'ID llamada'

FROM OCPR T0

LEFT JOIN OSCL T1 ON T0.CntctCode = T1.contctCode

AND T1.U_Fecha >= [%0] AND T1.U_Fecha <= [%1]

AND T1.origin = 3 AND T1.callType = 36 AND T1.problemTyp = 5

LEFT JOIN OINS T2 ON T1.insID = T2.insID

LEFT JOIN CRD1 T3 ON T3.CardCode = T0.CardCode

LEFT JOIN OTER T5 ON T5.territryID = T2.territory

LEFT JOIN OHEM T4 ON T4.empID = T1.technician

Where

T0.CardCode = '[%2]' AND

T0.U_CR IS NOT NULL

ORDER BY

isnull(T1.callID,0)

This is the query that I use in SAP B1, how can I add the T1.U_Fecha parameters to Crystal, because If I use them in the Where clause its gonna mess the results.

Thanks in advance hope you can help me.

[abhilash_kumar]

Hi,

This part in the Join portion has to be in the where clause:

AND T1.U_Fecha >= [%0] AND T1.U_Fecha <= [%1]

AND T1.origin = 3 AND T1.callType = 36 AND T1.problemTyp = 5

After you add the query through the Add Command window, just create two parameters. You can call it Start and End. I'm assuming the parameters are of string type. You can the add them to the query placing the cursor after ">=" and double-clicking the parameter name (Start) on the top-right and you would get this:

AND T1.U_Fecha >= {?Start} AND T1.U_Fecha <= {?End}

Hope this helps!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Firstable, thanks for your help, but I think you misunderstood my request I know how to add parameters in the Where clause, what I want to do is to add the parameters in the left join clause, the query gives me completely different results if I place them in the where clause, hope you can help me.

Regards.

[abhilash_kumar]

Hi,

In the SQL Query which is the parameter?

- Abhilash

[Former Member]

Exactly in this part:

LEFT JOIN OSCL T1 ON T0.CntctCode = T1.contctCode

AND T1.U_Fecha >= [%0] AND T1.U_Fecha <= [%1]

[%0] and [%1] are the parameters in SAP B1 and I want those parameters in crystal, there in the left join otherwise it won't work. U_Fecha are date parameters by the way.

Regards.

[abhilash_kumar]

Hi,

I'm not sure about SAP B1, but normally if you want to use parameters in the SQL, you would do it the way I mentioned above.

- Abhilash