Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: SQL to print value in to cell

    SQL2005

    Code:
    SELECT ISNULL(SUM(connection_charge + monthly_rental_charge), 0) AS Expr4 FROM tblFixedLine_orderdetails_broadband WHERE (fllog_ref = @fllog_ref) 
    UNION ALL 
    PRINT '0'
    What i am trying to achieve in the above SQL statement is that 0 is printed in one of the cells of my table...

    my reasoning being i have 5 tables in all in my join 4 of which i want to add the connection and monthly rental charges for the 5 has no financial information so i cant sum them up, this is why i want to print a 0.

    Any Ideas?

  2. #2
    Join Date
    Jul 2009
    Posts
    32
    Code:
    UNION ALL
    SELECT     '0.00' AS Expr1
    FROM         tblFixedLine_nongeo
    WHERE     (fllog_ref = @fllog_ref)
    Tried the above code too but it doesnt seem to add the row to my union table.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    SELECT SUM(COALESCE(connection_charge, 0) + 
               COALESCE(monthly_rental_charge, 0)) AS Expr4 
    FROM tblFixedLine_orderdetails_broadband 
    WHERE (fllog_ref = @fllog_ref) 
      UNION ALL 
    SELECT 0.0
    I cant say I really understand what your code means.

    You are talking about 5 tables in your join, while the only table in the FROM clause is tblFixedLine_orderdetails_broadband. Is it a view that is a join of 5 tables?

    A UNION ALL with a PRINT statement?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jul 2009
    Posts
    32
    Yes 5 tables unioned but one of the 5 cannot be summed so i need to put a zero in the cell.

  5. #5
    Join Date
    Jul 2009
    Posts
    32
    SELECT 0.0 works thank you!

    SELECT ISNULL(SUM(monthly_spend), 0) AS Expr1
    FROM tblFixedLine_cps
    WHERE (fllog_ref = @fllog_ref)
    UNION ALL
    SELECT ISNULL(SUM(total_conn_charge + total_rental_charge), 0) AS Expr2
    FROM tblFixedLine_call
    WHERE (fllog_ref = @fllog_ref)
    UNION ALL
    SELECT ISNULL(SUM(range_setup + ddi_setup + ddi_rental), 0) AS Expr3
    FROM tblFixedLine_call_isdn
    WHERE (fllog_ref = @fllog_ref)
    UNION ALL
    SELECT ISNULL(SUM(connection_charge + monthly_rental_charge), 0) AS Expr4
    FROM tblFixedLine_orderdetails_broadband
    WHERE (fllog_ref = @fllog_ref)
    UNION ALL
    SELECT 0.0 AS Expr1
    UNION ALL
    SELECT ISNULL(SUM(connection_charge + monthly_rental_charges), 0) AS Expr5
    FROM tblFixedLine_other
    WHERE (fllog_ref = @fllog_ref)

Posting Permissions

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