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.
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