[Resolved] Update table-substring in where clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Update table-substring in where clause

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-17 : 11:28:30

I seem to have a problem with using the substring function in the where clause: Values "1102877, 61531668" vs "1102877, 6153166". Using the below I think I should have a match on above values.

update #Combined_ReceiptTable
set    #Combined_ReceiptTable.jde_gross_qty  = #JDE_ReceiptTable.jde_gross_qty,
       #Combined_ReceiptTable.jde_net_qty    = #JDE_ReceiptTable.jde_net_qty,
       #Combined_ReceiptTable.gross_qty_diff = #Combined_ReceiptTable.ac2_gross_qty - #JDE_ReceiptTable.jde_gross_qty,
       #Combined_ReceiptTable.net_qty_diff   = #Combined_ReceiptTable.ac2_net_qty - #JDE_ReceiptTable.jde_net_qty
from   #Combined_ReceiptTable, #JDE_ReceiptTable
where  #Combined_ReceiptTable.trans_date     = #JDE_ReceiptTable.jde_trans_date and
       #Combined_ReceiptTable.petroex_code   = #JDE_ReceiptTable.jde_petroex_code and
       #Combined_ReceiptTable.tank_id        = #JDE_ReceiptTable.jde_tank_id and
       substring(#Combined_ReceiptTable.ticket_no, 1, 16)      = substring(#JDE_ReceiptTable.jde_ticket_no, 1, 16)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-17 : 11:38:22

they are a match.

select substring('1102877, 61531668', 1, 16)
union
select substring('1102877, 6153166', 1, 16)

sure those are the actual values? what are the data types? sure the other conditions in the join match?

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-17 : 11:43:24

Hi russell,

Got it working now. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources