The multi-part identifier "tblapVoucherLine.VoucherLineNumber" could not be bound

[dwg23]

Hello,
I am getting the above error from the below code and don't understand why.
Can someone please enlighten me?
Thanks in advance.
DWG23

Select
  Tx.TxSource,
  Tx.TxType,
  Tx.TxDate,
  Tx.SourceTxReference
From
  tblglTxHistory Tx Inner Join
  tblapVoucher On Tx.SourceTxReference = tblapVoucher.VoucherNumber + tblapVoucherLine.VoucherLineNumber + tblapVoucher.Vendor  Inner Join
  tblapVoucherLine On tblapVoucherLine.VoucherNumber =
    tblapVoucher.VoucherNumber
Where
  Tx.TxSource = 'APVCHR' And
  (Tx.TxType = 'APEXP' Or
    Tx.TxType = 'pndpay' Or
    Tx.TxType = 'DRPFRT' Or
    Tx.TxType = 'APDROP') And
  Tx.TxDate > '04/01/2015'

 

[gmmastros]

Your join between the Tx table and the Voucher table includes a reference to the VoucherLine table. Could this be your problem?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dwg23]

Ah! you are correct!
I restructured the query and it works! it takes almost 4 minutes to run but it works!
Thanks for the help!

here is the query as it is now.

Select
  Tx.TxType,
  tblapVoucher.PONumber,
  tblapVoucher.Vendor,
  tblapVoucher.InvoiceDate,
  tblapVoucher.InvoiceAmount,
  tblapVoucherLine.POLineNumber,
  tblapVoucherLine.Qty,
  tblapVoucherLine.UnitCost,
  tblapVoucherLine.ExtendedCost,
  tblapVendor.Name
From
  tblapVoucher Inner Join
  tblapVoucherLine On tblapVoucherLine.VoucherNumber =
    tblapVoucher.VoucherNumber Inner Join
  tblglTxHistory Tx On tblapVoucherLine.VoucherNumber +
    tblapVoucherLine.VoucherLineNumber + tblapVoucher.Vendor =
    Tx.SourceTxReference Inner Join
  tblapVendor On tblapVoucher.Vendor = tblapVendor.Vendor
Where
  Tx.TxSource = 'APVCHR' And
  (Tx.TxType = 'APEXP' Or
    Tx.TxType = 'pndpay' Or
    Tx.TxType = 'DRPFRT' Or
    Tx.TxType = 'APDROP') And
  Tx.TxDate > '04/01/2015'

 

[gmmastros]

4 minutes??? ouch. How many rows are returned by this query?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dwg23]

There are 2625 rows returned by the query. I did drop off a couple of items that were the same data just from different tables and it runs in about 10 seconds now.