CALL RETURN MAP depending on condition with CASE in a subquery (syntax problem)

[rob2]

Good Morning (at least where I am )

I to the following call

CALL { 
          WITH node1
	  MATCH
          (node1: status)
                 -[ :next_status {status: "active"} ]->
                 (node2: status)
          WITH distinct node2, node1
	  RETURN
	  CASE (NOT (node1)-[]->())
	  THEN (node1{ .*})
	  ELSE (node1{ .*, ListNextStatusleft: collect(DISTINCT node2{.*})})
	  END
    AS RESULT_STATUS
}

But I get

Invalid input 'T': expected whitespace, comment, '.', node labels or rel types, '[', '^', '*', '/', '%', '+', '-', "=~", IN, STARTS, ENDS, CONTAINS, IS, '=', '~', "<>", "!=", '<', '>', "<=", ">=", AND, XOR, OR, WHEN, AS, ',', ORDER, SKIP, LIMIT, FROM GRAPH, USE GRAPH, CONSTRUCT, LOAD CSV, START, MATCH, UNWIND, MERGE, CREATE UNIQUE, CREATE, SET, DELETE, REMOVE, FOREACH, WITH, CALL, RETURN, UNION, ';' or end of input (line 8, column 2 (offset: 161))
"	THEN (node1{ .*})"
  ^

What I want to archiev:
At the moment (without the case)

...
RETURN (node1{ .*, ListNextStatusleft: collect(DISTINCT node2{.*})})
...

it works and I get

• Current status_12
  ** next_status_27
  ** next_status_65
• Current status_17
  ** next_status_34
  ** next_status_73

BUT if a CurrentStaus (node1) is an EndStatus (= has no NextStatus) the info about the Node is not inlcuded in the response

I need

• Current status_12
  ** next_status_27
  ** next_status_65
• Current status_69
• Current status_17
  ** next_status_34
  ** next_status_73

where 69 has NO next status
any way to achive this?
Thanks rob

[michael_hunger]

You missed a WHEN in your CASE WHEN cond THEN expr ELSE expr END

CALL { 
          WITH node1
	  MATCH
          (node1: status)
                 -[ :next_status {status: "active"} ]->
                 (node2: status)
          WITH distinct node2, node1
	  RETURN
	  CASE WHEN (NOT (node1)-[]->())
	  THEN (node1{ .*})
	  ELSE (node1{ .*, ListNextStatusleft: collect(DISTINCT node2{.*})})
	  END
    AS RESULT_STATUS
}

[rob2]

HI Michael

OK thanks - syntax is working now but the result is not as exepected.
I still don't get EndStatusNodes (= have no next) in the result.

First of all changed your solution just a little bit.

1. only checking for rel "next_status" and 2) no "with distinct" and 3) with a simplified main routine 4) only giving back the result of the subroutine (in the realcase this is different)

MATCH (NodeProc: process) -[ :start_status]-> (NodeStartStatus: status) 
CALL { WITH NodeStartStatus 
	  MATCH
          (node1: status)
                 -[ :next_status]->
                 (node2: status)
	  RETURN
	  CASE WHEN (NOT (node1)-[:next_status]->())
	  THEN (node1{ .*})
	  ELSE (node1{ .*, ListNextStatusleft: collect(DISTINCT node2{.*})})
	  END
    AS RESULT_STATUS
}
return RESULT_STATUS;

At the end there is some debug data to create this

The node with the _exid: 586c-5975e0-bv455e5 has only an INgoing next_status relationship. No OUTgoing. So it is an EndStatus , but the modified query above only returns 7 nodes of typ status - all of them have ListNextStatusleft INfo included.

"Started streaming 7 records after 4 ms and completed after 20 ms"

There are 8 nodes with label status in the demo data (only on has no outgoing next_status rel)

match (n:status) return n;
Displaying 8 nodes, 10 relationships

Where is my error? Do i have to use OPTIONAL MATCH?

thanks rob

CREATE DEBUG DATA
(put in in one big line hope that is ok or is ther any other way to provide debug data?)

:param ProcessName  => 'TEST';		

CREATE ((pr:process{_exid: 'f31e-5972f6-bv45c4f', name: $ProcessName + ' Process'})	-[:start_status ]->	(status1:status {_exid: '94b2-597923-bv454a3',name: 'new - '+$ProcessName})	-[r12:next_status ]->	(status2:status {_exid: '597a13-597d9e-bv641aa',name: 'incomplete - '+$ProcessName})	-[r23:next_status ]->	(status3:status {_exid: 'c315-597ab9-bv4591f',name: 'complete - '+$ProcessName})	-[r34:next_status ]->	(status4:status {_exid: '684d-5979ce-bv45166',name: 'verified - '+$ProcessName})	-[r45:next_status ]->	(status5:status {_exid: '586c-5975e0-bv455e5',name: 'inactive - '+$ProcessName})	),		((status2)				-[r26:next_status ]->	(status6:status {_exid: '0309-5976f2-bb14',name: 'blocked incomplete- '+$ProcessName})	),			((status6)		-[r62:next_status ]->	(status2)	),		((status3)				-[r39:next_status ]->	(status7:status {_exid: 'd45a-59708c-83dd',name: 'blocked complete- '+$ProcessName})	),			((status7)		-[r73:next_status ]->	(status3)	),	((status4)				-[r48:next_status ]->	(status8:status {_exid: '308c-597129-8a2f',name: 'blocked verified- '+$ProcessName})	),			((status8)		-[r84:next_status ]->	(status4)	);

[michael_hunger]

Actually one issue that could occur here is the conditional aggregation/grouping.

Perhaps you can try to pull that collect in a WITH before the RETURN and try again.

Which neo4j version are you using?