Join table on calculated fields

1

Hello,

I am trying to bring the “UserName” field from Query 2 to all applicable records in Query 1 based on Query1.[DateSeq]=Query2.KeyString and Query1.[ViewName]=Query2.ViewName ( I can only join Query1 to Query2 using the calculated fields ( [DateSeq] and ViewName]) as those fields do not exist in any other views except Query2)

Query 1:
SELECT i.APCo AS APCompany, i.UIMth AS APUIUIMth, i.UISeq AS APUIUISeq, l.Line AS APULLine, i.Vendor AS APUIVendor, i.APRef AS APUIAPRef, i.Description AS APUIDesc, i.InvTotal AS APUIInvTotal, r.Reviewer AS APURReviewer, v.Name AS APVMName, e.RevEmail AS HQRVRevMail, e.Name AS HQRVName, l.Description AS APULDesc, l.GrossAmt AS APULGrossAmt, r.DateAssigned, Concat('UIMth:',' ', Convert(varchar(10), i.UIMth,1),' ', 'UISeq:',' ', i.UISeq ) as [DateSeq], 'APUI' as [ViewName], brvHQAuditDetail.UserName

Query 2
Select * from brvHQAuditDetail where ViewName='APUI' and RecType='A'

So far I tried the following but it does not work

SELECT i.APCo AS APCompany, i.UIMth AS APUIUIMth, i.UISeq AS APUIUISeq, r.DateAssigned, Concat('UIMth:',' ', Convert(varchar(10), i.UIMth,1),' ', 'UISeq:',' ', i.UISeq ) as [DateSeq], 'APUI' as [ViewName], brvHQAuditDetail

FROM bAPUI AS i WITH (Nolock) INNER JOIN
bAPUL AS l WITH (Nolock) ON i.APCo = l.APCo AND i.UIMth = l.UIMth AND i.UISeq = l.UISeq INNER JOIN
bAPUR AS r WITH (Nolock) ON l.APCo = r.APCo AND l.UIMth = r.UIMth AND l.UISeq = r.UISeq AND l.Line = r.Line INNER JOIN
bAPVM AS v WITH (Nolock) ON i.VendorGroup = v.VendorGroup AND i.Vendor = v.Vendor INNER JOIN
bHQRV AS e WITH (Nolock) ON r.Reviewer = e.Reviewer

                     Inner join brvHQAuditDetail AS p WITH (Nolock) On Concat('UIMth:',' ', Convert(varchar(10), i.UIMth,1),' ', 'UISeq:',' ', i.UISeq )=p.KeyString And [ViewName] =p.ViewName

It is giving me an error when I run it - Invalid column name 'brvHQAuditDetail'.

Do you have any idea on how to fix it?

Thanks in advance

2

select * from
(SELECT i.apco AS APCompany,
i.uimth AS APUIUIMth,
i.uiseq AS APUIUISeq,
l.line AS APULLine,
i.vendor AS APUIVendor,
i.apref AS APUIAPRef,
i.description AS APUIDesc,
i.invtotal AS APUIInvTotal,
r.reviewer AS APURReviewer,
v.NAME AS APVMName,
e.revemail AS HQRVRevMail,
e.NAME AS HQRVName,
l.description AS APULDesc,
l.grossamt AS APULGrossAmt,
r.dateassigned,
Concat('UIMth:', ' ', CONVERT(VARCHAR(10), i.uimth, 1), ' ', 'UISeq:', ' ', i.uiseq) AS [DateSeq],
'APUI' AS [ViewName],
brvhqauditdetail.username ) as a
join
(Select * from brvHQAuditDetail where ViewName='APUI' and RecType='A') as b
on a.[DateSeq] = b.KeyString
and a.[ViewName] = b.[ViewName]

3

Thanks Mike! how would I add other joints to pull data from r,e,l views? Would this be correct?

select * from
(SELECT i.APCo AS APCompany, i.uimth AS APUIUIMth, i.UISEQ AS APUIUISeq, l.Line AS APULLine, i.Vendor AS APUIVendor, i.apref AS APUIAPRef,
i.description AS APUIDesc, i.invtotal AS APUIInvTotal, r.reviewer AS APURReviewer, v.NAME AS APVMName, e.revemail AS HQRVRevMail, e.NAME AS HQRVName,
l.description AS APULDesc, l.grossamt AS APULGrossAmt, r.dateassigned, Concat('UIMth:', ' ', CONVERT(VARCHAR(10), i.uimth, 1), ' ', 'UISeq:', ' ', i.uiseq) AS [DateSeq],
'APUI' AS [ViewName], brvhqauditdetail.username ) as a

INNER JOIN
bAPUL AS l WITH (Nolock) ON i.APCo = l.APCo AND i.UIMth = l.UIMth AND i.UISeq = l.UISeq INNER JOIN
bAPUR AS r WITH (Nolock) ON l.APCo = r.APCo AND l.UIMth = r.UIMth AND l.UISeq = r.UISeq AND l.Line = r.Line INNER JOIN
bAPVM AS v WITH (Nolock) ON i.VendorGroup = v.VendorGroup AND i.Vendor = v.Vendor INNER JOIN
bHQRV AS e WITH (Nolock) ON r.Reviewer = e.Reviewer
Inner join
(Select * from brvHQAuditDetail where ViewName='APUI' and RecType='A') as b
on a.[DateSeq] = b.KeyString
and a.[ViewName] = b.[ViewName]

4

SELECT *
FROM (SELECT i.apco AS APCompany,
i.uimth AS APUIUIMth,
i.uiseq AS APUIUISeq,
l.line AS APULLine,
i.vendor AS APUIVendor,
i.apref AS APUIAPRef,
i.description AS APUIDesc,
i.invtotal AS APUIInvTotal,
r.reviewer AS APURReviewer,
v.NAME AS APVMName,
e.revemail AS HQRVRevMail,
e.NAME AS HQRVName,
l.description AS APULDesc,
l.grossamt AS APULGrossAmt,
r.dateassigned,
Concat('UIMth:', ' ', CONVERT(VARCHAR(10), i.uimth, 1), ' ', 'UISeq:', ' ', i.uiseq) AS [DateSeq],
'APUI' AS [ViewName],
brvhqauditdetail.username) AS a
INNER JOIN bapul AS l WITH (nolock)
ON a.APCompany = l.apco
AND a.APUIUIMth = l.uimth
AND a.APUIUISeq = l.uiseq
INNER JOIN bapur AS r WITH (nolock)
ON l.apco = r.apco
AND l.uimth = r.uimth
AND l.uiseq = r.uiseq
AND l.line = r.line
INNER JOIN bapvm AS v WITH (nolock)
ON a.APUIVendor = v.vendor
--AND i.vendorgroup = v.vendorgroup -- Don't know where VendorGroup is coming from
INNER JOIN bhqrv AS e WITH (nolock)
ON r.reviewer = e.reviewer
INNER JOIN (SELECT *
FROM brvhqauditdetail
WHERE viewname = 'APUI'
AND rectype = 'A') AS b
ON a.[dateseq] = b.keystring
AND a.[viewname] = b.[viewname]

Also, remove the Select * and replace with the column names that you need

5

Tried that but for some reasons it highlights all the fields referring to the "i" view:

those:

  • i.apco AS APCompany,
  • i.uimth AS APUIUIMth,
  • i.uiseq AS APUIUISeq,
  • l.line AS APULLine,
  • i.vendor AS APUIVendor,
  • i.apref AS APUIAPRef,
  • i.description AS APUIDesc,
  • i.invtotal AS APUIInvTotal

It looks like I need to add APUI as "i" but not sure where exactly it needs to go. Thank you!