Post reply

Problem with inner joins.

  • April 11, 2004 at 6:04 pm

    #82368

    I'm a relative newbie to sql server and I'm having a problem with a query that uses several inner joins.  It's a query that returns data based on all clients who have not paid their bills yet.  Basically, everything was working fine until I needed to select the amount of a clients invoice & the amount they were overdue.  What happens when I run this is 2 additional fields are returned(the "InvoiceAmt" field & the "OverdueAmt" field).  Unfortunately, all of the values within these fields' rows are listed as "NULL", which it incorrect.  They should display a value.  So it's something wrong with the portion of my statement that begins with "(select Sum..." and ends with "...AdjId desc) as OverdueAmt".

    Just in case, I've included the entire statement but in particular, it seems to be the portion of the statement that I mentioned above.

    begin

    declare @BusSrcId int

    declare @InvoiceNo int

    set @BusSrcId=2035

    select distinct tbilling.billid as InvoiceNo, tclients.clientname as ClientName, tclients.clientholdstmtind as ClientOnHoldInd, tclientcontacts.Billdate, (select Sum(IsNull(tbillingbillcodes.BBCConsFeeAmt,0))+Sum(IsNull(tbillingbillcodes.BBCServFeeAmt,0))

     From TBillingBillcodes Where tbillingbillcodes.BBCBillId=@InvoiceNo) as InvoiceAmt,

    (Select Top 1 IsNull((tadjustments.AdjBalAmt),0) From TAdjustments Where tadjustments.AdjBillID=@InvoiceNo Order By tadjustments.AdjId desc) as OverDueAmt from tbilling

    inner join tclients on tclients.clientid=tbilling.billclientid

    inner join tclientcontacts on tclientcontacts.clientcontclientid=tbilling.billclientid

    inner join tbussources on tbussources.bussrcid = tclientcontacts.clientcontbussrcid

    where tclientcontacts.ClientContBusSrcId=@BusSrcId and tbilling.billpmtstatus != 'PAID'

    end

    I'd appreciate any help you can provide on this.

    Thanks in advance.

    -goalie35

  • April 11, 2004 at 10:01 pm

    #502381

    Try running your subqueries separately in query analyzer. That should give you an idea where it is going wrong.

    Manoj

  • April 12, 2004 at 8:06 am

    #502413

    It doesn't appear that you set @InvoiceNo, which is used in the WHERE clause of each subselect.  It probably should be tbilling.billid and not @InvoiceNo.

    Caine

  • April 12, 2004 at 12:38 pm

    #502456

    "It doesn't appear that you set @InvoiceNo, which is used in the WHERE clause of each subselect.  It probably should be tbilling.billid and not @InvoiceNo."

     

    That was it.  I needed to change my WHERE clause to tbilling.billid.

    Stupid mistake. 

     Thanks for the help.

     

    -Ryan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply