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

Neo4j Graph Platform Cypher
1

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

2

Hello @akshat_mittal :slight_smile:

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

Regards,
Cobra

3

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

4

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

5

Hi Cobra,

Thanks a lot !!

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

Regards
AM

6

Do you want to return both on the same line?

7

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

8

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
9

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

10

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

11

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

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.

12

Hi Cobra,

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

Regards
Akshat

13

Hi Andrew ,

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

Regards
Akshat

14

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

15

Hello Cobra,

That is great !!

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

Regards
Akshat

16

Yes sure :slight_smile:

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'