CODEWITHSUNDEEP
sqlsql-server
Chris
Chris

Reputation: 93

Last record then remove text from SQL statement and replace with blank or another character

I have a SQL select statement from SQL server that puts text together from a dynamic table and creates a union statement. I then copy this statement into Oracle SQL Developer to hit an Oracle database to get counts or min/max columns, or whatever. My query is similar to this:

'Select ' + a.column1 + 'from' + a.tablename + 'where ' + a.column1 + '= 123' + 'UNION ALL' 
from Mytable a where a.tablename = 'MYTABLENAMEHERE'

So I then get a list of 20 tables then cut/paste this into Oracle for results.

My question is (and this may be a dumb question I just wanted to know how to do it in SQL) is that the output on the last record has the 'UNION ALL' at the end of it without a ";" that Oracle needs so I always have to remove it on the last record.

How do I tell SQL when it's the last record to replace the 'UNION ALL' and replace with a ";" terminator statement so that when I cut and paste it, I don't have to remove it each time?

Just curious from the experts!

Upvotes: 0

Views: 177

Answers (1)

JC Castro
JC Castro

Reputation: 51

You can check with a case sentence if the current record is the last one and, in that case, not print the UNION ALL:

select 'Select ' + a.column1 + 'from' + a.tablename + 'where ' + a.column1 + '= 123'
+ case when (select count(*) from Mytable where tablename=a.tablename)=ROW_NUMBER() OVER(order by a.tablename desc) then ';' else ' UNION ALL' end 
from Mytable a where a.tablename = 'MYTABLENAMEHERE'

Upvotes: 1

Related Questions