Query results as SQL function arguments

I kind of discovered this by accident. I was going through a manual multi step process to convert some numeric IP addresses in the database to a text dotted quad format, for input into a command line load testing script. I was finding it tedious.

The thing is I had SQL functions available to convert numeric to dotted quad and dotted quad to numeric IP addresses. In the database device table we have the IP address as numeric.

The device table has a unique index on serial number. I was using serial number to identify the devices. What I wanted to do was for a given serial number get the IP address in dotted quad format.

Originally I used a two step approach. First get the numeric IP address for the device serial number, in this example "serial1000".

select ipaddress from device where serial_number = 'serial1000';

Then copy that numeric IP address query result into a second query.

select numberToDottedQuad( result_from_above_query ) from dual;

That worked well enough but it was slow, manual and tedious all of the copying and
pasting. I wanted to combine them into a single query so I only had to enter the serial number and could get the dotted quad IP from that.

I couldn't figure out how to get the original query result to be the argument to the numberToDottedQuad() function. I tried a bit then gave up. This is what the DB wouldn't allow, syntax error.

select numberToDottedQuad(select ipaddress from device where serial_number = 'serial1000') from dual;

After a while though it kind of just came to me. Use the same style syntax as insert .. select statements. Nest the inner result in parenthesis. That was the aha moment and I set back to it. This worked.

select numberToDottedQuad((select ipaddress from device where serial_number = 'serial1000')) from dual;

The trick is that you have to use parenthesis on the inner query so the SQL engine evaluates that first, then it can feed it as the input to the outer query. Which makes sense. This was nice because essentially the same syntax can be used for both Oracle and SQL Server [except the dual part but that's trivial to take out].

If I had created a separate stored function to run the first query and feed the result into the second function then it would have meant separate code for Oracle and SQL Server. This way I didn't have to create a stored function (get the database to do the work) and I can use the same SQL query on Oracle and SQL Server.