Where clause not working in sql server

by

Let’s try creating a self-contained example – the below code works fine for me.

Can you insert some sample data from your application into this toy code and see if it runs as you expect? If it does, then the problem lies elsewhere in your code.

CREATE TABLE #TempReceivingUnmatchedUPCs (
RecUPC VARCHAR(20),
RecSupplierInvoiceNumber VARCHAR(20),
Foobar VARCHAR(20)
)

CREATE TABLE #tmpDistributorRecords  (
DistUPC VARCHAR(20),
DistSupplierInvoiceNumber VARCHAR(20),
Foobar VARCHAR(20)
)

INSERT INTO #TempReceivingUnmatchedUPCs
(RecUPC,RecSupplierInvoiceNumber,Foobar)
VALUES
('ABC',22,'FooA'),
('ABD',23,'BarB'),
('BBB',30,'RecA'),
('BBB',31,'RecB')

INSERT INTO #tmpDistributorRecords
(DistUPC,DistSupplierInvoiceNumber,Foobar)
VALUES
('ABA',22,'FooC'),
('BBD',43,'BarD'),
('BBB',30,'DistA'),
('BBB',32,'DistB')


SELECT *
FROM #TempReceivingUnmatchedUPCs tr 
        FULL JOIN #tmpDistributorRecords td 
            ON  ( tr.RecUPC=td.DistUPC )
            where
            tr.RecSupplierInvoiceNumber=td.DistSupplierInvoiceNumber

DROP TABLE #TempReceivingUnmatchedUPCs
DROP TABLE #tmpDistributorRecords

For me, this yields

RecUPC               RecSupplierInvoiceNumber Foobar               DistUPC              DistSupplierInvoiceNumber Foobar
-------------------- ------------------------ -------------------- -------------------- ------------------------- --------------------
BBB                  30                       RecA                 BBB                  30                        DistA

And RecSupplierInvoiceNumber = DistSupplierInvoiceNumber as expected

More Related Contents:

  1. hide/remove header and footer
  2. How do I generate a random number for each row in a T-SQL select?
  3. Combine multiple results in a subquery into a single comma-separated value
  4. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  5. SQL Server: SELECT only the rows with MAX(DATE)
  6. Floor a date in SQL server
  7. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  8. LEN function not including trailing spaces in SQL Server
  9. How FOR XML PATH(”) works when concatenating rows
  10. How to view transaction logs in SQL Server 2008 [closed]
  11. Scheduled run of stored procedure on SQL server
  12. SQL Server ORDER BY date and nulls last
  13. SSRS 2014 report deploy issue
  14. Best way to implement an audit trail in SQL Server?
  15. Set start value for column with autoincrement
  16. How can I change from SQL Server Windows mode to mixed mode (SQL Server 2008)?
  17. SqlDataAdapter.Fill method slow
  18. syntax for single row MERGE / upsert in SQL Server
  19. How to find all the dependencies of a table in sql server
  20. What happens to the primary key Id when it goes over the limit?
  21. Will GETUTCDATE() return the same value if used twice in the same statement?
  22. Convert decimal time to hours and minutes
  23. Create table (structure) from existing table
  24. Importing excel files having variable headers
  25. SQL Query Where Column = ” returning Emoji characters 🎃 and 🍰
  26. How can I make SQL Server return FALSE for comparing varchars with and without trailing spaces?
  27. SQL SERVER – Understanding how MIN(text) works
  28. Pivot multiple columns based on one column in SQL Server
  29. How to convert text column to datetime in SQL
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment