Tring to add a ''=========='' to result set

Question

Post reply

Tring to add a ''=========='' to result set

BuilderBits

SSCertifiable

Points: 6071
More actions
September 13, 2005 at 5:59 pm

#168730

Hello,
I AM tring to add a '==============' divider in my result set so that i can better split up my grouping...However, i am receving an error of ::: Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '========' to a column of data type int.
Thanks,
erik....

SELECT o.Customerid as MyCustomersid,
CASE GROUPING (od.Orderid)WHEN 0 THEN od.Orderid ELSE 99999 END AS MyfROG,
   CASE WHEN GROUPING (od.Orderid) = 1 THEN od.Orderid ELSE '========' END,
   SUM ( Quantity * UnitPrice ) AS [TOTAL AMOUNT]
    FROM Orders o, [Order Details] od
      WHERE YEAR(Orderdate) = 1997 AND od.Orderid = o.Orderid
        GROUP BY o.Customerid, od.Orderid WITH ROLLUP
          ORDER BY o.Customerid, od.Orderid
Dam again!

Viewing 15 posts - 1 through 15 (of 21 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

When the results of a case statement do not match in datatype you will get this error.

You connot convert '===============' into an int.

try CASE WHEN GROUPING (od.Orderid) = 1 THEN convert(od.Orderid as char(8)) ELSE '========' END

BuilderBits SSCertifiable Points: 6071 More actions · Answer 2

Server: Msg 170, Level 15, State 1, Line 8

Line 8: Incorrect syntax near '.'.

---------------------------\\

SELECT o.Customerid as MyCustomersid,

CASE GROUPING (od.Orderid)WHEN 0 THEN od.Orderid ELSE 99999 END AS MyfROG,

CASE WHEN GROUPING (od.Orderid) = 1 THEN convert(od.Orderid as char(8)) ELSE '========' END,

SUM ( Quantity * UnitPrice ) AS [TOTAL AMOUNT]

FROM Orders o, [Order Details] od

WHERE YEAR(Orderdate) = 1997 AND od.Orderid = o.Orderid

GROUP BY o.Customerid, od.Orderid WITH ROLLUP

ORDER BY o.Customerid, od.Orderid

Dam again!

BuilderBits SSCertifiable Points: 6071 More actions · Answer 3

i also need to get in a newbie question while i am at it

when you use the char(8) that is because the '========' is 8 charters long??? and if i was to do this '===========' that would be char(12)??

thanks alot!

CASE WHEN GROUPING (od.Orderid) = 1 THEN convert(od.Orderid as char(8)) ELSE '========' END

Dam again!

BuilderBits SSCertifiable Points: 6071 More actions · Answer 4

So in other words it is a fixed length..

correct?

Dam again!

Sergiy SSC Guru Points: 110208 More actions · Answer 5

It's just silly mistake:

cast(od.Orderid as char(8))

or

convert(char(8), od.Orderid)

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Use Varchar(255) or Varchar(8000) instead of Char(8) if you need.

The only point is to use same type of data in the column. '=======' is char (varchar) data, so you cannot use int, datetime or decimal, you must convert it to char data type.

_____________
Code for TallyGenerator

BuilderBits SSCertifiable Points: 6071 More actions · Answer 7

Thanks! that works great..

now is there a way to add the '=====' before or after the Total Amout in the Total Amount column?

Dam again!

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Same way. Just don't forget to convert "Total Amount" to any of char types before concatenation.

select '====' + convert(varchar(255), [Total Amount]) + '===='

_____________
Code for TallyGenerator

BuilderBits SSCertifiable Points: 6071 More actions · Answer 9

My qustion was more about the fact IS WHEN IMPLEMETING DATA USING THE CHAR(8).. THIS DOES MEAN THAT ONLY 8 CHARTER ARE ALLOWED?

I AM ASKING BECAUSE I WOULD LIKE TO KNOW IF THIS IS TRUE.. NOT BECAUSE I AM CONCERNED THAT I CAN NOT MAKE THE '=======' LONGER OR NOT..

KINDA OF A NESTED QUESTION.. LOL

Dam again!

BuilderBits SSCertifiable Points: 6071 More actions · Answer 10

That one is a little over my head.. I will need a little brighter picture please//

\

Dam again!

Sergiy SSC Guru Points: 110208 More actions · Answer 11

If you will convert to CHAR(8) yes, not more than 8 chars will be allowed. In case of less than 8 chars string will be filled with spaces up to 8 chars.

But it's up to you. Change you declaration as you need it to be.

It's gonna be an issue only if you use ISNULL. This will take shortest type and apply for both strings.

To avoid it use basic rule of programming - don't use explicit constants in code.

Declare @SepText nvarchar(4000)

SET @SepText = '========'

and you never have problems with 8 characters.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Replace

SUM ( Quantity * UnitPrice ) AS [TOTAL AMOUNT]

with

'====' + convert(varchar(25), SUM ( Quantity * UnitPrice ) ) + '====' AS [TOTAL AMOUNT]

OR

@SepText + convert(varchar(25), SUM ( Quantity * UnitPrice ) ) + @SepText AS [TOTAL AMOUNT]

_____________
Code for TallyGenerator

BuilderBits SSCertifiable Points: 6071 More actions · Answer 13

Thanks for the help!

funny how the ---> '====' + convert(varchar(25), can be added without being incapsulated in the convert. throwing me for a loop on that one..

Dam again!

BuilderBits SSCertifiable Points: 6071 More actions · Answer 14

Like with the roll up function is it alos posiable to add a row directly after the rollup super summery(bol word )

so:

$54,455.00

============

Dam again!