SQL Server Integer Values converting to String

I'm working with SQL server as the database for Microsoft Dynamics Nav.
The constant problem i'm trying to work around is converting the Integer values from "Document Type" to the associated string.

i.e.
"Interaction Log Entry" Table has a field called "Document Type"
These values are from 0 =""  to  25="Sales Invoice"
I'm using RecordSets (Microsoft ActiveX Data Objects 2.8 Library) to get the info from the SQL database.
how can i bring in the String instead of writing 8 difference functions to convert the associated integers to the values (one for each table i'm using)


SELECT  [Entry No_],
	[Contact No_],
	[Contact Company No_],
	[Document No_],[Date],
	[Time Of Interaction],
	[User ID],
	[Interaction Template Code],
	[Salesperson Code],
	[Correspondence Type],
	[Document Type] 
FROM [Quick Corporate$Interaction Log Entry] 
WHERE [Interaction Group Code] = 'SALES' 
    AND [Entry No_] > '10000'"

Open in new window

LVL 10
bromy2004Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Something like this perhaps:
SELECT  [Entry No_],
        [Contact No_],
        [Contact Company No_],
        [Document No_],
        [Date],
        [Time Of Interaction],
        [User ID],
        [Interaction Template Code],
        [Salesperson Code],
        [Correspondence Type],
        CAST([Document Type] AS varchar(20)) [Document Type]
FROM    [Quick Corporate$Interaction Log Entry]
WHERE   [Interaction Group Code] = 'SALES'
        AND [Entry No_] > '10000'

Open in new window

0
bromy2004Author Commented:
nope
With your code
10001      CT003322      CT003322      SSH-009441      2009-08-07 00:00:00.000      1754-01-01 10:06:19.413      BHARTIP      S_SHIP      122      0      5
With My Code
10001      CT003322      CT003322      SSH-009441      2009-08-07 00:00:00.000      1754-01-01 10:06:19.413      BHARTIP      S_SHIP      122      0      5

#5 at the end is the Document Type
0
batuesCommented:
I don't know what table relationship or rule you have with your [Document Type] field. Maybe you can post something more clearly. But if you just want to simplify things, make 1 function whose 8 previous functions inside.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bromy2004Author Commented:
The Table doesn't have any relationship.
My understanding is that from MS Dynamics Nav, it converts the Int to Str
The option for the field is
 ,Sales Qte.,Sales Blnkt. Ord,Sales Ord. Cnfrmn.,Sales Inv.,Sales Shpt. Note,Sales CR/Adj Note,Sales Stmnt.,Sales Rmdr.,Serv. Ord. Create,Serv. Ord. Post,Purch.Qte.,Purch. Blnkt. Ord.,Purch. Ord.,Purch. Inv.,Purch. Rcpt.,Purch. CR/Adj Note,Cover Sheet,Sales Return Order,Sales Finance Charge Memo,Sales Return Receipt,Purch. Return Shipment,Purch. Return Ord. Cnfrmn.,Service Contract,Service Contract Quote,Service Quote
So 0=""
1="Sales Qte."
2="Sales Blnkt. Ord"
etc.
But for all the table i'm using, they are different, and i need to convert between each of them.

Interaction Log Entry :Document Type
 ,Sales Qte.,Sales Blnkt. Ord,Sales Ord. Cnfrmn.,Sales Inv.,Sales Shpt. Note,Sales CR/Adj Note,Sales Stmnt.,Sales Rmdr.,Serv. Ord. Create,Serv. Ord. Post,Purch.Qte.,Purch. Blnkt. Ord.,Purch. Ord.,Purch. Inv.,Purch. Rcpt.,Purch. CR/Adj Note,Cover Sheet,Sales Return Order,Sales Finance Charge Memo,Sales Return Receipt,Purch. Return Shipment,Purch. Return Ord. Cnfrmn.,Service Contract,Service Contract Quote,Service Quote

Document Distribution Details: Document Type
S.Quote,S.Order,S.Invoice,S.Cr.Memo,,P.Quote,P.Order,P.Invoice,P.Cr.Memo,P.Receipt,,,,,,,,,,,S.Blanket,P.Blanket,,,,,,,,,,,,,,,,,S.Shipment,,S.Work Order,Statement

Interaction Log Entry: Correspondence Type
 ,Hard Copy, Fax, Email

Customer: Delivery Document
Priced Invoice, Priced Delivery Document, Un-Priced Delivery Document

For each of those, it returns an Integer
and for the first 2 they need to be interchangeable (5:Sales Inv. = 3:S.Invoice)

Could you provide a sample function that would do that?
Its in Excel VBA
0
batuesCommented:
I still don't get the whole picture :D Where do you store the option?
But... it seems that you can use array as the option storage and use it in the code.
0
bromy2004Author Commented:
Figured out that i was over complicating things with getting Int to Enum to String

So i cut out the Enum and I've come up with this function

The Option is stored in SQL as an integer.
The string version is most likely stored in the Dynamics Nav Application Settings

Any better suggestions will get points, otherwise i'll accept this Comment
Private Enum Table
  IntLog = 0
  DocDist = 1
End Enum

Private Function DocType(ByVal Value As Byte, ByVal Table As Table) As String
Select Case Table
  'Case Interaction Log
  Case IntLog
    Select Case Value
      Case 4: DocType = "Invoice"
      Case 5: DocType = "Shipment"
      Case 6: DocType = "Credit"
      Case 7: DocType = "Statement"
    Case Else: DocType = "Other"
  End Select
  
  'Case Docuemnt Distribution
  Case DocDist
    Select Case Value
      Case 2: DocType = "Invoice"
      Case 38: DocType = "Shipment"
      Case 3: DocType = "Credit"
      Case 41: DocType = "Statement"
      Case Else: DocType = "Other"
    End Select
  Case Else: DocType = "Other"
End Select
End Function

Open in new window

0
batuesCommented:
I see... there seems to be no rule in the value to make it automatic. Well... your code is suit already. :)
0
RichardSchollarCommented:
You could just amend the SQL to include a Case When structure to return the value:


Richard

SELECT  [Entry No_], 
        [Contact No_], 
        [Contact Company No_], 
        [Document No_],[Date], 
        [Time Of Interaction], 
        [User ID], 
        [Interaction Template Code], 
        [Salesperson Code], 
        [Correspondence Type], 
        Case [Document Type] 
          When 2 Then 'Invoice'
          When 3 Then  'Credit'
          When 4 Then  'Invoice'
          When 5 Then 'Shipment'
          When 6 Then 'Credit'
          When 7 Then  'Statement'
          When 38 Then  'Shipment'
          When 41 Then  'Statement'
          Else 'Other
FROM [Quick Corporate$Interaction Log Entry]  
WHERE [Interaction Group Code] = 'SALES'  
    AND [Entry No_] > '10000'" 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RichardSchollarCommented:
I missed a closing apostrophe off the 'Other' and missed the End keyword - the entire SQl shd have been:


SELECT  [Entry No_],  
        [Contact No_],  
        [Contact Company No_],  
        [Document No_],[Date],  
        [Time Of Interaction],  
        [User ID],  
        [Interaction Template Code],  
        [Salesperson Code],  
        [Correspondence Type],  
        Case [Document Type]  
          When 2 Then 'Invoice' 
          When 3 Then  'Credit' 
          When 4 Then  'Invoice' 
          When 5 Then 'Shipment' 
          When 6 Then 'Credit' 
          When 7 Then  'Statement' 
          When 38 Then  'Shipment' 
          When 41 Then  'Statement' 
          Else 'Other'
        End   
FROM [Quick Corporate$Interaction Log Entry]   
WHERE [Interaction Group Code] = 'SALES'   
    AND [Entry No_] > '10000'

Open in new window

0
bromy2004Author Commented:
Can the Select Case work in Reverse?
WHERE [Document Type] = Case...
0
RichardSchollarCommented:
You can use Case in a WHERE clause but I'm afraid I was unable to understand how you were wanting to use it in your example.
0
bromy2004Author Commented:
The end result is to show alot of data in Excel, then summarize with a Pivot Table.
I dont want to show numbers in the Data, so i can use the Case.
however, when I need to query another table base on the same data my recordset (rst(0).Value) would show "Invoice" instead of the integer that SQL has stored.

My Second query is
SELECT  [Distribution Type],[Distribution Details From],[E-mail],[Fax No_],[Auto Send E-Mail],[Print_E-Mail Option]
FROM [Quick Corporate$Document Distribution Details]
WHERE [Source No_] = 'CT100000'
    AND [Distribution Type]='1'
    AND [Document Type]='1'

So i need to get  "Invoice" to '1' for the SQL command
0
RichardSchollarCommented:
I'm afraid I'm not following - how are you querying SQL server using your recordset?
0
RichardSchollarCommented:
I can't have had enough coffee this morning :)

Do you mean like this:

SELECT * FROM SomeTable
WHERE CASE [Document Type]
          WHEN 2 Then 'Invoice'
          When 3 Then  'Credit'  
          When 4 Then  'Invoice'  
          When 5 Then 'Shipment'  
          When 6 Then 'Credit'  
          When 7 Then  'Statement'  
          When 38 Then  'Shipment'  
          When 41 Then  'Statement'  
          Else 'Other'
        End = '" & rst(0).Value & "'"

The above certainly works on a test in SQL Server 2005.

Richard
0
bromy2004Author Commented:
In excel VBA.
I've added a reference to ActiveX object library.
The recordsets use the SQL command to get the information into recordsets.
0
RichardSchollarCommented:
Did you see my post just before you posted yours?  I think it is what you were referring to.
0
Patrick MatthewsCommented:
bromy2004,

Richard has the CASE syntax nailed (once he remembered the END, anyway :)

In any event, while I am not familiar with Dynamics, I should think that you have a table somewhere in the database that enumerates the different document types, such as:

tblDocTypes

[Document Type]    [Document Type Descr]
--------------------------------------------------------
1                            'Contract'
2                            'Invoice'
3                            'Credit'  
4                            'Invoice'  
5                            'Shipment'
etc.

In that case, your SQL statement becomes much simpler, as shown below.

BTW, be advised that using anything other than letters, digits, and underscores in table or column names is a very bad idea.  I don't know if the names are standard to Dynamics or if someone on your side created them :)

Patrick


SELECT  q.[Entry No_],  
        q.[Contact No_],  
        q.[Contact Company No_],  
        q.[Document No_],[Date],  
        q.[Time Of Interaction],  
        q.[User ID],  
        q.[Interaction Template Code],  
        q.[Salesperson Code],  
        q.[Correspondence Type],  
        d.[Document Type Descr] AS [Document Type]
FROM [Quick Corporate$Interaction Log Entry] q  INNER JOIN
        tblDocTypes d ON q.[Document Type] = d.[Document Type]
WHERE q.[Interaction Group Code] = 'SALES'   
    AND q.[Entry No_] > '10000'

Open in new window

0
bromy2004Author Commented:
Patrick,
Unfortunately with each Tables' "Document Type" (About 10-15 Tables out of the 100 or so) they are all different setups, so there isn't any relationship with any secondary table.

In each table (In Dynamics Nav) there is an "Option" property which is a comma delimited string ( ,Invoice,Shipment)
That is what converts the Integer to the String (or maybe it doesn't convert, just shows the String)
We can create tables, but to change all the relationships in Nav along with Forms, Codeunits, and reports would be extremely time consuming/difficult and expensive.

Sorry Richard, i must have refreshed too early for your last post.

I've tested your SQL statement, and it works
The attached code is the 2 statements that i will be modifying.

I can put together SQL statements with a fair bit of googling but i've never seen the CASE syntax before

I know that SELECT CASE in VBA can handle comma for multiple cases'
i.e.
Select Case Value
Case 1,2,3 : Code
Case 4,5,6 : More Code
End Select

Is that possible in SQL syntax?
'Getting String to Integer'
SELECT  [Entry No_],  
        [Contact No_],  
        [Contact Company No_],  
        [Document No_],[Date],  
        [Time Of Interaction],  
        [User ID],  
        [Interaction Template Code],  
        [Salesperson Code],  
        [Correspondence Type],  
        [Document Type]  
FROM [Quick Corporate$Interaction Log Entry]   
WHERE [Entry No_] > '122935'
	AND CASE [Document Type]
			WHEN 4 THEN 'Invoice'
			WHEN 3 THEN 'Credit'
			ELSE 'Other'
		END ='Invoice'

'Getting Integer to String
SELECT  [Entry No_],  
        [Contact No_],  
        [Contact Company No_],  
        [Document No_],[Date],  
        [Time Of Interaction],  
        [User ID],  
        [Interaction Template Code],  
        [Salesperson Code],  
        [Correspondence Type],  
        Case [Document Type]  
          When 2 Then 'Invoice' 
          When 3 Then  'Credit' 
          When 4 Then  'Invoice' 
          When 5 Then 'Shipment' 
          When 6 Then 'Credit' 
          When 7 Then  'Statement' 
          When 38 Then  'Shipment' 
          When 41 Then  'Statement' 
          Else 'Other'
        End   
FROM [Quick Corporate$Interaction Log Entry]   
WHERE [Interaction Group Code] = 'SALES'   
    AND [Entry No_] > '10000'

Open in new window

0
RichardSchollarCommented:
AFAIk you can't code for ranges (although you can compare values like in the following:

SELECT ID, CASE
                     WHEN [Price] > 0 And [Price] < 10 ThEN 'Cheap'
                     WHEN [Price] >1000 THEN 'Expensive'
                     ELSE 'Reasonable'
                   END
FROM Table

Something to be wary of is if you are using an Access driver (eg if querying Access or using Jet to query Excel) you cannot use Case structures (you need to use Iifs instead (which can achieve a similar result, but are no where near as readable).

Richard
0
RichardSchollarCommented:
^^^ a consequence of the above is that if your integer values were sequential you could do it with Case:

CASE
   When  [Document Type]>2 And [Document Type] <5 THEN 'Invoice'
0
Patrick MatthewsCommented:
The CASE...WHEN...END structure in SQL is not really about branching logic--it is about making choices of return values in recordset.  (Whereas in VB/VBA, Select Case is truly about logical branching.)

It would be helpful if we paused here for you to restate just what it is you are trying to do with your SQL statement.
0
Patrick MatthewsCommented:
Richard,

SQL's CASE does allow for ranges/multiple items, but the syntax is slightly different:



SELECT CASE
    WHEN SomeColumn = 1 OR SomeColumn = 3 OR SomeColumn = 5 THEN 'Odd'
    WHEN SomeColumn = 2 OR SomeColumn = 4 OR SomeColumn = 6 THEN 'Odd'
    ELSE 'Invalid Value' END AS Result
FROM SomeTable

...


SELECT CASE
    WHEN SomeColumn >= 1 AND SomeColumn <= 10 THEN 'Large'
    WHEN SomeColumn > 10 THEN 'Small'
    ELSE 'No soup for you!' END AS Result
FROM SomeTable


You could also use IN instead of multiple OR, but I try to stay away from IN because it can cause SQL Server to ignore indexes; the resulting full table scans erode performance.

Your warning about Access not recognizing CASE is apt; as an alternative I prefer Switch to IIf, as I find embedded IIf expressions to be maddeningly difficult to untangle, and Switch a bit easier to comprehend.

Patrick
0
RichardSchollarCommented:
Hi Patrick

Well I've learned something there - I never knew Switch worked in Access SQL.

Didn't know about the indexing issue with IN() in SQL Server either - does this apply to any statement using IN() or just when part of CASE WHEN?

I suppose you could also create a temporary table to hold the values and their string equivalents and then join to this (if there is no native table for the join).  Would this be worth the hassle Patrick?  Or no better than the Case When?

Richard
0
Patrick MatthewsCommented:
Richard,

If acperkins is still reading, he could answer better than I could just how badly IN interferes with using indexes: I know enough about SQL Server to be dangerous, but I am certainly not at Anthony's level (he is a SQL Server MVP).

I do find it tough to believe that there is not a table enumerating the document types; if there is not, then the whole thing just smacks of poor database design.  Then again, I know nothing about Dynamics beyond how to spell it, so perhaps there is a nuance here that I am just not appreciating :)

Patrick
0
bromy2004Author Commented:
Hi Guys,
You have been very helpful with everything here,
I can't thank you enough
I think you have covered everything i've asked for and more, Thank you.

I'm not 100% sure how to check what driver i'm using but the CASE syntax in Excel VBA works

You have mentioned Access SQL, For this macro and entire project, Access isn't used at all.

Dynamics Nav is loosely based on Access (with the forms and Table etc)

Attached are 3 Images
1. is a Screen shot directly at the Table in Navision (Dynamics Nav)
2. is the setup of the table, with the properties of the "Document Type" Field
3. is a screen shot of the SQL query that returns the information

The different tables are each unrelated in their data but contain information about certain Documents, as different as lemons and oranges, they're both citrus and thats about it.
The Tables i'm working with are ,
1. Interaction Log Entry (as it says, logs interactions with a specific contact when sending documents, Logs Emails Printed docs and Faxed),
2. Document Distribution (Tells Nav what to do with each Document, Email, Fax or Print it)

Both Tables have an integer in the SQL database to indicate what "Document Type" that row of data is referring to, however they both show them slightly different, as in comment 31248800.


A bit more info:
Our interaction Log Entry table has shown several customers with E-mailed Invoices.
The Document Distribution for these customers are setup as E-Mailed invoices.
However (as with any program) there is a bug.
A random subset of customers are getting printed Invoices when they should be emailed.
These customers vary depending on the day.
in 1 day, CustomerA might get 2 Invoices E-Mailed and 2 Printed
while on day 2 CustomerA might get everything right, but CustomerB might get 1 Email and 3 Printed

What I'm Doing:
I'm combining the data from the interaction table, adding int the Document Distribution details (if any) for that Customer/Document Type, to try and find any common data between the errored invoices.

In Short:
The CASE SQL syntax works.
It converts each integer to String and can be reverted back to integers for the other table.
I'm a beginner with SQL,
I know a decent amount about how it relates within Dynamics Nav (how forms get the information from the Tables and how Fields within the Tables can "Lookup" into another table),
I'm ok with simple SQL Statements (SELECT FROM WHERE) but i need to google a bit if i need to use INNER JOIN or anything else.
I've never used CASE before

I'm OK to keep this Q going a little bit longer but i plan to:
31266382 Richard Accept - 175 - CASE return Value
31266382 Richard Assist - 175 - CASE filter value
31274638 Patrick Assist - 150 - OR syntax
Capture.JPG
Capture3.JPG
Capture2.JPG
0
Anthony PerkinsCommented:
>> how badly IN interferes with using indexes<<
You should find that there should not be any difference whatsoever.  SQL Server is smart enough to translate an IN clause to a series of ORs.

Just to be sure, I just tested the following two SQL Statements in the AdventureWorks database:
SELECT  *
FROM    Production.Product
WHERE   Color IN ('black', 'Blue', 'Silver', 'Grey', 'Red')

SELECT  *
FROM    Production.Product
WHERE   Color = 'black'
        OR Color = 'Blue'
        OR Color = 'Silver'
        OR Color = 'Grey'
        OR Color = 'Red'

I then compared the Execution plan and they were identical.  The Predicate for the IN statement had this:

[AdventureWorks].[Production].[Product].[Color]=N'black' OR
[AdventureWorks].[Production].[Product].[Color]=N'Blue' OR
[AdventureWorks].[Production].[Product].[Color]=N'Grey' OR
[AdventureWorks].[Production].[Product].[Color]=N'Red' OR
[AdventureWorks].[Production].[Product].[Color]=N'Silver'

So in general it is safe to use a SQL statement with an IN clause.

Anthony
P.S.  Thanks for the kind words.
0
Patrick MatthewsCommented:
Anthony,

Thanks for that information.  For some reason I had acquired the idea that SQL Server would not use an optimal execution plan when an IN expression is used, and I'm glad you cleared up that misconception for me.

As for the kind words, they are well deserved :)

Patrick
0
RichardSchollarCommented:
Thanks Anthony - that's good to know.  

Richard
0
bromy2004Author Commented:
Thank you to all the Experts.
Your help has been wonderfully easy to follow.

You've made my job easier as well as extending my knowledge of SQL syntax.
0
bromy2004Author Commented:
Thank you to all the Experts.
Your help has been wonderfully easy to follow.

You've made my job easier as well as extending my knowledge of SQL syntax.

Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.