Query using UDF field to link to Activity

[former_member459477]

Hi Experts,

I am trying to use a number entered in U_NxtVisitReason field in CRD1 table to pull in fields from the Activity which ClgCode matches that number.

(UDF "U_NxtVisitReason" is Alphanumeric)

SELECT T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue AS 'Nxt Visit Due (Approved)', T1.U_LastVisit AS 'Last Visit (Approved)', T1.U_NxtVisitReason AS 'Extra Visit 1', T2.Details

FROM  OCRD T0

LEFT JOIN CRD1 T1 on T1.CardCode=T0.CardCode

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <600

JOIN OCLG T2 on T2.ClgCode = T1.U_NxtVisitReason

Query will not execute - Error message says there is syntax error near 'JOIN'

Please can someone assist?

I have already tried different JOIN types

Thanks

Karen

[former_member184146 · Accepted solution]

Hi,

     Your UDF type is alphanumeric thats why it is showing the conversion error,

make a new UDF of date type or  if you want to use the same field then use the date format in YYYYMMDD.

Regards,

Manish

[former_member459477 · Accepted solution]

Hi Manish

Thanks for response.

OCLG.ClgCode is type Int & U_NxtVisitReason is Alphanumeric - that's why I think if entered data in UDF is numeric this can be equal to Activity number in ClgCode?

Regards

Karen

[former_member184146 · Accepted solution]

Yes Karen, enter the data in both UDF's in numeric type.

and one question what type of data you are entrering in both fields Date type??

as per Nagarajan Query the error coming is because of conversion in formula

SELECT  T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue AS 'Nxt Visit Due (Approved)', T1.U_LastVisit AS 'Last Visit (Approved)', T1.U_NxtVisitReason AS 'Extra Visit 1', T2.Details

FROM  OCRD T0

LEFT JOIN CRD1 T1 on T1.CardCode=T0.CardCode JOIN OCLG T2 on T2.ClgCode = T1.U_NxtVisitReason

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <600

in above query the highlighted field should be in YYYYMMDD format then it can return your required result.

Regards,

Manish

[former_member459477 · Accepted solution]

Hi Manish

Thank you - Yes I understand problem now.

Because I was trying to re-use old UDF field for new purpose some old data of non-numeric type was preventing query from executing. I would have to delete old data.

So now instead I have made new UDF : CRD1.U_ExtraVisit1 also alphanumeric and below query is executing OK.

But the query return result only if there exist an entry in U_ExtraVisit1.

I want query to return all records where there is an entry in CRD1.U_NxtVisitDue <600 days...If there is entry in U_ExtraVisit1 this should appear in the record.. if no entry in this field record should still appear in result with blank field in the column.

Can you advise?

SELECT  T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue AS 'Nxt Visit Due (Approved)', T1.U_LastVisit AS 'Last Visit (Approved)', T1.U_ExtraVisit1 AS 'Extra Visit 1', T2.Details

FROM  OCRD T0

LEFT JOIN CRD1 T1 on T1.CardCode=T0.CardCode JOIN OCLG T2 on T2.ClgCode = T1.U_NxtVisitReason

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <600

Regards,

Karen

[former_member459477]

Hi

I think this is working successfully now.

Thank you for the help with this

Regards,

Karen

SELECT  T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue AS 'Nxt Visit Due (Approved)', T1.U_LastVisit AS 'Last Visit (Approved)', T1.U_ExtraVisit1 AS 'Extra Visit 1', T2.Details

FROM  OCRD T0

JOIN CRD1 T1 on T1.CardCode=T0.CardCode

LEFT JOIN OCLG T2 on T2.ClgCode = T1.U_ExtraVisit1

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <600

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT  T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue AS 'Nxt Visit Due (Approved)', T1.U_LastVisit AS 'Last Visit (Approved)', T1.U_NxtVisitReason AS 'Extra Visit 1', T2.Details

FROM  OCRD T0

LEFT JOIN CRD1 T1 on T1.CardCode=T0.CardCode JOIN OCLG T2 on T2.ClgCode = T1.U_NxtVisitReason

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <600

Thanks & Regards,

Nagarajan

[former_member459477]

Hi Nagarajan,

Thanks for response

This one is giving error:

Conversion failed when converting the nvarchar value 'Genaral' to data type int

Is it to do with the format of U_NxtVisitReason?

Regards,

Karen