Adding RcvDtl table to MtlTags RDD

Epicor ERP 10
,
1

So I am trying to add the RcvDtl table to the MtlTags RDD and I thought I did everything I needed to do for the join but I am apparently missing something because i keep getting an error when previewing the tag. This is what I have for the relationship on the RDD and the code is what I have for the SSRS dataset:
Any help would be much appreciated!

image
image830×536 21.8 KB

=“SELECT T1.AsmSeq, T1.TagNum, T1.BCAsmSeq, T1.BCBinNum, T1.BCJobNum, T1.BCLotNum, T1.BCOprSeq, T1.BCPartNum, T1.BCWhseCode, T1.BinNum, T1.ItemQty, T1.JobNum, T1.LaborNote, T1.LotNum, T1.NCComment, T1.NonConfTranID, T1.OpCode, T1.OprSeq, T1.PartNum, T1.PartDesc, T1.POLine, T1.PONum, T1.PORel, T1.Reason, T1.ReasonDesc, T1.ResGrpID, T1.TagFormat, T1.TagTitle, T1.UM, T1.VendID, T1.VendName, T1.WhseCode, T1.QtyNum, T1.LegalNumber, T1.JobSeqType, T1.Revision, T1.BCPCID, T1.PCID, T1.DMRNum, T1.Company, T2.[Company], T2.[PONum],
T2.[POLine], T2.[ImportNum] FROM MtlTags_” + Parameters!TableGuid.Value + " T1 LEFT OUTER JOIN RcvDtl_" +Parameters!TableGuid.Value + " T2
ON T1.[Company] = T2.[Company] AND T1.[PONum] = T2.[PONum] AND T1.[POLine] = T2.[POLine]"

2

What is the error you are getting?

3

the error I am getting is:

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: The remote server returned an error: (500) Internal Server Error.

4

What happened to Seq 1?

image

5

hmm I don’t know that is the first time I noticed that it didn’t start with 1. I changed it to start with 1:

image
image830×536 21.8 KB

I tried it again I’m pretty sure that wasn’t the issue considering it is still giving me the same error.

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: The remote server returned an error: (500) Internal Server Error.

6

Well, I was hoping.

The message seems like it is an error with the SysAgent. Can you get a more detailed error from the server?

Not related to the error, but did you check the Linked Tables to see if you could access the field there? That is always my first choice when I need to add data in. If it is not there then I would add the table.

7

well the field I am trying to add is the ImportNum from the RcvtDtl or the RcvHead table because the MtlTags is a temp-table and I have already looked to see if it is in there unfortunately it isn’t. So that is why I am adding the RcvDtl table.

This is what it says which doesn’t make since:

Query execution failed for dataset ‘MtlTags’. —> System.Data.SqlClient.SqlException: Incorrect syntax near ‘T1’.

8

Try this. SSRS gets real pissy if there is something not right and I copied and pasted what you had into Notepad and saw that 2 of the " were the ones SSRS does not like.

="SELECT T1.AsmSeq, 
T1.TagNum, 
T1.BCAsmSeq, 
T1.BCBinNum, 
T1.BCJobNum, 
T1.BCLotNum, 
T1.BCOprSeq, 
T1.BCPartNum, 
T1.BCWhseCode, 
T1.BinNum, 
T1.ItemQty, 
T1.JobNum, 
T1.LaborNote, 
T1.LotNum, 
T1.NCComment, 
T1.NonConfTranID, 
T1.OpCode, 
T1.OprSeq, 
T1.PartNum, 
T1.PartDesc, 
T1.POLine, 
T1.PONum, 
T1.PORel, 
T1.Reason, 
T1.ReasonDesc, 
T1.ResGrpID, 
T1.TagFormat, 
T1.TagTitle, 
T1.UM, 
T1.VendID, 
T1.VendName, 
T1.WhseCode, 
T1.QtyNum, 
T1.LegalNumber, 
T1.JobSeqType, 
T1.Revision, 
T1.BCPCID, 
T1.PCID, 
T1.DMRNum, 
T1.Company, 
T2.Company, 
T2.PONum,
T2.POLine, 
T2.ImportNum 
FROM MtlTags_" + Parameters!TableGuid.Value + " T1  
LEFT OUTER JOIN RcvDtl_" +Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.PONum = T2.PONum AND T1.POLine = T2.POLine"
9

Nope still giving me the same error:

Query execution failed for dataset ‘MtlTags’. —> System.Data.SqlClient.SqlException: Incorrect syntax near ‘T1’.

10

I would put some extra spaces or a return after the T1

These things become a hunt and peck for which one is causing the error. I think @ckrusen has some tricks to find the error. I usually do it blindly.

11

Oh ok well I appreciate you trying to help.