Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH FORMAT OF OUTPUT:

Expand Messages
  • John Warner
    Arnie it maybe bad form, but concating fields together, adding spaces etc is a part of the SQL language. Take a look at SQL Plus for Oracle, much of it is
    Message 1 of 8 , Aug 17, 2007
    • 0 Attachment
      Arnie it maybe bad form, but concating fields together, adding spaces etc
      is a part of the SQL language. Take a look at SQL Plus for Oracle, much of
      it is devoted to formatting output for reports without actually using a
      reporting tool such as crystal.

      You seem to have been missing the last few weeks, hope all is well with
      you!

      John Warner




      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
      > Sent: Friday, August 17, 2007 3:41 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: RE: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
      > FORMAT OF OUTPUT:
      >
      >
      > Controlling the display of the output is a function of the
      > client application -NOT of SQL Server.
      >
      > SQL Server is NOT a client application. You really, really,
      > should NOT be attempting to format the output display with
      > SQL code. SQL code is extremely inefficient in string
      > manipulation and you will be putting an unnecessary
      > performance burden on your server.
      >
      > The client application is better suited for display and
      > presentation formatting.
      >
      > - Arnie Rowland, MVP (SQL Server)
      >
      > "I am a great believer in luck, and I find that the harder I
      > work, the more I have of it." - Thomas Jefferson (1743-1826)
      >
      >
      >
      >
      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
      > Sent: Friday, August 17, 2007 6:30 AM
      > To: Arnie
      > Subject: RE: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
      > FORMAT OF
      > OUTPUT:
      >
      > Going to need to see what you are doing and maybe a bit of
      > sample data, but yes you can alter the layout of the data.
      >
      > John Warner
      >
      >
      >
      >
      > > -----Original Message-----
      > > From: SQLQueriesNoCode@yahoogroups.com
      > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of dmobley1022
      > > Sent: Thursday, August 16, 2007 2:39 PM
      > > To: SQLQueriesNoCode@yahoogroups.com
      > > Subject: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
      > > FORMAT OF OUTPUT:
      > >
      > >
      > > I have a User Defined Function that the output of the text
      > > items looks
      > > as follows:
      > >
      > > Description Unit Price Qty Ord Amount
      > > CTL 220UW 22" FP LCD $318 1 $318
      > >
      > > Is there a way to format this so that it looks better and lines up
      > > better?
      > >
      >
    • Arnie Rowland
      In the original post, the issue was presented as Is there a way to format this so that it looks better and lines up better? I posit that since SQL Server
      Message 2 of 8 , Aug 17, 2007
      • 0 Attachment
        In the original post, the issue was presented as

        "Is there a way to format this so that it looks better and lines up better?"

        I posit that since SQL Server DOES NOT control fonts, that it is impossible
        to control with SQL code whether or not the output will "looks better and
        lines up better" and that is the task of the presentation client.

        - Arnie Rowland, MVP (SQL Server)

        "I am a great believer in luck, and I find that the harder I work, the more
        I have of it." - Thomas Jefferson (1743-1826)


        -----Original Message-----
        From: SQLQueriesNoCode@yahoogroups.com
        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
        Sent: Friday, August 17, 2007 1:36 PM
        To: Arnie
        Subject: RE: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH FORMAT OF
        OUTPUT:

        I agree. It isn't a perfect fit, but sometimes it's better than the
        alternative.
        SQLmail for instance - if you just want some quick info mailed for debugging
        or reporting or whatever, you may have to make some adjustments on the
        presentation side.

        Travis-

        -----Original Message-----
        From: John Warner [mailto:john@...]
        Sent: Friday, August 17, 2007 2:51 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: RE: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH FORMAT OF
        OUTPUT:


        Arnie it maybe bad form, but concating fields together, adding spaces etc
        is a part of the SQL language. Take a look at SQL Plus for Oracle, much of
        it is devoted to formatting output for reports without actually using a
        reporting tool such as crystal.

        You seem to have been missing the last few weeks, hope all is well with
        you!

        John Warner




        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
        > Sent: Friday, August 17, 2007 3:41 PM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: RE: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
        > FORMAT OF OUTPUT:
        >
        >
        > Controlling the display of the output is a function of the
        > client application -NOT of SQL Server.
        >
        > SQL Server is NOT a client application. You really, really,
        > should NOT be attempting to format the output display with
        > SQL code. SQL code is extremely inefficient in string
        > manipulation and you will be putting an unnecessary
        > performance burden on your server.
        >
        > The client application is better suited for display and
        > presentation formatting.
        >
        > - Arnie Rowland, MVP (SQL Server)
        >
        > "I am a great believer in luck, and I find that the harder I
        > work, the more I have of it." - Thomas Jefferson (1743-1826)
        >
        >
        >
        >
        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
        > Sent: Friday, August 17, 2007 6:30 AM
        > To: Arnie
        > Subject: RE: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
        > FORMAT OF
        > OUTPUT:
        >
        > Going to need to see what you are doing and maybe a bit of
        > sample data, but yes you can alter the layout of the data.
        >
        > John Warner
        >
        >
        >
        >
        > > -----Original Message-----
        > > From: SQLQueriesNoCode@yahoogroups.com
        > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of dmobley1022
        > > Sent: Thursday, August 16, 2007 2:39 PM
        > > To: SQLQueriesNoCode@yahoogroups.com
        > > Subject: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
        > > FORMAT OF OUTPUT:
        > >
        > >
        > > I have a User Defined Function that the output of the text
        > > items looks
        > > as follows:
        > >
        > > Description Unit Price Qty Ord Amount
        > > CTL 220UW 22" FP LCD $318 1 $318
        > >
        > > Is there a way to format this so that it looks better and lines up
        > > better?
        > >
        >





        Yahoo! Groups Links






        Yahoo! Groups Links







        Disclaimer - August 17, 2007
        This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
        This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


        [Non-text portions of this message have been removed]
      • Arnie Rowland
        Yes, John, we often concatenate using SQL. And we often get stuck doing some basic formatting . BUT the SQL language is very inefficient for string
        Message 3 of 8 , Aug 17, 2007
        • 0 Attachment
          Yes, John, we often concatenate using SQL. And we often get 'stuck' doing
          some basic 'formatting'.

          BUT the SQL language is very inefficient for string manipulation. If the
          task can be handled on the client application, having it done on the Server
          puts an unnecessary burden on the Server.

          String manipulation is usually very efficiently handled by C#/VB and most
          presentation layer applications.

          - Arnie Rowland, MVP (SQL Server)

          "I am a great believer in luck, and I find that the harder I work, the more
          I have of it." - Thomas Jefferson (1743-1826)


          -----Original Message-----
          From: SQLQueriesNoCode@yahoogroups.com
          [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
          Sent: Friday, August 17, 2007 12:55 PM
          To: Arnie
          Subject: RE: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH FORMAT OF
          OUTPUT:

          Arnie it maybe bad form, but concating fields together, adding spaces etc
          is a part of the SQL language. Take a look at SQL Plus for Oracle, much of
          it is devoted to formatting output for reports without actually using a
          reporting tool such as crystal.

          You seem to have been missing the last few weeks, hope all is well with
          you!

          John Warner




          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
          > Sent: Friday, August 17, 2007 3:41 PM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: RE: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
          > FORMAT OF OUTPUT:
          >
          >
          > Controlling the display of the output is a function of the
          > client application -NOT of SQL Server.
          >
          > SQL Server is NOT a client application. You really, really,
          > should NOT be attempting to format the output display with
          > SQL code. SQL code is extremely inefficient in string
          > manipulation and you will be putting an unnecessary
          > performance burden on your server.
          >
          > The client application is better suited for display and
          > presentation formatting.
          >
          > - Arnie Rowland, MVP (SQL Server)
          >
          > "I am a great believer in luck, and I find that the harder I
          > work, the more I have of it." - Thomas Jefferson (1743-1826)
          >
          >
          >
          >
          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
          > Sent: Friday, August 17, 2007 6:30 AM
          > To: Arnie
          > Subject: RE: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
          > FORMAT OF
          > OUTPUT:
          >
          > Going to need to see what you are doing and maybe a bit of
          > sample data, but yes you can alter the layout of the data.
          >
          > John Warner
          >
          >
          >
          >
          > > -----Original Message-----
          > > From: SQLQueriesNoCode@yahoogroups.com
          > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of dmobley1022
          > > Sent: Thursday, August 16, 2007 2:39 PM
          > > To: SQLQueriesNoCode@yahoogroups.com
          > > Subject: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
          > > FORMAT OF OUTPUT:
          > >
          > >
          > > I have a User Defined Function that the output of the text
          > > items looks
          > > as follows:
          > >
          > > Description Unit Price Qty Ord Amount
          > > CTL 220UW 22" FP LCD $318 1 $318
          > >
          > > Is there a way to format this so that it looks better and lines up
          > > better?
          > >
          >





          Yahoo! Groups Links







          Disclaimer - August 17, 2007
          This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
          This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


          [Non-text portions of this message have been removed]
        • dmobley1022
          I have pasted the function below. I then use the following {MATH, (SELECT _SMDBA_ . GET_PR_LINE_ITEMS ({TR,Req #}))} in the body of my email to pull the line
          Message 4 of 8 , Aug 21, 2007
          • 0 Attachment
            I have pasted the function below. I then use the following {MATH,
            (SELECT "_SMDBA_"."GET_PR_LINE_ITEMS"({TR,Req #}))}
            in the body of my email to pull the line items.
            However the output isn't very professional.

            Here is the Function
            CREATE FUNCTION [_SMDBA_].[GET_PR_LINE_ITEMS]
            (
            @PRNo int
            )
            RETURNS nvarchar(4000)
            AS
            BEGIN
            -- Declare the return variable here
            DECLARE @LineItems nvarchar(4000)
            SET @LineItems = 'Description' + CHAR(9) + 'Unit Price' + CHAR
            (9) + 'Qty Ord' + CHAR(9) + 'Amount' + CHAR(10)

            DECLARE @Description nvarchar(50),
            @UnitPrice float,
            @QtyOrd smallint,
            @Amount float

            -- Add the T-SQL statements to compute the return value here
            DECLARE cur CURSOR

            FOR SELECT [Description],
            [Unit Price],
            [Qty Ord],
            [Amount]
            FROM _SMDBA_.[Purchasing Items]
            WHERE [ReqN] = @PRNo
            AND [InActive:] = 0

            OPEN cur
            FETCH NEXT FROM cur INTO

            @Description,
            @UnitPrice,
            @QtyOrd,
            @Amount

            WHILE (@@fetch_status <> -1)
            BEGIN
            IF (@@fetch_status <> -2)
            BEGIN
            SELECT @LineItems = @LineItems + @Description
            +
            CHAR(9) + '£' + CONVERT(nvarchar(10),
            @UnitPrice) +
            CHAR(9) + CAST(@QtyOrd As nvarchar
            (4)) +
            CHAR(9) + '£' + CONVERT(nvarchar(10),
            @Amount) +
            CHAR(10)
            END
            FETCH NEXT FROM cur INTO

            @Description,
            @UnitPrice,
            @QtyOrd,
            @Amount

            END

            CLOSE cur
            DEALLOCATE cur

            -- Return the result of the function
            RETURN @LineItems

            END

            --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
            wrote:
            >
            > Going to need to see what you are doing and maybe a bit of sample
            data, but
            > yes you can alter the layout of the data.
            >
            > John Warner
            >
            >
            >
            >
            > > -----Original Message-----
            > > From: SQLQueriesNoCode@yahoogroups.com
            > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of dmobley1022
            > > Sent: Thursday, August 16, 2007 2:39 PM
            > > To: SQLQueriesNoCode@yahoogroups.com
            > > Subject: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
            > > FORMAT OF OUTPUT:
            > >
            > >
            > > I have a User Defined Function that the output of the text
            > > items looks
            > > as follows:
            > >
            > > Description Unit Price Qty Ord Amount
            > > CTL 220UW 22" FP LCD $318 1 $318
            > >
            > > Is there a way to format this so that it looks better and lines
            up
            > > better?
            > >
            >
          • dmobley1022
            I ve been trying to use some HTML in the client application, but I m not sure if 1 it can be done and 2 if I m going it correctly, because its not working that
            Message 5 of 8 , Aug 21, 2007
            • 0 Attachment
              I've been trying to use some HTML in the client application, but I'm
              not sure if 1 it can be done and 2 if I'm going it correctly, because
              its not working that well.

              --- In SQLQueriesNoCode@yahoogroups.com, "Arnie Rowland" <arnie@...>
              wrote:
              >
              > Controlling the display of the output is a function of the client
              > application -NOT of SQL Server.
              >
              > SQL Server is NOT a client application. You really, really, should
              NOT be
              > attempting to format the output display with SQL code. SQL code is
              extremely
              > inefficient in string manipulation and you will be putting an
              unnecessary
              > performance burden on your server.
              >
              > The client application is better suited for display and presentation
              > formatting.
              >
              > - Arnie Rowland, MVP (SQL Server)
              >
              > "I am a great believer in luck, and I find that the harder I work,
              the more
              > I have of it." - Thomas Jefferson (1743-1826)
              >
              >
              >
              >
              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
              > Sent: Friday, August 17, 2007 6:30 AM
              > To: Arnie
              > Subject: RE: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
              FORMAT OF
              > OUTPUT:
              >
              > Going to need to see what you are doing and maybe a bit of sample
              data, but
              > yes you can alter the layout of the data.
              >
              > John Warner
              >
              >
              >
              >
              > > -----Original Message-----
              > > From: SQLQueriesNoCode@yahoogroups.com
              > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of dmobley1022
              > > Sent: Thursday, August 16, 2007 2:39 PM
              > > To: SQLQueriesNoCode@yahoogroups.com
              > > Subject: [SQLQueriesNoCode] New to SQL...PLEASE HELP WITH
              > > FORMAT OF OUTPUT:
              > >
              > >
              > > I have a User Defined Function that the output of the text
              > > items looks
              > > as follows:
              > >
              > > Description Unit Price Qty Ord Amount
              > > CTL 220UW 22" FP LCD $318 1 $318
              > >
              > > Is there a way to format this so that it looks better and lines
              up
              > > better?
              > >
              >
              >
              >
              >
              >
              > Yahoo! Groups Links
              >
              >
              >
              >
              >
              >
              >
              > Disclaimer - August 17, 2007
              > This email and any files transmitted with it are confidential and
              intended solely for SQLQueriesNoCode@yahoogroups.com If you are not
              the named addressee you should not disseminate, distribute, copy or
              alter this email. Any views or opinions presented in this email are
              solely those of the author and might not represent those of Westwood
              Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken
              reasonable precautions to ensure no viruses are present in this
              email, the company cannot accept responsibility for any loss or
              damage arising from the use of this email or attachments.
              > This disclaimer was added by Policy Patrol:
              http://www.policypatrol.com/
              >
              >
              > [Non-text portions of this message have been removed]
              >
            Your message has been successfully submitted and would be delivered to recipients shortly.