Forum Discussion
Adding index column based on another query, issues with data types
are any of your queries set to be staged? specifically the MaxKey_Accounts query
when you stage a query it needs to be saved as a table. If you had a scalar value, like a number, simply referring to that query would yield the scalar value (Number). However, if its a table (like it was in this case because the query was staged) then you'd need to do the table navigation to reach the value that you're looking for. It could be something like QueryName[ColumnName]{0} where the 0 indicates that you just want the first row.
Number.From works against scalar values and not complex ones (like records, tables, lists and such). More info on the signature of the function below:
are any of your queries set to be staged? specifically the MaxKey_Accounts query
Yes, they both were as default. Changed the MaxKey_Accounts query to not be, and it worked! Thanks a lot! 🙂
Note: it did however require some other troubleshooting as it did not work at first, but that was due to the DROP and CREATE table functions in the warehouse. When I first created the tables I used:columnName varchar NULL,... and for reasons I did a DROP and CREATE in which the automated code created did this:
columnName varchar(1) NULL,... which caused other insert errors since all data was more than 1 varchar big (not related to the index column) but I'm leaving this info here for others so they don't make the same mistake I did.
Follow-up question: if staging the MaxKey_Accounts returns it as a table in the index column creation, how come Number.From did not work in converting it into a number?when you stage a query it needs to be saved as a table. If you had a scalar value, like a number, simply referring to that query would yield the scalar value (Number). However, if its a table (like it was in this case because the query was staged) then you'd need to do the table navigation to reach the value that you're looking for. It could be something like QueryName[ColumnName]{0} where the 0 indicates that you just want the first row.
Number.From works against scalar values and not complex ones (like records, tables, lists and such). More info on the signature of the function below:
Thanks a lot! 😄
