SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    using column values to compute other columns

    Hey guys.
    I'm trying something I've never done before.

    Basically what I want to do is use the value of a column (in this case 'App Id') to allow me to calculate on the fly how much is owed on a particular application (oustanding)

    The code below will produce the correct result for when when I hard code in a value for application_id_int (in this case 76)

    I want to be able to use the application_id_int returned in the first column as the value that is used in the nested select statement.

    Any ideas folks? Help much appreciated.

    DS



    Code SQL:
    SELECT 
     
    		claimant.claimant_id_int AS 'Claimant', 
    			application_id_int AS 'App ID', 
    			application_date AS DATE,  
    			claimant.office_no_int AS Office,  
    			application_type_id_int AS TYPE, 
    			amount_requested_dec AS Requested, 
    			amount_awarded_dec AS Awarded,
     
     
    			(SELECT(Amount_awarded_dec) FROM [Application] WHERE application_id_int = '76') - (SELECT ISNULL(SUM(repayment_amount_dec), 0) FROM repayments WHERE application_id_int = '76') AS Outstanding,
     
     
    			claimant.username_str AS Username
    		FROM dbo.application, dbo.claimant
    		WHERE (claimant.claimant_id_int = @claimant_id) AND claimant.claimant_id_int = application.claimant_id_int

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT claimant.claimant_id_int AS 'Claimant'
         , application_id_int AS 'App ID'
         , application_date AS Date
         , claimant.office_no_int AS Office
         , application_type_id_int AS Type
         , amount_requested_dec AS Requested
         , amount_awarded_dec AS Awarded
         , Amount_awarded_dec - COALESCE(r.repayments,0) AS Outstanding
         , claimant.username_str AS Username
      FROM dbo.application
    INNER
      JOIN dbo.claimant
        ON claimant.claimant_id_int = application.claimant_id_int 
    LEFT OUTER
      JOIN ( SELECT application_id_int
                  , SUM(repayment_amount_dec) AS repayments
               FROM repayments
             GROUP
                 BY application_id_int ) AS r
        ON r.application_id_int = application.application_id_int
     WHERE claimant.claimant_id_int = @claimant_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a million for the quicke reply.
    Still getting a small error and since your code is something preeeetty complicated for me I'm not sure what I should be doing to correct it!

    Line 8
    The multi-part identifier "application.application_id_int" could not be bound.

    Thanks again.

    DS

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's because i didn't realize (and your query did not specify) which table that column happens to be in

    prefix it correctly and let's see what happens

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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