GitHub user swaroopchenchu edited a discussion: What is best approach instead
of DB lookup?
I have two Entities which one-to-many relation in mssql server
Entity A - Fields(id, age, gender, ActivationId, ... etc)
Entity B - Fields(id, Activation ,....etc)
so A.ActivationId == B.Activation
Entity B can have multiple records with same ActivationId
Earlier I was using DB lookup for Entity B with ActivationId came from response
of query of InputTable of A.
DB lookup gets the top one record(thats fine for me) but it taking humongous
time i have almost 2.8 million records, Hence i had to switch to incorporate in
Input Query Itself with left join but later realized multiple rows from Entity
B makes new record in destination. so count is not matched from source to
destination.
Eg: LEFT JOIN QUERY
accIdn.idType, accIdn.idNumber, accIdn.expiryDate are newly placed in query
SELECT
// other fields of acc and con table
, accIdn.idType as AccIdentificationType
, accIdn.idNumber as AccIdentificationNumber
, accIdn.expiryDate as AccIdentificationexpiryDate
FROM ACCOUNT acc
LEFT JOIN XCONTACT con ON con.account = acc.BAN and con.type_=1
LEFT JOIN AccountIdentification accIdn on accIdn.BAN = acc.BAN
WHERE acc.spid = 410
AND acc.state != 2
AND acc.systemType !=0
AND (acc.parentBAN = '' OR acc.parentBAN IS NULL)
ORDER BY
acc.BAN
what is the best way to handle multiple records of Entity B? is it from query
or other transform etc?
Any help is appreciated.
Thanks
GitHub link: https://github.com/apache/hop/discussions/6136
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]
