none
SQL --> Excel

    Question

  • Hi there,

    I wonder if anyone could help me with the following...

    In excel Developer I am trying to run a sql query to return information, and all my rows are returned successfully...except a column where I have a function in ...does not appear to return any data and remains blank...even though when running the same script directly in sql returns the data..

    The column is populated from a Function which coalesces transaction references into one colum...see below

    Has anyone ever had this issue before...I believe it may be to do with the fact that some of the columns which need to be coalesced are blank and excel cannot handle this.

    Thanks a million for any information.

    Cheers


    Aidan

    BEGIN

    execute

    dbo.sp_executesql@statement = N'ALTER FUNCTION [dbo].[bkGetInvoicesForPayments]

    (

    @PLHeaderAllocationID as Bigint

    )

    Returns varchar(max)

    AS

    BEGIN

    declare @Reference varchar(max)

    SELECT @Reference = COALESCE(@Reference + '','','''','''''''')+transactionreference

    FROM bksupplierInvoicesView

    WHERE bksupplierInvoicesView.PLAllocationHeaderID =@PLHeaderAllocationID

    Return @Reference

    END'


    END

    Thursday, July 18, 2013 11:20 AM

Answers

  • Hi aidan1972

    I think the issue is more likely related to the column data type mapping from SQL Server to Excel, please post your SQL Server table definition codes here for analysis. 


    Allen Li
    TechNet Community Support

    Monday, July 22, 2013 9:40 AM
  • use the below query, I added only the red part, rest is same as yours

    SELECT @Reference = CONVERT(varchar(max), COALESCE(@Reference + '','','''','''''''')+transactionreference)

    FROM bksupplierInvoicesView

    WHERE bksupplierInvoicesView.PLAllocationHeaderID =@PLHeaderAllocationID


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 22, 2013 2:56 PM

All replies

  • Hi aidan1972

    I think the issue is more likely related to the column data type mapping from SQL Server to Excel, please post your SQL Server table definition codes here for analysis. 


    Allen Li
    TechNet Community Support

    Monday, July 22, 2013 9:40 AM
  • use the below query, I added only the red part, rest is same as yours

    SELECT @Reference = CONVERT(varchar(max), COALESCE(@Reference + '','','''','''''''')+transactionreference)

    FROM bksupplierInvoicesView

    WHERE bksupplierInvoicesView.PLAllocationHeaderID =@PLHeaderAllocationID


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 22, 2013 2:56 PM