:auto transaction doesn't run as described in the manual

You can change the return values from all 1's to different values and return them. You can then see which cypher statements executed. maybe add a 'limit 10' or so after the first 'where' clause so it doesn't execute all the rows.

The above code you pasted does not have the 'return' statements in the apoc 'when' cypher. I was able to get both 'when' statements to execute in my prototype only when I returned something from the first 'when' cypher; otherwise, the query stops since it produces no results.

Please, show me what you mean with a return statements in the apoc.when cypher.
Anyway, I can't try now because neo4j is not responding: it' loading millions transactions using your other query and, even I split the input file in chunks of 10000lines (with shell split) and I'm running in the cypher shell, the browser doesn't respond. I'll have to wait it will finish to make more tests!

Try this code. See in the each cypher statement in each 'with' clause, it terminates with a 'RETURN 1' statement. This causes the apoc 'when' method to return a value in the 'yield value' statement, which gets merged with the current result. Without returning a value, the current result gets merged with 'null' and the query stops since there is no result to process. At least this is my hypothesis. I got it to work locally when I returned a value.

:auto 
LOAD CSV WITH HEADERS FROM 'file:///Book1.csv' AS row FIELDTERMINATOR ';'
WITH row
WHERE row.id IS NOT NULL

CALL {
    WITH row

    MERGE (transaction:Transaction { id : toInteger(row.id) })
		SET transaction = row,
			transaction.uuid = apoc.create.uuid(),
            transaction.id = toInteger(row.id),
            transaction.consumer_id = NULL // remove the attribute no more used

    WITH transaction, row

    OPTIONAL MATCH (bankAccount:BankAccount {id: toInteger(row.bank_account_id)})

    CALL apoc.do.when(bankAccount IS NOT NULL, 
    'WITH $tran as tran, $bankAcct as bankAcct MERGE (tran)-[h:HAS]->(bankAcct) RETURN 1', 
    'WITH $tran as tran  MERGE (error:Error) MERGE (tran)-[h:HAS_NO_BANK_ACCOUNT]->(error) RETURN 1',
    {tran: transaction, bankAcct: bankAccount}) yield value

    with transaction, row

    OPTIONAL MATCH (organisation:Organisation {id: toInteger(row.organisation_id)})

    CALL apoc.do.when(organisation IS NOT NULL, 
    'WITH $tran as tran, $org as org MERGE (tran)-[h:BELONGS_TO]->(org) RETURN 1',
    'WITH $tran as tran MERGE (error:Error) MERGE (tran)-[h:HAS_NO_ORGANISATION]->(error) RETURN 1',
    {tran: transaction, org: organisation}) yield value

    RETURN 1

} IN TRANSACTIONS

RETURN 1
1 Like

Understood: will try ASAP. Thank you anyway!

@glilienfield @bennu_neo @Rcolinp Justr for your info, I loaded 11 million transactions of different kind, using the latest approach proposed by @glilienfield.

This approach using apoc turned out to be by far the most readable one (after a bit of a sprucing up of the query!)

This is the result:

LOAD CSV WITH HEADERS FROM 'file:///transactions.csv' AS row FIELDTERMINATOR ';'

CALL {

WITH row

WITH row
    WHERE row.transactionid IS NOT NULL

MERGE (transaction:Transaction { transactionid : row.transactionid })
        SET transaction = row,
            transaction.uuid = apoc.create.uuid(),
            transaction.consumer_id = NULL // remove the attribute no more used

    with transaction, row
    
    OPTIONAL MATCH (organisation:Organisation {id: toInteger(row.organisation_id)})

    CALL apoc.do.when(organisation IS NOT NULL, 
    'WITH $tran as tran, $org as org 
        MERGE (tran)-[h:TRANSACTION_BELONGS_TO_ORGANISATION]->(org) 
        RETURN 1',
    'WITH $tran as tran 
        MERGE (error:Error) 
        MERGE (tran)-[h:TRANSACTION_HAS_NO_ORGANISATION]->(error) 
        RETURN 1',
    {tran: transaction, org: organisation}) yield value AS X

    WITH transaction, row

    OPTIONAL MATCH (bankAccount:BankAccount {id: toInteger(row.bank_account_id)})

    CALL apoc.do.when(bankAccount IS NOT NULL, 
    'WITH $tran as tran, $bankAcct as bankAcct 
        MERGE (tran)-[h:TRANSACTION_HAS_BANK_ACCOUNT]->(bankAcct)
        RETURN 1', 
    'WITH $tran as tran  
        MERGE (error:Error) 
        MERGE (tran)-[h:TRANSACTION_HAS_NO_BANK_ACCOUNT]->(error) 
        RETURN 1',
    {tran: transaction, bankAcct: bankAccount}) yield value AS Y

return transaction

} IN TRANSACTIONS OF 5000 ROWS

return count(transaction)

The story will follow with a new discussion .... but this is an achieved result I'd like to share!

Thank you to everybody!

Super @paolodipietro58 !

So @glilienfield is suggesting APOC solutions now. :wink:

1 Like

Well, we make some trial, and the APOC.do.when looks better, also in terms of query readability.

I agree, it is much more readable than the other approach.

@glilienfield @bennu_neo @Rcolinp I have a little good new on this:

I discovered tha you can remove the WITH statement inside and this further simplify the query, just keep attention to the names!

    CALL apoc.do.when(organisation IS NOT NULL, 
    '   MERGE (tran)-[h:TRANSACTION_BELONGS_TO_ORGANISATION]->(org) 
        RETURN 1',
    '   MERGE (error:Error) 
        MERGE (tran)-[h:TRANSACTION_HAS_NO_ORGANISATION]->(error) 
        RETURN 1',
    {tran: transaction, org: organisation}) yield value AS X

That is very interesting, so I looked at the source code to understand how that works. It turns out the apoc procedure passes the parameter map to the neo4j API 'execute' method as parameters, so they can be referenced as parameters, i.e. with a '$' sign. It also turns out the the procedure creates a 'WITH' clause with all the parameter values individually assigned to their parameter's names, i.e. 'WITH value1 as key1, value2 as key2, ...' This 'WITH' clause is prepended to both the 'if' cypher and the 'else' cypher before they are passed to the 'execute' method. This allows the parameter values to be referenced directly as cypher variables. In summary, the apoc.do.when allows passed parameters to be references as parameters (with '$') and directly as cypher variables. This explains why both methods work. This approach is used in all of the apoc cypher methods that pass a parameter map.

Good find.