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"
Access your files securely from anywhere
Centralize Knowledge. Transform Learning.
All-in-one knowledge management and training platform for your employees and customers.
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Alex Ngo
Anitha Rajesh Kumar
Sticky Posts
What's New in Zoho Analytics - January 2026
Hello Users! We are starting the year with a strong lineup of updates, marking the beginning of many improvements planned to enhance your analytics experience. Explore the latest improvements built to boost performance, simplify analysis, and help youWhat'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'sWhat'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! ExtremeWhat’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, expandedAnnouncing 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.
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
Zoho Desk Partners with Microsoft's M365 Copilot for seamless customer service experiences
Hello Zoho Desk users, We are happy to announce that Zoho Desk has partnered with Microsoft's M365 to empower customer service teams with enhanced capabilities and seamless experiences for agents. Microsoft announced their partnership during their keynotehow to avoid duplicate customer
How can i avoid to create a duplicate customer. I charged a same customer with two different plans and it showed up twice in my customer list and subsequently in Zoho books contacts. It creates confusion to have a same customer appears multiple times in customer or contact list. How can i avoid it.The Social Wall: January 2026
Hello everyone, We’re back with the first edition of The Social Wall of 2026. There’s a lot planned for the year ahead, and we’re starting with a few useful features and improvements released in January to help you get started. Create a GBP in SocialAPI keys not showing in ZeptoMail dashboard
Hi there, I'm hoping someone can provide some assistance as support isn't replying. I am trying to configure my transactional emails, but the dashboard doesn't show any of my API details - the `div` is there but it's emtpy. Every time I click "GeneratePipeline: Copying rulesets from one data source to another
When creating and editing data pipelines, it would be really helpful to be able to copy the ruleset from one data source and 'paste' it to another. This would save time and reduce manual mistakes.Doubt about maximum email reach
Good morning, greetings. This is Bramdon García from EDULABS S.A.S ESP, located in Colombia. I'm writing to inquire about the possibility of sending an email to 35,000 people simultaneously. Our company has a Zoho account, but we'd like to know if there'sZoho calendar not working in browser
Hello, I am new to Zoho. I have two accounts for two separate businesses. In one of them, calendar loads in a browser no problem. However, if I use this account (sairfeetmusic.co.uk) calendar does not load. I also cannot add it to my Thunderbird LighteningAuthentication Failure when adding POP3 accounts
Hi everyone, I am a new user currently migrating from Gmail to Zoho Mail. I decided to make the switch following Google's decision to discontinue POP3 fetching. I previously used Gmail as my primary mail hub, and Zoho seems like the perfect alternativeTrident Application Folder
Hi, How to choose installation folder for Trident, by default it gets installed in C drive. How to change drive?Email disappeared to specific contact
Good afternoon, this morning I emailed somebody. This email isn’t showing up in my sent folder. They sent me a response which I clicked on and it disappeared immediately. Why could this be?How Do Mutliple Sales People Prospect in the "LEADS" module without calling the same leads?
We have 4 sales reps and the Leads module does not have real time intuitive knowlodge to make the sales rteps dont call the same people at the same time. How can we crate a fluent prospecting sytem where the salres reps can go out bound without callingCreate an Eye-Catching Announcement Widget for Your Help Center
Hello Everyone! In this week’s edition, let’s explore how to keep your customers updated with exciting news in the Help Center. See how ZylkerMobile wowed their customers by bringing updates right to their portal. ZylkerMobile, the renowned brand forEnable Free External Collaboration on Notecards in Zoho Notebook
Hi Zoho Notebook Team, I would like to suggest a feature enhancement regarding external collaboration in Zoho Notebook. Currently, we can share notes with external users, and they are able to view the content without any issue. However, when these externalUpdating records through Zoho Sheets View doesn't update timeline or trigger workflow rules
I have noticed that when i update a series of record with the zoho sheets view (see here http://d.pr/i/ahnR) it doesn't update timeline history or trigger workflow rules. I am using it in the Deals module. Looking forward for some more info. DenisHow do I change the account bank charges are charged to?
I want bank charges charged to my Credit Card Fees account. Is there a way to do this?Mail Search should allow grouping by conversation like Gmail.
Having switched from gmail I have found the search function hard to use. Mostly because mail is not grouped by conversation in search. If I search for a word when looking for a conversation had with someone then sometimes 10 emails will come up from theAbility to CC on a mass email
Ability to CC someone on a mass email.Updation of Old Browsers Versions for Zoho CRM
We have upgraded the default browser version for CRM to facilitate features like widgets and scripts that are often implemented aside from advanced functionalities for various business requirements. The latest version is aimed to provide better performanceProject-Based Inventory Consumption and Proforma Invoice in Zoho ERP
While working with customers across different industries, we have identified a few functional questions and gaps that commonly arise—especially for project-based and service-oriented businesses. Many organizations, such as those in shipping, construction,Zoho Desk domain Mapping not Working Showing CNAME Error
I have created the subdomain and created the Cname as well as its instracted on the Zoho website but when i try add the domain on help desk its showing error msg : Make sure you've mapped the CNAME entry and linked it to desk.cs.zohohost.com.au on yourFounders using Zoho — are you leveraging Zoho Campaigns + Zoho Social for thought leadership… or just sending emails?
I’ve noticed something interesting in the Zoho ecosystem. Many founders use Zoho Campaigns and Zoho Social for basic marketing—newsletters, scheduled posts, and announcements. But very few are using these tools strategically to: • Position themselvesI have the item field and Quantity field in the sub form , on the submit of the form if the quantity is grater than inventory means show alert on submit validation only for item type goods ,
I have the item field and Quantity field in the sub form , on the submit of the form if the quantity is grater than inventory means show alert on submit validation . Stock Check Validation only for item type goods , not for item type service . For theIMAP stopped working after enabling 2 factor authentication
IMAP stopped working after enabling 2 factor authentication. Is there any solution for this?Rename Service Report
Some of our customers are requesting the name of the service report PDF to be in a specific format, for example, instead of REP-001.PDF some are requesting to include their name like customername.pdf. is that possible?Approvals in Zoho Creator
Hi, This is Surya, in one of my creator application I have a form called job posting, and I created an approval process for that form. When a user submits that form the record directly adding to that form's report, even it is in the review for approval.Outgoing emails rejected due to SpamCop RBL listing (IP 136.143.188.12)
Hi All, I am writing to report a deliverability issue affecting outgoing emails from my Zoho Mail account. Recently, several messages sent from my domain (example.com) to external recipients have been rejected with the following error message (redactedShare Record Ownership in Zoho Recruit
We’re introducing User Fields in Zoho Recruit designed to make collaboration easier when multiple team members need to work on the same record. With User Fields, you can extend record ownership beyond a single user and enable smoother teamwork acrossRecherche d'un développeur
Bonjour, j'ai un projet de SAAS sur une base de zoho créator et zoho CRM et je recherche un développeur qualifié français pour créer l'application créator (fonctionnel et graphique) et les workflow et blueprint de CRMAPI to Apply Retainer invoice payment to Invoice
Hi Team, I could not find API to apply the Retainer invoice payment to existing Invoice. Can you please help ? Attaching the screenshotDisplay actual mileage on an invoice
My users are creating expenses in Zoho expense. For example, they expense 10 miles and get paid 7 dollars (10 miles * IRS rate of .70). If I look at the expenses in Zoho Books, it does show them at 10 miles at .70 cent When I add these expense to an invoiceCustomer Parent Account or Sub-Customer Account
Some of clients as they have 50 to 300 branches, they required separate account statement with outlet name and number; which means we have to open new account for each branch individually. However, the main issue is that, when they make a payment, theyCloning a Pick List
I have an existing Pick List in my Contacts that I want to drop into my Leads. Is there a way to copy or clone the field with it's accompanying Pick List? Thanks for your time.How do I link my invoice to an estimate?
There has been instances where I have created estimates, however, invoices for the same estimate were created independently. The status of these estimates hasn't converted to 'invoiced'.I wish to upload 40000 Resumes in Zoho Recruit Database. Can I do this in batch of 1000 Resumes ?
I would like to upload thousand or few hundred of resumes in Zoho Recruit in one go. Please let me know how can I do this Or migrate my 40000 resumes from previous ATS to Zoho Recruit.Zoho Writer for Proposals
Hi, one of the things we've struggled with since moving to Zoho ecosystem is our proposal software Qwilr does not integrate well. It surprises me Zoho doesn't have proposal software but given all the capabilities of Zoho Writer, I'm wonder if anyone isCustom Fonts in Zoho CRM Template Builder
Hi, I am currently creating a new template for our quotes using the Zoho CRM template builder. However, I noticed that there is no option to add custom fonts to the template builder. It would greatly enhance the flexibility and branding capabilities ifHow do I edit the Calendar Invite notifications for Interviews in Recruit?
I'm setting up the Zoho Recruit Interview Calendar system but there's some notifications I don't have any control over. I've turned off all Workflows and Automations related to the Calendar Scheduling and it seems that it's the notification that is sentFoutmelding bij uitvoering/opslaan functie 'Left expression is of type TEXT and right expression is of type NUMBER'
Hoi! Ik heb een workflow (zie bijlage) die wordt getriggerd zodra de verwachte weekomzet van een bedrijf wordt aangepast naar een bedrag hoger dan 0. Op dat moment moet een functie (zie bijlage) gaan berekenen wat het benodigde kredietlimiet moet zijn.Hoe kun je Nederlandse loonstroken boek in Zoho Books?
Beste Community, Heeft er iemand een idee hoe je standaard loonstroken kunt inboeken in Zoho Books? Ik ben benieuwd hoe jullie dit doen en wat de mogelijkheden zijn.Unable to Filter Retail Sales Orders & Need Paid/Unpaid Filter – Zoho Books
Hi Zoho Team, Recently you introduced Retail – Standard and Retail – Premium templates for Sales Orders. However, in the Sales Order module we still cannot filter or segregate Retail Sales Orders separately from normal B2B sales orders. Because of this,Next Page