How to change data type when importing data in Query Builder?

[#1]

Hello. I'm using the Query Builder in JMP 13 to get my SQL data into JMP.  There is a very informative white paper on the Query Builder tool that shows how to change the modeling type and format, but I don't see anything about how to chang the data type.  I'm wondering if it's possible to change the data type through Query Builder?

My data is store as a varbinary data type in SQL.  When I use the Query Builder tool to open the data, JMP interprets varbinary as a Numeric value. However, I would like to import it into JMP as a Character data type since all the data dissapears when JMP interprets it as Numeric. 

The SQL data I'm trying to import is a string of hexadecimal digits 778 characters long and begins with the prefix '0x'. When I import this data in JMP as Numeric, then I get a data table with all blank cells. A work around I've found is exporting the SQL data as a.csv, then importing it to JMP by using file->open->data with preview.  In the 'data with preview' dialog I can easily change the data type from numeric to character, but I would like to automate this with Query Builder or an Open Database() command.  

I've found that once I get my hexadecimal data in JMP as a character type I can easily work with it and parse it using the Hex to Number() function. 

I hope this makes sense.  Thank you for your feedback and help!

-Nick

[#2]

I normally write my queries separately and then use executesql(), so there may be a better way--but it looks like you can create your query in the builder and then convert to a custom SQL (hotspot in top left near Query Name in JMP12.1). From there you can change the query directly and cast the relevant data as a character type. I tested this for a few different numeric-->character data types (though not yours exactly) and it seemed to work OK. It doesn't seem like you can go back to the interactive version of the query though so you should do this when you are done building.

[#3]

Thanks! Your comment put me down the correct path.  I ended up building a custom SQL query to type cast it to character and it worked.  The extra code looks something like this:

select 

convert(VARCHAR(n), myColumnName, 2)
from

myDataTableName;

The number 2 in the convert command skips the '0x' hexadecimal prefix. 

[#4]

You may have already tried this, but after you run the query and see the JMP data type(s) you can left click on the data type symbol to change the data type.  You will have the standard data type choices (continuous, character, or ordinal) to choose from.

HTH

Bill