Union All with %1 & %2

[Former Member]

Hi,

The folowing query is used to extract data from JE & AP however, when I add one more statement Where T2.U_description2 like '[%1]' at the end, then it doesn't work:

Select T0.objtype, T0.RefDate, T0.TransId, T0.Line_ID, T0.Account, T0.U_Description1, T0.Debit, T0.Credit from JDT1 T0

Where T0.Transtype='30' and T0.U_description1 like '[%1]'

Union All

Select T2.objtype, T2.DocDate, T2.DocEntry, T2.LineNum, T2.AcctCode, T2.U_description2, T2.LineTotal, Null from PCH1 T2

Where T2.U_description2 like '[%1]'

do you know why?

Thanks.

Jack

Edited by: Raymond Chan on Aug 5, 2008 3:58 AM

Edited by: Raymond Chan on Aug 5, 2008 4:01 AM

Edited by: Raymond Chan on Aug 5, 2008 4:01 AM

Edited by: Raymond Chan on Aug 5, 2008 4:04 AM

[former_member583013 · Accepted solution]

Is U_description2 a user field that you have created? What is this description field and what are you trying to get as a result?

Select T0.objtype, T0.RefDate, T0.TransId, T0.Line_ID, T0.Account, T0.U_Description1, T0.Debit, T0.Credit from [dbo\].[JDT1\] T0

Where T0.Transtype='30' and T0.U_description1 like '[%1\]'

Union All

Select T2.objtype, T2.DocDate, T2.DocEntry, T2.LineNum, T2.AcctCode, T2.U_description2, T2.LineTotal, Null from [dbo\].[PCH1\] T2 Where T2.U_description2 like '[%1\]'

Also use fully qualified table reference like [dbo\].[tablename\]

[Former Member · Accepted solution]

Hi, you solved my problem again. Thanks a lot! It works now.

Another question is if I want to add AR header and row to the union, how should I do?

Now it is from JE and AP, then I want to add AR but fields will come from header level and row level, then what should I do?

Thanks.

Jack & Raymond