cancel
Showing results for 
Search instead for 
Did you mean: 

Error using CASE WHEN statment

gorkasalauniver
Node Clone

Hello there, I'm trying to use a CASE WHEN statement, but i get this error:

Invalid input 'S': expected 'l/L'

What does 'S' mean?

1 ACCEPTED SOLUTION

adam_cowley
Neo4j
Neo4j

There are a couple of options; you could use apoc.do.when to simulate this relationship, or you could use apoc.create.relationship to create the dynamic relationship type. The foreach then executes the code inside once if the predicate is true or does nothing if it's false.

MERGE (pre:Predicate{text:predicate.text, type: predicate.type})
WITH r, pre
CALL apoc.merge.relationship(r, 'PREDICATES_'+ predicate.type, {}, {}, pre) YIELD rel

Or if you don't like apoc then there is the "foreach hack" where you use the case statement to either return an array with one item for true or an empty array for false:

FOREACH (_ IN CASE WHEN predicates.predicate = 'T' THEN [1] ELSE [] END |
  MERGE (r)-[:PREDICATES_T]->(pre)
)

View solution in original post

8 REPLIES 8

adam_cowley
Neo4j
Neo4j

This is a syntax error. It means that where you have a character S somewhere in your cypher statement, the query parser is expecting something else. Can you post the entire query?

gorkasalauniver
Node Clone

Thank you for your attention Adam.

I´m trying to import a json document, this document:

[
{
"id" : "X8",
"content":{
		"text": "if {R} then {S}",
		"predicates" : [
			{
			"predicate": { "text": "A == 1", "type" : "R" }
			} ,
			{
			"predicate": { "text": "A=A+1", "type" : "S"}
			} ,
			{
			"predicate": "None"
			},
			{
			"predicate": "None"
			}
		]
	},
}
]

This file is an example that they gave to me, so now I'm trying to put this with nodes and relations.

As you can see some 'predicate' values are none, but delete them in the next command line:

CALL apoc.load.json("first_example.json") YIELD value AS requirement
UNWIND requirement.content.predicates as predicates
WITH apoc.map.clean(predicates,[], ['None']) as predicates
UNWIND predicates.predicate AS predicate
CASE predicate.type
WHEN 'R' THEN
MERGE (pre_r:Predicate{text:predicate.text, type: predicate.type})
WHEN 'S' THEN
MERGE (pre_s:Predicate{text:predicate.text, type: predicate.type})
WHEN 'T' THEN
MERGE (pre_t:Predicate{text:predicate.text, type: predicate.type})
WHEN 'U' THEN
MERGE (pre_u:Predicate{text:predicate.text, type: predicate.type})

I goes well until it reach to the case part.

If you could help me I would be so grateful

The case statement doesn't work in this way. It is used to return a value based on a condition, something like:

CASE 1 WHEN 1 THEN 'one' ELSE 'not one' END

It looks like you're trying to do the same merge regardless of what the value is anyway - why not something like this?

CALL apoc.load.json("first_example.json") YIELD value AS requirement
UNWIND requirement.content.predicates as predicates
UNWIND predicates.predicate AS predicate
WITH predicate WHERE predicate <> "None"
MERGE (pre:Predicate{text:predicate.text, type: predicate.type})

gorkasalauniver
Node Clone

Yeah Adam, I thought something like that, the problem is that later I would like to add some relations based on the predicate.type. I would like to add a node with the id of the requirement and then realate each one of them according to their type.

CALL apoc.load.json("first_example.json") YIELD value AS requirement
MERGE (r:Requirement{id:requirement.id})
UNWIND requirement.content.predicates as predicates
WITH apoc.map.clean(predicates,[], ['None']) as predicates
UNWIND predicates.predicate AS predicate
CASE predicate.type
WHEN 'R' THEN
MERGE (pre_r:Predicate{text:predicate.text, type: predicate.type})
MERGE (r)-[:PREDICATES_R]->(pre_r)
WHEN 'S' THEN
MERGE (pre_s:Predicate{text:predicate.text, type: predicate.type})
MERGE (r)-[:PREDICATES_S]->(pre_s)
WHEN 'T' THEN
MERGE (pre_t:Predicate{text:predicate.text, type: predicate.type})
MERGE (r)-[:PREDICATES_T]->(pre_t)
WHEN 'U' THEN
MERGE (pre_u:Predicate{text:predicate.text, type: predicate.type})
MERGE (r)-[:PREDICATES_U]->(pre_u)

If there's a solution it would be great, however I would be grateful with your solution.

adam_cowley
Neo4j
Neo4j

There are a couple of options; you could use apoc.do.when to simulate this relationship, or you could use apoc.create.relationship to create the dynamic relationship type. The foreach then executes the code inside once if the predicate is true or does nothing if it's false.

MERGE (pre:Predicate{text:predicate.text, type: predicate.type})
WITH r, pre
CALL apoc.merge.relationship(r, 'PREDICATES_'+ predicate.type, {}, {}, pre) YIELD rel

Or if you don't like apoc then there is the "foreach hack" where you use the case statement to either return an array with one item for true or an empty array for false:

FOREACH (_ IN CASE WHEN predicates.predicate = 'T' THEN [1] ELSE [] END |
  MERGE (r)-[:PREDICATES_T]->(pre)
)

Thank you Adam, I've made it!!

I'm a complete noob going thru the examples and I get the Invalid input 'S' : expected 'l/L' error for even the most basic example such as

Match n
CASE
WHEN n.eyes = 'blue' THEN 1
ELSE 3
END

Is it POSSIBLE that there is a parsing problem? - I'm using Desktop 1.2.6 (.1668), this is straight out of the examples and on the ref-sheet and I know of at least one other person with the same problem. The 'expected 'l/L' part would indicate that the parser is looking for "CALL" and not looking for "CASE"

Try just
Match (n)
CASE

The parser fails the 'S'