Very new Newbie Needs Help with simple query

Question

Post reply

Very new Newbie Needs Help with simple query

bbouffor

Newbie

Points: 2
More actions
January 9, 2009 at 1:53 pm

#217415

We have just moved to MS Dynamics Navision and am trying to convert my Oracle procedures. I need some help, the query is a simple one I am creating to get a handle on the different expressions
but I get the error
"Conversion failed when converting the varchar value 'Quote' to data type int." and have been unable to figure out the fix.
Thanks for any assistance.
select [header].[Document Type],
case [header].[Document Type]
when 0 then 'Quote'
when 2 then 'Sales Order'
else [header].[Document Type]
END
, [header].[No_]Number
, [line].[Sales Order No_]
, [line].[No_] Item
, [line].[Job No_]
, [line].[Description]
, [line].[Description 2]
, CONVERT(DECIMAL(20,0),[line].[Qty_ to Ship]) Qty2Ship
, CONVERT(DECIMAL(20,0),[line].[Qty_ to Invoice]) Qty2Inv
, CONVERT(DECIMAL(20,2),[line].[Amount]) Amount
, CONVERT(VARCHAR(8), [Order Date], 1) OrderDate
, CONVERT(VARCHAR(8), [header].[Shipment Date], 1) ShipDate
, [header].[Customer Posting Group]PostGroup
, [header].[Order Class]
, [header].[On Hold]
, [header].[Shipping No_]
, [header].[Prepayment No_]
, [header].[Transaction Type]
, CONVERT(VARCHAR(8),[header].[Document Date], 1) DocDate
from [GRT TEST$Sales Header]header
,[GRT TEST$Sales Line]line
where [header].[No_] = [line].[Document No_]
order by 4, 2

Viewing 5 posts - 1 through 5 (of 5 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Your issue is this statement:

case [header].[Document Type]

when 0 then 'Quote'

when 2 then 'Sales Order'

else [header].[Document Type]

END

This should fix it:

case [header].[Document Type]

when 0 then 'Quote'

when 2 then 'Sales Order'

else Convert(varchar(12), [header].[Document Type])

END

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

bbouffor Newbie Points: 2 More actions · Answer 2

Jack - you are a star. Thanks for the quick reply:D

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 3

Yeah... that's one thing that Oracle does do better... some of the implicit conversions. If you look in Books Online (comes with SQL Server as the help system) for CONVERT, you'll find a handy chart that explains when an implicit conversion will take place, or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

bbouffor (1/9/2009)
Jack - you are a star. Thanks for the quick reply:D

I wouldn't go that far, but thanks and you are welcome.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question