How to write these 2 queries in a best possible way?

[akshat_mittal]

Hello Team,

Need your help in sharing a best possible query or possible to combine these 2 queries in 1 query.

Here c.EQUIP_NATIVENAME can be either IPV6 (2400:122:44:aec2:33e) or IPV4 ( 10.127.12.123 )

Query 1)
MATCH (sc:SITE_CODE_TABLE)-[:site2sitecode]-(s:SITE)-[utilsite2container]-(c:CONTAINER)
WHERE c.EQUIP_TYPE = 'ESC' and s.SITE_TYPE IN
['AG2-OTN','AG3-IDC','CLS','AG3','SAG2'] AND c.EQUIP_VENDOR = 'EMERSON NETWORK POWER' AND c.EQUIP_NATIVENAME CONTAINS ':'
RETURN
'GENERIC .x'+toLower(sc.SITE_CIRCLE_CODE_OC)+'.'+replace(c.EQUIP_NATIVENAME,':','_') AS MO,
'.oc.'+s.SITE_REGION+'_LFLRTU' AS OC
LIMIT 10

Query 2)
MATCH (sc:SITE_CODE_TABLE)-[:site2sitecode]-(s:SITE)-[utilsite2container]-(c:CONTAINER)
WHERE c.EQUIP_TYPE = 'ESC' and s.SITE_TYPE IN
['AG2-OTN','AG3-IDC','CLS','AG3','SAG2'] AND c.EQUIP_VENDOR = 'EMERSON NETWORK POWER' AND c.EQUIP_NATIVENAME CONTAINS '.'
RETURN 'GENERIC .x'+toLower(sc.SITE_CIRCLE_CODE_OC)+'.'+replace(c.EQUIP_NATIVENAME,'.','_') AS MO,
'.oc.'+s.SITE_REGION+'_LFLRTU' AS OC
LIMIT 10

Thanks in Advance !!

Regards
AM

[Cobra]

Hello @akshat.mittal

What is the difference between your two queries? They are the same.

Regards,
Cobra

[akshat_mittal]

Hi Cobra,

Both the queries have a difference in replace function.
Query 1 , having replace as colon ( : )
Query 2 , having replace as dot ( . )

Regards
AM

[Cobra]

You can use a sub-query:

MATCH (sc:SITE_CODE_TABLE)-[:site2sitecode]-(s:SITE)-[utilsite2container]-(c:CONTAINER)
WHERE c.EQUIP_TYPE = 'ESC'
AND s.SITE_TYPE IN ['AG2-OTN','AG3-IDC','CLS','AG3','SAG2']
AND c.EQUIP_VENDOR = 'EMERSON NETWORK POWER'
AND c.EQUIP_NATIVENAME CONTAINS ':'
WITH sc, c, s
CALL {
    WITH sc, c, s
    RETURN 'GENERIC .x'+toLower(sc.SITE_CIRCLE_CODE_OC)+'.'+replace(c.EQUIP_NATIVENAME,':','_') AS MO,
           '.oc.'+s.SITE_REGION+'_LFLRTU' AS OC
    LIMIT 10
    UNION
    WITH sc, c, s
    RETURN 'GENERIC .x'+toLower(sc.SITE_CIRCLE_CODE_OC)+'.'+replace(c.EQUIP_NATIVENAME,'.','_') AS MO,
           '.oc.'+s.SITE_REGION+'_LFLRTU' AS OC
    LIMIT 10
}
RETURN MO, OC

Regards,
Cobra

[akshat_mittal]

Hi Cobra,

Thanks a lot !!

Any possibility of doing some changes in replace clause rather than using sub-query approach ??

Regards
AM

[Cobra]

Do you want to return both on the same line?

[akshat_mittal]

Yes !! I want them on same line and after that i will SET their value to some specific variable.

[Cobra]

Why don't you do?

MATCH (sc:SITE_CODE_TABLE)-[:site2sitecode]-(s:SITE)-[utilsite2container]-(c:CONTAINER)
WHERE c.EQUIP_TYPE = 'ESC'
AND s.SITE_TYPE IN ['AG2-OTN','AG3-IDC','CLS','AG3','SAG2']
AND c.EQUIP_VENDOR = 'EMERSON NETWORK POWER'
AND c.EQUIP_NATIVENAME CONTAINS ':'
RETURN 'GENERIC .x'+toLower(sc.SITE_CIRCLE_CODE_OC)+'.'+replace(c.EQUIP_NATIVENAME,':','_') AS MO_1,
       'GENERIC .x'+toLower(sc.SITE_CIRCLE_CODE_OC)+'.'+replace(c.EQUIP_NATIVENAME,'.','_') AS MO_2,
       '.oc.'+s.SITE_REGION+'_LFLRTU' AS OC
LIMIT 10

[akshat_mittal]

Here Catch is c.EQUIP_NATIVENAME CONTAINS either COLON or DOT.

[Cobra]

I'm sorry, I didn't see it.
Do you want to have 10 of each or only 10 results where you have for example 4 with . and 6 with ;?

[akshat_mittal]

Hi Cobra,

We can ignore the limit part from the query. It was used for my purpose to see less rows.

Regards
Akshat

[andrew_bowman]

Use a UNION ALL between the two queries, that way you can use a single query to execute and get the results for both.

https://neo4j.com/docs/cypher-manual/current/clauses/union/

If you need something on the row to differentiate which query the result is associated with, add an additional RETURN variable where the value indicates which one it came from.

[akshat_mittal]

Hi Andrew ,

Any possibility of doing some changes in replace clause rather than using sub-query approach ??

Regards
Akshat

[Cobra]

So, we can do it with a CASE statement:

MATCH (sc:SITE_CODE_TABLE)-[:site2sitecode]-(s:SITE)-[utilsite2container]-(c:CONTAINER)
WHERE c.EQUIP_TYPE = 'ESC'
AND s.SITE_TYPE IN ['AG2-OTN','AG3-IDC','CLS','AG3','SAG2']
AND c.EQUIP_VENDOR = 'EMERSON NETWORK POWER'
RETURN 'GENERIC .x'+toLower(sc.SITE_CIRCLE_CODE_OC)+'.'+
       CASE WHEN c.EQUIP_NATIVENAME CONTAINS ':'
            THEN replace(c.EQUIP_NATIVENAME,':','_') 
            ELSE replace(c.EQUIP_NATIVENAME,'.','_') END AS MO,
       '.oc.'+s.SITE_REGION+'_LFLRTU' AS OC

Regards,
Cobra

[akshat_mittal]

Hello Cobra,

That is great !!

Can we use your query to set the values to a specific variable?

Regards
Akshat

[Cobra]

Yes sure

MATCH (sc:SITE_CODE_TABLE)-[:site2sitecode]-(s:SITE)-[utilsite2container]-(c:CONTAINER)
WHERE c.EQUIP_TYPE = 'ESC'
AND s.SITE_TYPE IN ['AG2-OTN','AG3-IDC','CLS','AG3','SAG2']
AND c.EQUIP_VENDOR = 'EMERSON NETWORK POWER'
SET c.MO = 'GENERIC .x'+toLower(sc.SITE_CIRCLE_CODE_OC)+'.'+
       CASE WHEN c.EQUIP_NATIVENAME CONTAINS ':'
            THEN replace(c.EQUIP_NATIVENAME,':','_') 
            ELSE replace(c.EQUIP_NATIVENAME,'.','_') END,
    c.OC = '.oc.'+s.SITE_REGION+'_LFLRTU'