I am trying to write a query to a SQLite database (Spiceworks) in which I
need to transform some data as part of the SELECT statement.
The query I *want* to use looks like this (includes some pseudo code):
SELECT d.name, d.current_user, d.device_type, d.model, d.operating_system,
d.service_pack_major_version,
pd.model as HDModel,
--- check for hex string with leading zero (30) characters
CASE WHEN pd.serial like "303030%" THEN
LTRIM(X'pd.serial', '0')
-- check for hex string with leading space (20) characters
WHEN pd.serial like "202020%" THEN
LTRIM(X'pd.serial')
-- check for any other serial with leading zero characters
WHEN pd.serial like "00%" THEN
LTRIM(pd.serial, '0')
-- fall back to just presenting whatever is in the column
ELSE
pd.serial
END as HDSerial,
cast(pd.firmware as TEXT) as 'HD Firmware',
cast((pd.size / 1000000000) as NUMERIC) as SizeGB,
pd.failure_prediction as 'SMART Enabled',
pd.status as 'SMART Status'
FROM devices d
INNER JOIN physical_disks pd ON (d.id = pd.computer_id)
WHERE SizeGB > 20 AND d.name like "0039%"
ORDER BY d.model, d.name ASC;
Everything in that query works perfectly for me except the X'pd.serial'
lines. I want to put the value of the pd.serial field into the X'...'
format and be returned the decoded hex data. I can manually do it one by
one and get the data I want:
sqlite> select X'3030303030303030313131343330423137454342';
00000000111430B17ECB
But I can't figure out a way do this dynamically for all rows. I'm hoping
there is just a bit of syntax I am missing or another function or method I
should be using. I see lots and lots of documentation about using that
X'...' format, but always in the examples they are static one-off values,
and never as part of a larger SELECT query.
For what it's worth, I did discover that I can concatenate a string to
create the X'...' format I want, but there doesn't seem to be anyway to
actually evaluate that into the converted data. (instead I just get back
the whole X'3030303030303030313131343330423137454342' as a text string.
The concatenation method I used is:
('X''' || pd.serial || '''')
Unfortunately, I don't have the ability to just grab the raw data out of
SQL and then post-process it with PHP or another language for this specific
task (which would be much easier for me). I'm trying to keep this as a
built-in report for Spiceworks, and that limits me to only what a SQL query
can spit out.
Any guidance offered is appreciated!
Thanks,
Jim Sanders
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
