GitHub user swaroopchenchu created a discussion: What is best approach instead
of DB lookup?
I have two Entities which are related 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, 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]
