select query left outer join not returning expected result

Question

declare @pTargetFund varchar(8) = '500', @tolAmt money = 1

declare @test1 table (

amount money,

tradeid int,

cusip varchar(12),

principal decimal(14,0),

principalx decimal(2,0),

accruint decimal(14,0),

accruintx decimal(2,0),

transnum varchar(20),

masttick varchar(20),

maturity varchar(50),

settledate varchar(50),

transtype varchar(50),

transcode varchar(4),

rectype decimal(3,0),

series varchar(4),

settleloc varchar(3),

ticker varchar(8),

shortname varchar(20),

fund varchar(8)

)

declare @test2 table (

dollaramount varchar(10),

centsamount varchar(2),

ID int,

cusip varchar(12),

statuscode char(1),

securitysubtype varchar(3),

SCDTransCode varchar(50),

participantnumber varchar(4),

sourceDate datetime

)

Insert into @test1

SELECT 97778333.33,	675984,	'47816FGB1',	9777833333,	2,	0,	0,	758130,	758129,	'20240711',	'20240207',	'B',	'OA',	'2',	'CP',	'DTC',	'JNJPP',	'WILL CAP APP',	'500'

select * from @test1

INSERT INTO @test2

SELECT '0048889166', '66', 145864, '47816FGB1', 'M', '525', 'BUY', '8486', '2024-02-07 00:00:00.000'

union all

SELECT '0048889166', '66', 145865, '47816FGB1', 'M', '525', 'BUY', '8486', '2024-02-07 00:00:00.000'

union all

SELECT '0021474753',	'15',	145866,		'59157THL0', 'M', '525', 'BUY', '8486', '2024-02-07 00:00:00.000'

union all

SELECT '0050000000',	'00',	145869,	'06417LYZ4', 'M', '525', 'BUY',  '8486', '2024-02-07 00:00:00.000'

union all

SELECT '0050000000',	'00',	145870,	'06417LYZ4', 'M', '525', 'BUY','8486', '2024-02-07 00:00:00.000'

select * from @test2

SELECT 

	sum(cast(Ds.DollarAmount as money)) As dollarAmount, 

	sum(cast(ds.CentsAmount as money)) As CentsAmount, 		 		

	bt.shortname,

	ds.CUSIP

FROM @test2 DS

LEFT OUTER JOIN @test1 BT ON bt.cusip = ds.CUSIP

	AND CONVERT(money, DollarAmount + '.' + CentsAmount)

		BETWEEN bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) - @tolAmt

			AND bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) + @tolAmt

	AND bt.settledate = '20240207'

	AND bt.transtype = 'B'

	AND bt.series = 'CP'

	AND bt.transcode in ('CPA', 'PA', 'OA', 'COA', 'OMB')

	AND bt.settleloc = 'DTC'

WHERE DS.StatusCode = 'M'

	AND SecuritySubType IN (520, 525, 526)

	AND ISNULL(bt.fund, @pTargetFund) = @pTargetFund 

	AND ScdTransCode = 'BUY'

	AND ParticipantNumber = '8486'

	AND DS.sourceDate = '20240207'

	GROUP BY DollarAmount, CentsAmount, DS.CUSIP, bt.shortname
```When I do left outer join of test2 with test1, all rows should be returned from test2 with matching rows from test1. Then why is shortName blank.

can someone please help.

Thanks

Answer 1

Please try commenting out the where clauses first and see if the data shows up in the initial output, then gradually put the where clauses back.

Answer 2

short name is from the right table. you are doing a left outer join, so you get all left rows that match the join, and all left rows that don't match (with null for right values), then filtered by the where.

in the join clause only left row with id '47816FGB1' have a right row match. but the join clause:

AND CONVERT(money, DollarAmount + '.' + CentsAmount)
	BETWEEN bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) - @tolAmt
      AND bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) + @tolAmt

removes any matching right rows. so you only get left rows with no matching right values.

Answer 3

Hi @Spunny

As answered above, the issue caused by this clause:

AND CONVERT(money, DollarAmount + '.' + CentsAmount) BETWEEN bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) - @tolAmt AND bt.principal/POWER(10, principalx) + BT.accruint/power(10, accruintx) + @tolAmt

For the cusip 47816FGB1, the calculated result is 48889166.66 between 97778332.33 and 97778334.33 which obviously not matched the join condition.

Please check this clause and take some modification.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".