0 values after casting varchar(50) to float in named query

Ignition
,
1

Hello,
In my named query I casted column ExternalLeakTestBackgroundResultmbarls which is varchar(50) to float. My column contain scientific notation of values.

In SSMS it ok but in Ignition don`t.

My named query is:

SELECT 
	TimeStamp,
	[ExternalLeakTestBackgroundResultmbarls],
	CAST(REPLACE([ExternalLeakTestBackgroundResultmbarls], ',' , '.') AS FLOAT) AS 'CASTED TO FLOAT'
FROM 
	[SYS10064_X20].[dbo].[Result]
WHERE
	TimeStamp > '2024-06-24' and TimeStamp < '2024-06-25';

In SSMS it looks like that:

SSMS

and It`s fine but, in Ignition it looks like that:

IGNI
IGNI1345×688 145 KB

Can anyone solve this?

2

You should try testing it in scripting. I don't know if the query previewer can show such small numbers.

Try the following code in your console. runNamedQuery needs to be filled in.

def printDataset(data):
	"""
	Prints the dataset or pyDataset pretty in a console log
	Args:
		data (dataset or pyDataset)
	returns nothing, but does print some info
	"""
	if data and data.rowCount > 0:
		# Determine the maximum width of each column
		columnWidths = []
		for colIndex in range(data.columnCount):
			columnName = data.getColumnName(colIndex)
			maxWidth = len(columnName)
			for rowIndex in range(data.rowCount):
				value = str(data.getValueAt(rowIndex, colIndex))
				maxWidth = max(maxWidth, len(value))
			columnWidths.append(maxWidth)
	
		# Print the header row
		headerRow = ""
		for colIndex in range(data.columnCount):
			columnName = data.getColumnName(colIndex)
			headerRow += columnName.ljust(columnWidths[colIndex] + 2)
		print(headerRow)
	
		# Print a separator
		print('-' * len(headerRow))
	
		# Print the data rows
		for rowIndex in range(data.rowCount):
			rowStr = ""
			for colIndex in range(data.columnCount):
				value = str(data.getValueAt(rowIndex, colIndex))
				rowStr += value.ljust(columnWidths[colIndex] + 2)
			print(rowStr)
	else:
		print("The dataset is empty.")
		return

dataset = system.db.runNamedQuery("YOURPATH")
printDataset(dataset)
3

You probably shouldn't be storing those as strings in the first place.
If there's anything you can do about this, I'd suggest trying one of these:

1 Like