Getting a join error on a query table that has always worked before

Getting a join error on a query table that has always worked before

Hello,

I am getting a join error "Sorry, query execution failed as the columns used in the JOIN conditions might contain too many duplicate values. Please ensure that at-least one of the column used in JOIN condition contains unique values."

This is a query that previously worked and I have multiple reports pulling data on it. I haven't changed anything within the query. Suddenly, now reports that are dependent aren't able to populate data and when running the query I get the error statement listed above.

Is this a Zoho bug? If not, how do i resolve this issue?

SELECT
max("payorauthorization"."AuthEntryDate") AS 'AuthEntryDate',
current_auth_rendered.AuthId AS 'AuthId',
cast(group_first("current_auth_rendered"."Weekly_Hours") as float) AS 'Weekly_Hours',
"current_auth_rendered"."AuthClientId" AS 'AuthClientId',
"current_auth_rendered"."YearWeek" AS 'YearWeek',
"current_auth_rendered"."Service_Desc" AS 'Service_Desc',
"current_auth_rendered"."Monday" AS 'Monday',
"current_auth_rendered"."CompositeKey" AS 'AuthCompositeKey',
"current_bx_weekly_sp_hours1"."Compositekey" AS 'SPCompositeKey',
cast(avg("current_auth_rendered"."Authorized Hours") as float) AS 'Authorized Hours',
"current_auth_rendered"."Rendered Hours" AS 'Rendered Hours',
"current_auth_rendered"."sum_hours" AS 'sum_hours',
"clientpayor"."ClientPayorType" as "ClientPayorType",
"clientpayor"."IsCurrentPayor" as "IsCurrentPayor",
"clientpayor"."ClientPayorCompanyId" as "ClientPayorCompanyId",
"clientpayor"."ClientPayorPlanId" as "ClientPayorPlanId",
"contactprincipal"."PrincipalContactId" as "PS_ID",
"contactprincipal"."PrincipalProviderFullName" as 'Program Supervisor',
"contactprincipal"."PrincipalProviderType" AS 'PrincipalProviderType',
IF("contactprincipal"."PrincipalProviderType"  = 'SuperSupervisor', 'Program Supervisor', IF("contactprincipal"."PrincipalProviderType"  = 'SpeechTherapist', 'Speech Language Pathologist', IF("contactprincipal"."PrincipalProviderType"  = 'SeniorConsultant', 'Occupational Therapist', ' '))) AS 'Supervision Position',
group_first("current_bx_weekly_sp_hours1"."SP_start_yearweek") AS 'SP Start Week',
"current_bx_weekly_sp_hours1"."SP_end_yearweek" AS 'SP End Week',
if_null("current_bx_weekly_sp_hours1"."Service Plan Hours", 0) as "Service Plan Hours",
if_null("Termination Date"."Termination Date", add_year(current_date(), 1)) as "Patient Termination Date",
if("current_auth_rendered"."Monday"  BETWEEN if_null("Start Date"."Start Date", '2017-01-01 00:00:00')  AND  if_null("Termination Date"."Termination Date", add_year(current_date(), 1)), 'Yes', 'No') as 'Active Patient',
if_null("Start Date"."Start Date", '2017-01-01 00:00:00') as "Patient Start Date",
"payorauthorization"."AuthId" AS 'AuthorizationId',
"payorplan"."payorname" AS 'PayorName',
"sp_sp_service_plans_leads_c"."sp_sp_service_plans_leadssp_sp_service_plans_idb" AS 'SP_id',
"payorauthorization"."Authorization Period" AS 'AuthorizationPeriod',
RANK() OVER(PARTITION BY "current_auth_rendered"."CompositeKey"  ORDER BY current_auth_rendered.AuthId DESC ) AuthRank,
RANK() OVER(PARTITION BY "current_bx_weekly_sp_hours1"."Compositekey"  ORDER BY "sp_sp_service_plans"."sp_active_date" DESC ) SPRank,
IF(if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100  <= 30, '30% and under', IF(if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100  > 30
AND if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100  <= 50, '50%', IF(if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100  > 50
AND if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100  <= 70, '70%', IF(if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100  > 70
AND if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100  <= 90, '90%', IF(if_null((sum("current_auth_rendered"."Rendered Hours") / sum("current_bx_weekly_sp_hours1"."Service Plan Hours")), 0) * 100  > 90, 'Greater than 90%', 'Check'))))) AS 'Percent Bucket',
"contactprincipal"."PrincipalProviderId",
"sp_sp_service_plans"."sp_active_date",
IF("current_auth_rendered"."Service_Desc"  = 'Behavioral Individual Therapy'
OR "current_auth_rendered"."Service_Desc"  = 'Behavioral Group Therapy', "current_auth_rendered"."Rendered Hours", 0) AS '1:1 and Group Rendered Hours',
IF("current_auth_rendered"."Service_Desc"  = 'Behavioral Supervision', "current_auth_rendered"."Rendered Hours", 0) AS 'Supervision Rendered Hours'
FROM  "current_auth_rendered"
JOIN "leads_cstm" ON "current_auth_rendered"."AuthClientId"  = "leads_cstm"."eo_id_c" 
INNER JOIN "sp_sp_service_plans_leads_c" ON "sp_sp_service_plans_leads_c"."sp_sp_service_plans_leadsleads_ida"  = "leads_cstm"."id_c" 
INNER JOIN "sp_sp_service_plans" ON "sp_sp_service_plans"."id"  = "sp_sp_service_plans_leads_c"."sp_sp_service_plans_leadssp_sp_service_plans_idb" 
INNER JOIN "current_bx_weekly_sp_hours1" ON "current_auth_rendered"."CompositeKey"  = "current_bx_weekly_sp_hours1"."Compositekey" 
LEFT JOIN "contactprincipal" ON "current_auth_rendered"."AuthClientId"  = "contactprincipal"."PrincipalContactId"
AND "current_auth_rendered"."Monday"  >= "contactprincipal"."PrincipalStartDate"
AND "current_auth_rendered"."Monday"  <= "contactprincipal"."End Date" 
LEFT JOIN "Termination Date" ON "current_auth_rendered"."AuthClientId"  = "Termination Date"."ContactId" 
LEFT JOIN "Start Date" ON "Start Date"."ContactId"  = "current_auth_rendered"."AuthClientId" 
INNER JOIN "payorauthorization" ON "current_auth_rendered"."AuthId"  = "payorauthorization"."AuthId" 
INNER JOIN "timebilling" ON "current_auth_rendered"."AuthId"  = "timebilling"."TimeBillingAuthorizationId" 
INNER JOIN "clientpayor" ON "payorauthorization"."AuthPayorPlanId"  = "clientpayor"."ClientPayorPlanId"
AND "payorauthorization".AuthClientId  = "clientpayor".ClientId 
LEFT JOIN "payorplan" ON "payorplan"."payorplanid"  = "payorauthorization"."authpayorplanid" 
LEFT JOIN "leads" ON "leads"."id"  = "leads_cstm"."id_c"  
WHERE "clientpayor"."ClientPayorType"  = 'Primary'
 AND "clientpayor"."ClientPayorDeletedDate"  IS Null
 AND "current_auth_rendered"."Monday"  BETWEEN "ClientPayorCoverageStartDate"  AND  if_null("ClientPayorCoverageEndDate", add_year(current_date(), 2))
 AND "timebilling"."TimeBillingIsDeleted"  IS NULL
 AND "timebilling"."TimeBillingIsVoid"  IS NULL
GROUP BY "current_auth_rendered"."YearWeek",
"current_auth_rendered"."AuthClientId",
"current_auth_rendered"."Service_Desc",
"current_auth_rendered"."Monday",
"current_auth_rendered"."CompositeKey",
"current_auth_rendered"."Rendered Hours",
"current_auth_rendered"."sum_hours",
"clientpayor"."ClientPayorType",
"clientpayor"."IsCurrentPayor",
"clientpayor"."ClientPayorCompanyId",
"clientpayor"."ClientPayorPlanId",
"contactprincipal"."PrincipalContactId",
"contactprincipal"."PrincipalProviderFullName",
"contactprincipal"."PrincipalProviderType",
"current_bx_weekly_sp_hours1"."SP_start_yearweek",
"current_bx_weekly_sp_hours1"."SP_end_yearweek",
"payorplan"."payorname",
if_null("current_bx_weekly_sp_hours1"."Service Plan Hours", 0),
if_null("Termination Date"."Termination Date", add_year(current_date(), 1)),
if("current_auth_rendered"."Monday"  BETWEEN if_null("Start Date"."Start Date", '2017-01-01 00:00:00')  AND  if_null("Termination Date"."Termination Date", add_year(current_date(), 1)), 'Yes', 'No'),
if_null("Start Date"."Start Date", '2017-01-01 00:00:00'),
"payorauthorization"."AuthId",
"current_bx_weekly_sp_hours1"."Compositekey",
"sp_sp_service_plans_leads_c"."sp_sp_service_plans_leadssp_sp_service_plans_idb",
IF("current_auth_rendered"."Service_Desc"  = 'Behavioral Individual Therapy', "current_auth_rendered"."Weekly_Hours", NULL),
"contactprincipal"."PrincipalProviderId",
current_auth_rendered.AuthId,
"sp_sp_service_plans"."sp_active_date",
"payorauthorization"."Authorization Period",
  IF("contactprincipal"."PrincipalProviderType"  = 'SuperSupervisor', 'Program Supervisor', IF("contactprincipal"."PrincipalProviderType"  = 'SpeechTherapist', 'Speech Language Pathologist', IF("contactprincipal"."PrincipalProviderType"  = 'SeniorConsultant', 'Occupational Therapist', ' '))) 
ORDER BY "current_auth_rendered"."Yearweek" 

    • Sticky Posts

    • What's New in Zoho Analytics - November 2025

      We're thrilled to announce a significant update focused on expanding your data connectivity, enhancing visualization capabilities, and delivering a more powerful, intuitive, and performant analytics experience. Here’s a look at what’s new. Explore What's
    • What's New in Zoho Analytics - October 2025

      Hello Users! We're are back with a fresh set of updates and enhancements to make data analysis faster and more insightful. Take a quick look at what’s new and see how these updates can power up your reports and dashboards. Explore What's New! Extreme
    • What’s New in Zoho Analytics – September 2025

      Hello Users!! In this month’s update, we’re raising the bar across multiple touchpoints, from how you bring in data, plan and track projects to how you design and brand your dashboards. We’ve added the all-new Gantt chart for project visualization, expanded
    • Announcing Agentic AI - Ask Zia!

      We are delighted to roll out the new agentic AI capabilities in Ask Zia, where every stage of the BI workflow is assisted by AI. With a human-in-the-loop approach, Ask Zia ensures that you’re in command of the decision, while AI handles the complexity.
    • Invitation-Based User Access in Zoho Analytics

      Hello everyone, We’re rolling out an important update on how users are added to your Zoho Analytics Organization and Workspaces. Previously, when admins added users, they were automatically added to the organization. Moving forward, to improve security
    • Recent Topics

    • Cancellation of written-off invoice

      Hi, Can I know when we cancel the write off (write back), in which FY, the reversal is recorded. It doesn't ask as to when the write off should be cancelled to reflect!. It shouldn't reflect in the year in which the invoice was written off since the Year
    • Create Invoice automated with Package

      Does anyone knows how to create an invoice from a SO when we have created the package? We do these manually. and validate that the product packed is the product invoiced (if the order is partially packed) Regards, JS
    • I want to create a mailing list, NOT a group.

      Can I create a mailing list in Zoho mail? I just want to be able to make a list of email addresses and give the list a name. Then when I type the list name, the list of email addresses will be automatically listed. When I create a group it sends an email
    • ERROR CODE :554 - Your access to this mail system has been rejected due to poor reputation of a domain used in message transfer

      This message was created automatically by mail delivery software. A message that you sent could not be delivered to one or more of its recipients. This is a permanent error. *****, ERROR CODE :554 - Your access to this mail system has been rejected due
    • no me llegan los correos a Zoho mail

      No puedo recibir correos pero sí enviarlos, ya hice la modificación de MX y la verificación de teléfonos, qué es lo que ocurre? gracias
    • Group Calendar as Default for adding new events, etc?

      Hi, I want to make the group calendar (that I created, if that makes a difference) the default for anything new I add to the calendar. How can I do that? thanks.
    • Bookmark Loading is Buffering

      Hi, i clicked on the bookmark tab, around yesterday and since then it's been constantly buffering and doesn't allow me to access the mail's i have tried login in and out but of no help also trying to share a screenshot of the issue around 232 kb size,
    • Zoho Webinar custom registration fields into Zoho CRM

      I am pushing webinar registrations into zoho crm as leads and this is working fine. I have added a few custom fields to my webinar registration and I wish for these fields values to get mapped into the resulting CRM lead record. I am not seeing anywhere
    • GitLab Extension for Zoho Desk: Connecting support and development for faster resolutions

      Hello everyone! We’re excited to introduce the GitLab Extension for Zoho Desk, an integration that bridges the gap between support and development teams. This allows tickets to be converted into actionable GitLab issues for faster resolutions, better
    • Download Google Drive Shared Image/File Using Deluge – Complete Guide

      Download Google Drive Shared Image/File Using Deluge – Complete Guide When working with Google Drive share links inside Deluge, many developers face a common challenge: 👉 How can we download a Google Drive file directly using Deluge? This article explains
    • How to update Acuity appointment fields from Zoho Flow?

      Hi, I need help with Zoho Flow → Acuity Scheduling integration. What I’m doing Appointment created in Acuity Zoho Flow creates a Zoho Calendar event I want to send the Zoho Calendar Event UID back into Acuity (into a custom form field) This is so that
    • No horizontal scroll bar in reports

      Hi support team, I created a pivot table report which contains many columns (in my application about 15). The problem is that there is no bottom scroll bar to display colums that does not fit within the browser page. I saw that it was possible to resize colums, but colums sizes are not saved within the session. So could you add scroll bar, and eventually make colums sizes stored in the user session ? Best regards,
    • Zoho Map integration tasks have changed - you need to "Locate all instances of Zoho Map integration tasks in your Deluge scripts by searching for the v1 marker... before 16 January 2026"

      Details of an issue specific to Zoho CRM + Deluge Functions (originally posted in this forum) can be found here: https://help.zoho.com/portal/en/community/topic/zoho-map-integration-tasks-have-changed-you-need-to-locate-all-instances-of-zoho-map-integration-tasks-in-your-deluge-scripts-by-searching-for-the-v1-marker
    • filter broke my data

      I uploaded a file recently from Sheets and it has top 2 rows frozen, with table headers in second row and each one is filterable. somehow my first 2 columns became unfiltered and no matter what I do I cannot reapply the filter?? also didn't realize they
    • Request to remove domain "greentechcoatings.vn" from previous Zoho account

      Hello Zoho Support, I would like to register the domain "greentechcoatings.vn" in my new Zoho Mail account, but I receive the message "This domain is already associated with another account". Please remove the domain from any previous/unknown account
    • Trigger actions in third-party apps using Zoho Flow

      Greetings, I hope you're all doing well. We're excited to share an enhancement to Bigin's workflow capabilities. Zoho Flow Actions are now available in Bigin, enabling you to automate tasks across third-party applications directly from your workflow and
    • Enroll, Script, Win: Hackathon 2025!

      Hello CRM Developers! Are you ready to create some magic with Client Script, Widgets and Functions in Zoho CRM? Let’s make Hackathon 2025 an unforgettable adventure! The Zoho Developer Community Hackathon 2025 is here, and it’s your time to shine! REGISTER
    • Zoho Help Desk - Not receiving emails

      Hello… I am on your trial version of help desk… and I am having trouble getting emails from Zoho Help Desk to my MS Outlook Exchange Server… So when a customer sends ticket request I am not getting an email with a notification? I have read your articles
    • Almost all Flows are being queued

      A few days ago I saw one of my Flows status was Queued. This was the first time this had ever happened. Now, almost every Flow that triggers is Queued for at least a few minutes. If I re-trigger the Flow (which causes separate problems) they sometimes
    • Employment Contract / HRMS Documents - Zoho People

      How do I create customized HR documents for disbursal from Zoho People ? Example, say offer letters ? appointment letters ? Memos ? We want to be able to preset these in one or ther other form and issue them to employees who are in Zoho People.
    • Live webinar: Craft and deliver impactful slides with Show’s desktop apps

      If you love the comfort of working on your desktop and want a setup that keeps you focused and uninterrupted, this session is made for you. We’re excited to invite you to our upcoming live webinar: "Craft and deliver impactful slides with Show's desktop
    • Enhancements to Bigin's forms

      Greetings, I hope all of you are doing well. We're happy to announce a few recent enhancements we've made to Bigin's forms. We'll go over each one in detail. Merge field support in auto-filled forms The auto-fill option in Bigin's forms lets you predefine
    • Table dimensions

      I try changing the dimensions of the table on my computer but it doesn't change. Do I have to be a premium member or does it only work on the app?
    • Direct link to Record Summary

      Hi everyone, In one of my reports, I have built a Record Summary template to display the details of one record. I would like to be able to link directly to this Record Summary once I submit a new record, without having to go to the list of records first and click on View. Is there a possibility to do so ?  Should I use the URL by passing some parameters ? Thank you very much for your help ! Guillaume
    • Verifying Zoho Mail Functionality After Switching DNS from Cloudflare to Hosting Provider

      I initially configured my domain's (https://roblaxmod.com/) email with Zoho Mail while using Cloudflare to manage my DNS records (MX, SPF, etc.). All services were working correctly. Recently, I have removed my site from Cloudflare and switched my domain's
    • Amendment effective date

      Hi everyone, I noticed that the amendment effective date mentionned in my amendment is not right. Indeed, when a contract is amended several times, it states the previous amendment and their effective date. However, the effective date stated is always
    • Show both Vendor and Customers in contact statement

      Dear Sir, some companies like us working with companies as Vendor and Customers too !!! it mean we send invoice and also receive bill from them , so we need our all amount in one place , but in contact statement , is separate it as Vendor and Customer, 
    • STOCK history in zohosheets

      is it possible to get historical stock value using stock function in zoho sheets? i could not see from and to period in the helper document.
    • Auto sync Photo storage

      Hello I am new to Zoho Workdrive and was wondering if the is a way of automatically syncing photos on my Android phone to my workdrive as want to move away from Google? Thanks
    • Agent password reset

      Hi Zoho support, I would like to ask if there is a way the admin can reset a password of an agent? Regards
    • Can receive but not send messages in Zoho Mail

      Hello! I was able to configure my email client successfully in that I can receive messages just fine. However, when I send messages out, they seem to go out fine (I don't receive any errors or anything), but the recipient does NOT receive those messages.
    • Mail is sent twice!

      Been using Zoho for a while now. Installed Zoho for someone else and some weird things are happening. Mails are being sent twice. He is using Thunderbird as an email client. I already read about email being duplicated in the sent folder. But in my case
    • Can't login IMAP suddenly

      Since this evening I'm getting the error: You are yet to enable IMAP for your account. Please contact your administrator... IMAP always been enabled in my account and was workign fine for the past 7 years. Already tried turning IMAP off and on again.
    • Sending of username did not succeed: Mail server pop.zoho.com responded: User already specified

      I am having issues receiving emails from Zoho in Thunderbird. I am getting the above error. The first error tells me Authentication failed, and prompts me to enter in my password. Then I get the above error. I can receive emails when I log in online to
    • Bug tracking

      Hi, does anyone know how to track errors during picking or packing? This way I can keep track and see how to improve and prevent errors in this area.
    • Migration of corporate mail environment from Yandex 360 to Zoho mail

      I have to migrate a corporate mail environment with an existing domain from Yandex 360 to Zoho mail. It is vital to migrate all users with all the data. I have read the article on this topic using MacMister Email Backup Software just now and have some
    • I'm unable to send mail pthrough Zoho SMTP programmatically

      This has been working for years, but today it's been offline all day long. I see nothing anywhere on your site about this. I'm not the only one experiencing this. Downdetector has a spike of reports today
    • Can no longer send email via Django site

      This was working fine as of 11/7/25. Now I am unable to send user verification emails from a Django site on a AWS lightsail sever. When a user attempts to register the following error occurs. I have also attempted to send a test email via the shell and
    • unable to send email but able to receive email

      my email address is info@securityforceservices.ca
    • Login to server failing

      When trying to retrieve my mail, I am getting this error message -- Login to server pop.zoho.com with username (my email address) failed. It gives me the option to retry, enter password, or cancel. Then I get this message -- Sending of username did not
    • Next Page