63721_en-US

liliconcom

Two questions about SQL

First question: is there anything like SAS PROC SQL manual for JMP - ie SQL syntax a la JMP? Online?

 

Second question:

I am running the following query:

SELECT t1.Record, t1."plist.dict.array.dict.key._text" AS "key", t1."plist.dict.array.dict.string._text" AS string 

FROM "antiwar-v3"  t1

WHERE t1.Record IN (SELECT DISTINCT t2.Record FROM "antiwar-v3" t2 WHERE t2."plist.dict.array.dict.key._text" = "Object" )

ORDER BY t1.Record ASC, t1."plist.dict.array.dict.key._text" ASC;

 

The key to this is that the column, Record, is calculated - formula based on the previous row.

That same formula is returned as a result - not desired.

Is there a way to "cast" the formula in the source table into what is in the original table?

 

TIA

Re: Two questions about SQL

My incoming data has a variant data structure in an XML file. I am using the XML Import Add-in. A record can be either 2 or 4 fields. So I have added a column, record, which (before I changed it) was a formula that returned a record number depending on the value of one of the columns.

 

When I run a query, a subset of the original table is returned. This apparently changes the record number since the formula (not the original values) seems to be returned (in either the temporary table or result,  not sure) , and run on the returned table, which has the effect of changing the record number.

 

What I was after was a way to do something like what you can do in spreadsheets - transform a column from a formula to it's evaluated value.

 

I think I've figured out another way of getting what I'm after, so in some sense, this is moot to my current problem.

 

I've got 30 years of SAS under my belt - so I tend to the manual mode pretty quickly. Still waiting for a good QBE model. Guess I've got to go back and re-examine the visual version a little more closely.

Re: Two questions about SQL

@gzmorgan0 pointed you in the right direction.

 

JMP Query Builder is both a visual and scriptable way to use SQL to reshape JMP data tables.

 

I don't understand the formula part of your question but the JMP Query Builder does support computed columns so you can probably accomplish what you're looking for.

Re: Two questions about SQL

Two starting points: 

• Help > Scripting Index > All categories > search for SQL
• Help > Books > Using JMP look for sections like these (excepted from JMP 14, Using JMP, bookmarks )

 

I