Column Concatenation in Oracle [closed]

I’m trying to Concatenate these three columns into one but having the error, any help?

 WITH ABC AS
    (select tsk.task_number || ' / '|| tsk.task_status|| ' / '|| tsk.summary,
            CSI.creation_date AS creation_date
    FROM csf_ct_task_assignments tsa,CSF_DEBRIEF_HEADERS db, csf_ct_tasks tsk ,CSI_ITEM_INSTANCES csi
    where 1=1
    and tsk.customer_product_id = csi.instance_id 
    and tsk.INCIDENT_CUSTOMER_ID = csi.OWNER_PARTY_ID
    and tsa.Task_Assignment_Id=db.Task_Assignment_Id(+)
    and tsk.task_id = tsa.task_id (+)
    and tsk.task_type like '%Termination%'
    and tsk.task_status_id<>7
    and (SELECT  actual_shipment_date
    FROM oe_order_lines_all
    WHERE line_id= csi.last_oe_order_line_id)
    <= tsk.creation_date
    and rownum=1
    ) AS termination_call_dtls,

[Error] : Execution (14: 74): ORA-00936: missing expression

Answer

but i want to concatenate those columns in termination_call_dtls!!

The problem, as @APC mentioned, is that you’re trying to alias the WITH block. It isn’t clear that you need subquery factoring at all here; you can just do:

select tsk.task_number || ' / '|| tsk.task_status|| ' / '|| tsk.summary AS termination_call_dtls,
  CSI.creation_date AS creation_date
FROM csf_ct_task_assignments tsa,CSF_DEBRIEF_HEADERS db, csf_ct_tasks tsk ,CSI_ITEM_INSTANCES csi
where 1=1
and tsk.customer_product_id = csi.instance_id 
and tsk.INCIDENT_CUSTOMER_ID = csi.OWNER_PARTY_ID
and tsa.Task_Assignment_Id=db.Task_Assignment_Id(+)
and tsk.task_id = tsa.task_id (+)
and tsk.task_type like '%Termination%'
and tsk.task_status_id<>7
and (SELECT  actual_shipment_date
FROM oe_order_lines_all
WHERE line_id= csi.last_oe_order_line_id)
<= tsk.creation_date
and rownum=1

with termination_call_dtls as a column alias on the first line.

As also mentioned in comments you should consider using modern join syntax instead of the old and Oracle-only syntax you have now; and the subquery might be better as another join, something like:

select tsk.task_number || ' / '|| tsk.task_status|| ' / '|| tsk.summary as termination_call_dtls,
  csi.creation_date
from csf_ct_tasks tsk
join csi_item_instances csi on csi.instance_id = tsk.customer_product_id
and csi.owner_party_id = tsk.incident_customer_id
join oe_order_lines_all oola on oola.line_id = csi.last_oe_order_line_id
and ools.actual_shipment_date <= tsk.creation_date
left join csf_ct_task_assignments tsa on tsa.task_id = tsk.task_id
left join csf_debrief_headers db on db.task_assignment_id = tsa.task_assignment_id
where tsk.task_type like '%Termination%'
and tsk.task_status_id != 7
and rownum = 1

Either way you can still use the query as a CTE if you need to – if it’s part of a larger and more complicated query than you showed.

It isn’t clear why you have the (outer) joins to csf_ct_task_assignments or csf_debrief_headers, since you don’t use any columns from those tables anyway. Those joins can probably just be removed.

You should also be aware that and rownum = 1 is going to give you an indeterminate row back, assuming you get multiple rows without it. Normally you would have an inline view that includes an order by clause, and then apply the rownum filter afterwards to limit the results, e.g. (guessing that you want the earliest creation date):

select termination_call_dtls, creation_date
from (
  select tsk.task_number || ' / '|| tsk.task_status|| ' / '|| tsk.summary as termination_call_dtls,
    csi.creation_date
  from csf_ct_tasks tsk
  join csi_item_instances csi on csi.instance_id = tsk.customer_product_id
  and csi.owner_party_id = tsk.incident_customer_id
  join oe_order_lines_all oola on oola.line_id = csi.last_oe_order_line_id
  and ools.actual_shipment_date <= tsk.creation_date
  where tsk.task_type like '%Termination%'
  and tsk.task_status_id != 7
  order by csi.creation_date
)
where rownum = 1

From 12c there are other mechanisms to simplify that a bit. Or using aggregation:

select tsk.task_number || ' / '|| tsk.task_status|| ' / '|| tsk.summary as termination_call_dtls,
  min(csi.creation_date) as creation_date
from csf_ct_tasks tsk
join csi_item_instances csi on csi.instance_id = tsk.customer_product_id
and csi.owner_party_id = tsk.incident_customer_id
join oe_order_lines_all oola on oola.line_id = csi.last_oe_order_line_id
and ools.actual_shipment_date <= tsk.creation_date
where tsk.task_type like '%Termination%'
and tsk.task_status_id != 7
group by tsk.task_number, tsk.task_status, tsk.summary