Add new record in existing db using sql?

Hi
This is schema

T11 has 4 edges and A10 has 1 edge

Now I have a new record to insert in the graph, shown in the excel sheet

I created a batch query

begin
let pt = create vertex extends PT set TrxID = ‘T20’
let acc = select from Account where AccountNo = ‘A18’
let cus = select from CustNo where CustomerNo = ‘C21’
let ccus = select from CustNo where CustomerNo = ‘C1’
let ccode = select from CCode where CurrencyCode = ‘AUS’
let cofc = select from CofC where CountryOfOrigin = ‘AU’

let bcon = create property extends PT set $pt.BeneCountry = ‘AU’
let bal = create property extends PT set $pt.TransactionAmount = 51656

let edge1 = create edge HasAccount from $pt to $acc
let edge2 = create edge HasCustNo from $acc to $cus
let edge3 = create edge PT_CounterAID from $pt to $ccus
let edge4 = create edge PT_CurCode from $pt to $ccode
let edge5 = create edge PT_CofOrigin from $pt to $cofc
commit retry 100
return $edge1, $edge2, $edge3, $edge4, $edge5

May I know is this correct, getting error, also whether BeneCountry and TransactionAmount settings are correct?

Here T20 only new, other values have a existing vertex, so I need to just link it,
Also if the new vertex doesn’t exist can I do “if else” to create or select

Thanks

Hi @hanzgs

At a first look I’d say you have at least two problems in this script:

  • all the statements need a semicolon at the end (also the BEGIN and COMMIT RETRY)
  • RETURN with commas is not supported, but you can add square brackets and it should work: return [$edge1, $edge2, $edge3, $edge4, $edge5]

Thanks

Luigi

Hi @luigidellaquila
I followed the syntax in OrientDB Documentation for the semicolon

Uhm… I see, I’d say the docs are outdated.
Could you please point me to the page, so that I can fix it?

Thanks

Luigi

https://orientdb.com/docs/3.0.x/sql/SQL-batch.html

Thank you very much, I’ll fix it ASAP

Luigi

Hi @luigidellaquila

begin;
let pt = create vertex extends PT set TrxID = ‘T20’;
let acc = select from Account where AccountNo = ‘A18’;
let cus = select from CustNo where CustomerNo = ‘C21’;
let ccus = select from CustNo where CustomerNo = ‘C1’;
let ccode = select from CCode where CurrencyCode = ‘AUS’;
let cofc = select from CofC where CountryOfOrigin = ‘AU’;
let bcon = create property extends PT set $pt.BeneCountry = ‘AU’;
let bal = create property extends PT set $pt.TransactionAmount = 51656;
let edge1 = create edge HasAccount from $pt to $acc;
let edge2 = create edge HasCustNo from $acc to $cus;
let edge3 = create edge PT_CounterAID from $pt to $ccus;
let edge4 = create edge PT_CurCode from $pt to $ccode;
let edge5 = create edge PT_CofOrigin from $pt to $cofc;
commit retry 100;
return [$edge1, $edge2, $edge3, $edge4, $edge5];

May I know the mistake please

Thanks

Hi @hanzgs

I think the problem is the following

 create property extends PT

that is not supported (not sure what is your actual goal here…)

Thanks

Luigi

Hi @luigidellaquila
Thanks for the reply

PT, Account and CustomerNo were already created vertices, For Account and CustomerNo I don’t have to create new ones, but for PT I need a new vertex, so for creating new vertex i’m extending from the created PT

Thanks