[sql] sql how to cast a select query

Is it possible to apply the cast function to a select statement? If yes, how? I have a query that returns a number which I have to use a string to get other info's from another table.

Yes you can do.

Syntax for CAST:

CAST ( expression AS data_type [ ( length ) ] )

For example:

CAST(MyColumn AS Varchar(10))

CAST in SELECT Statement:

Select CAST(MyColumn AS Varchar(10)) AS MyColumn
FROM MyTable

See for more information CAST and CONVERT (Transact-SQL)

If you're using SQL (which you didn't say):

select cast(column as varchar(200)) from table 

You can use it in any statement, for example:

select value where othervalue in( select cast(column as varchar(200)) from table)
from othertable

If you want to do a join query, the answer is here already in another post :)

I interpreted the question as using cast on a subquery. Yes, you can do that:

select cast((<subquery>) as <newtype>)

If you do so, then you need to be sure that the returns one row and one value. And, since it returns one value, you could put the cast in the subquery instead:

select (select cast(<val> as <newtype>) . . .)

And when you use a case :

CASE
WHEN TB1.COD IS NULL THEN
    TB1.COD || ' - ' || TB1.NAME
ELSE
    TB1.COD || ' - ' || TB1.NAME || ' - ' || TB.NM_TABELAFRETE
END AS NR_FRETE,