Forum Discussion
MongoDB Atlas SQL connection using 2 connections with 1 data gateway problem
Hi Anonymous
I contacted Microsoft support and they were helping me, but finally I managed to find a solution with my colleagues. It turns out that it is possible to create a kind of ‘view’ in a section that Mongo Atlas offers called Data Federation. There I put together both collections, each one from a different cluster and I only needed one connection for both Desktop and Service.
So as this has helped me, I'll give this answer as a solution.
Anyway, thank you very much for the help provided in your comments!
Yes am using in Native Query
am getting 1007 column is not specified as it is array of objects and one lead may have many quotation and many leads will have blanks how to resolve this??
Error 1007 indicates that the status field does not exist in all objects in the quotations array, or that quotations is null or empty in some documents.
To avoid this, make sure the field exists before accessing it. For example:
IF(quotations IS NOT NULL AND quotations.status IS NOT NULL, quotations.status, NULL)Also check that there are no name errors in the fields.
Tried with case
SELECT
_id,
CASE WHEN quotations IS NOT NULL AND quotations.status IS NOT NULL THEN quotations.status ELSE NULL END AS quotation_status,
CASE WHEN quotations IS NOT NULL AND quotations.totalAmount IS NOT NULL THEN quotations.totalAmount ELSE NULL END AS quotation_amountFROM UNWIND(
leads WITH PATHS => (
quotations[OUTER => TRUE, INDEX => quotation_idx]
),
INDEX => idx,
OUTER => FALSE
)
Direct If Condition is not working so tried with CASE WHEN but also same error
CommandError { code: 96, code_name: "OperationFailed", message: "failed getting result set schema: algebrize error: Error 1007: Cannot access field `status` because it could not be found. below is my Schemaquotations
Array (1)0
Objectstatus
"Yes"
totalAmount
10