cancel
Showing results for 
Search instead for 
Did you mean: 

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

rob2
Node Clone

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

3 REPLIES 3

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
}

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)	);

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?

Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

On November 16 and 17 for 24 hours across all timezones, you’ll learn about best practices for beginners and experts alike.