Results 1 to 7 of 7

Thread: Subquery issue

  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Question Unanswered: Subquery issue

    THis issue relates to MS SQL 2000 service pack 3.

    I have a query where the result don't make sense. The query works fine if i use a temporary table and a subquery to reference the temporary table values but if I just use a subquery directly I get funny results. The query also perfoms correctly if I remove the 2nd part of the union in the sub query

    An example of the funny results: If I change Line #4 from demandnumber >= 28500 to 28554 the query result increases from 0 to 30. This is not mathematically possible?

    Please review the query below.

    declare @CL int

    select @CL = codeid from codes where codetyp = 'BTSTATUS' and coderef = 'CL'

    select count(*) from paymentdemands
    where demandnumber >= 28550
    and owntyp = 'ME' and ownid in

    (select ownid from feedetails fd
    join codes fpt on fd.paymenttermsid = fpt.codeid and fpt.coderef = 'DFSA' left join bankaccounts ba on fd.sourcebankaccid = ba.bankaccid
    left join banks b on ba.bankid = b.bankid and b.sortcode = '124267'
    left join banktransactions bt on ba.bankaccid = bt.bankaccid and bt.statusid = @CL
    where owntyp = 'ME' and bt.banktxnid is null
    union
    select ownid from feedetails fd
    join codes fpt on fd.paymenttermsid = fpt.codeid
    where owntyp = 'ME' and fpt.coderef = 'DFSA' and fd.sourcebankaccID is null)
    Last edited by JRT76; 03-04-10 at 08:03.

  2. #2
    Join Date
    Feb 2004
    Posts
    30

    Then try SP4

    If you sure it goes wrong after you apply the SP3, then why not you try SP4 for SQL Server 2000?

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    I'm not sure that it specifically relates to SP3. That's the version we are running but have done for years.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Code:
    declare @CL int
    
    select @CL = codeid from codes where codetyp = 'BTSTATUS' and coderef = 'CL'
    
    select	count(*)
    from	paymentdemands 
    where	demandnumber >= 28550 
    		and owntyp = 'ME'
    		and ownid in 
    			(
    			select	ownid
    			from	feedetails fd
    			join	codes fpt on
    						fd.paymenttermsid = fpt.codeid
    						and fpt.coderef = 'DFSA'
    			left
    			join	bankaccounts ba on
    						fd.sourcebankaccid = ba.bankaccid
    			left
    			join	banks b on
    						ba.bankid = b.bankid
    						and b.sortcode = '124267'
    			left
    			join	banktransactions bt on
    						ba.bankaccid = bt.bankaccid
    						and bt.statusid = @CL
    			where	owntyp = 'ME'
    					and bt.banktxnid is null 
    
    			union 
    			
    			select	ownid
    			from	feedetails fd
    			join	codes fpt on
    						fd.paymenttermsid = fpt.codeid
    			where	owntyp = 'ME'
    					and fpt.coderef = 'DFSA'
    					and fd.sourcebankaccID is null
    Don't know about you, but things jump out at me when I make code a bit neater . . .

    Sorry about the large indentions--the tab character is being interpreted differently than on my machine.

    First problem I see . . .

    I believe your line

    select @CL = codeid from codes where codetyp = 'BTSTATUS' and coderef = 'CL'

    is incorrect. I think it should be

    select @CL = (select codeid from codes where codetyp = 'BTSTATUS' and coderef = 'CL')
    Last edited by PracticalProgram; 03-04-10 at 15:53.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Another thing that jumps out when its neater is the left joins being transformed to inner joins and tables not being required.
    Code:
    	and ownid in 
    		(
    		select	ownid
    		     from	feedetails fd
    		join	codes fpt 
                                on  fd.paymenttermsid = fpt.codeid
      		 and  fpt.coderef = 'DFSA'
    		left join	bankaccounts ba
                                on  fd.sourcebankaccid = ba.bankaccid
    
    --why is this table in query???
    		left join	banks b 
                                on  ba.bankid = b.bankid
    		 and  b.sortcode = '124267'
    
    		left join	banktransactions bt 
                                on  ba.bankaccid = bt.bankaccid
    		 and  bt.statusid = @CL
    		where	owntyp = 'ME'
    --about to change left join to a join
    		   and bt.banktxnid is null 
    
    		union 
    			
    		select	ownid
    		    from	feedetails fd
    		join	codes fpt
                                on  fd.paymenttermsid = fpt.codeid
    		 and fpt.coderef = 'DFSA'
    		where  owntyp = 'ME'
    		   and  fd.sourcebankaccID is null
    Maybe instead of worrying about the type of join, you could just change it to an existence check as well.
    Dave

  6. #6
    Join Date
    Mar 2010
    Posts
    4
    Thanks for your replies. The left join to the banktransactions table is being used to only include records where a corresponding record does not exist in the banktransactions table (see 'where' clause "and bt.banktxnid is null"). Is there a better way of doing this?

  7. #7
    Join Date
    Mar 2010
    Posts
    4
    Any ideas why the code works when I use a temporary table as opposed to a subquery?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •