I’m using a query to select all columns of a table and separate them by a comma(‘,’) but in case if value of a column is null then I’ll use string ‘null’ in place of a value.
The query I am trying is –
SELECT CONCAT(NVL(ID,'null'),',',NVL(NAME,'null'),',',NVL(ROLL_NO,'null'))
FROM DUAL
Expected result-
1,john,123
2,josh,null
I intend to run this query on a spark temporary table. But before that I tried running it on sql developer.
But I’m getting ORA-00909 : invalid number of arguments error. I can’t find where I’m going wrong here.
Extra question (not necessary to answer) : Is there a way to concatenate all columns by not writing columns manually? I know there is a function concat_ws in spark.sql and oracle, where we can use a delimiter but it also neglects null value instead of replacing them with ‘null’ string but again I’ve to write all columns manually even in concat_ws.
>Solution :
Oracle’s CONCAT function accepts only 2 arguments.
But there is the concat operator, ||, which does what you want:
SELECT NVL(ID,'null') || ',' || NVL(NAME,'null') || ',' || NVL(ROLL_NO,'null')
FROM DUAL