Results 1 to 4 of 4
  1. #1
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    407

    Subquery - no result gives error

    hi, i have this query (actually just a small part) that retrieve the last experience of every guy, but only if the previous experience is a real bank, not "\n"

    Code:
    SELECT denominazione_soggetto_abilitato,Comune,provincia,regione,  (SELECT    denominazione_soggetto_abilitato     FROM   ocfdettaglio AS O2        WHERE   ocfdettaglio.id = O2.id     AND O2.data_fine =  (SELECT Max(data_fine)   FROM   ocfdettaglio AS   O3   WHERE  ocfdettaglio.id =O3.id    AND    ocfdettaglio.data_inizio     >= O3.data_fine  AND O3.data_fine     IS NOT     NULL   AND O3.nocambio = FALSE AND O3.denominazione_soggetto_abilitato <> "\n")   AND    ocfdettaglio.denominazione_soggetto_abilitato <>
    O2.denominazione_soggetto_abilitato AND   O2.denominazione_soggetto_abilitato IS NOT NULL AND O2.denominazione_soggetto_abilitato  <> "\n") AS AziendaPrec,NULL AS AziendaSucc,data_inizio as DataUnita 
    
    
    FROM ocfdettaglio WHERE NoInizio = False AND (ocfdettaglio.[denominazione_soggetto_abilitato] IN(SELECT OCfbanche.denominazione_soggetto_abilitato   FROM OCFBAnche WHERE aziendaID =(SELECT FIRST(aziendaid) FROM OCFbanche RIGHT JOIN OCfdettaglio ON Ocfbanche.denominazione_soggetto_abilitato = ocfdettaglio.denominazione_soggetto_abilitato     WHERE (ocfbanche.[denominazione_soggetto_abilitato]=[Maschere]![OCFAnalisi]![CercaBancaCB] )  )))   AND DAta_inizio IS NOT NULL AND (Data_inizio is not null) AND(data_inizio BETWEEN FORMS!Ocfanalisi!iniziotb AND FORMS!Ocfanalisi!FINEtb OR FORMS!Ocfanalisi!iniziotb is null OR FORMS!Ocfanalisi!finetb is null)
    the problem is that i want this value ,but only if it's a bank, not if is the "\n" that means nothing.
    Click image for larger version. 

Name:	Screenshot 2024-02-05 164745.png 
Views:	19 
Size:	15.8 KB 
ID:	51443




    I tried deleting the part
    Code:
    AND O2.denominazione_soggetto_abilitato  <> "\n"
    and it works, so i'd like to know the best way to avoid the "hhe subquery give more than 1 result"

    thanks

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,707
    I have reformatted your sql (via PoorSQL.com) for readability

    Code:
    SELECT denominazione_soggetto_abilitato
    	,Comune
    	,provincia
    	,regione
    	,(
    		SELECT denominazione_soggetto_abilitato
    		FROM ocfdettaglio AS O2
    		WHERE ocfdettaglio.id = O2.id
    			AND O2.data_fine = (
    				SELECT Max(data_fine)
    				FROM ocfdettaglio AS O3
    				WHERE ocfdettaglio.id = O3.id
    					AND ocfdettaglio.data_inizio >= O3.data_fine
    					AND O3.data_fine IS NOT NULL
    					AND O3.nocambio = FALSE
    					AND O3.denominazione_soggetto_abilitato <> "\n"
    				)
    			AND ocfdettaglio.denominazione_soggetto_abilitato <> O2.denominazione_soggetto_abilitato
    			AND O2.denominazione_soggetto_abilitato IS NOT NULL
    			AND O2.denominazione_soggetto_abilitato <> "\n"
    		) AS AziendaPrec
    	,NULL AS AziendaSucc
    	,data_inizio AS DataUnita
    FROM ocfdettaglio
    WHERE NoInizio = False
    	AND (
    		ocfdettaglio.[denominazione_soggetto_abilitato] IN (
    			SELECT OCfbanche.denominazione_soggetto_abilitato
    			FROM OCFBAnche
    			WHERE aziendaID = (
    					SELECT FIRST(aziendaid)
    					FROM OCFbanche
    					RIGHT JOIN OCfdettaglio ON Ocfbanche.denominazione_soggetto_abilitato = ocfdettaglio.denominazione_soggetto_abilitato
    					WHERE (ocfbanche.[denominazione_soggetto_abilitato] = [Maschere] ! [OCFAnalisi] ! [CercaBancaCB])
    					)
    			)
    		)
    	AND DAta_inizio IS NOT NULL
    	AND (Data_inizio IS NOT NULL)
    	AND (
    		data_inizio BETWEEN FORMS ! Ocfanalisi ! iniziotb
    			AND FORMS ! Ocfanalisi ! FINEtb
    		OR FORMS ! Ocfanalisi ! iniziotb IS NULL
    		OR FORMS ! Ocfanalisi ! finetb IS NULL
    		)

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    without knowing why it is returning more than one record In your sub query use

    select distinct

    or

    select top 1

    or change it to an aggregate query

    otherwise you need additional criteria

  4. #4
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    407
    what a jackass! i always forgot "top"! That did the trick! thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error in using subquery in Join query
    By ham.sajjadi in forum Queries
    Replies: 5
    Last Post: 08-17-2019, 05:31 PM
  2. Subquery - Syntax Error In Expression
    By kagoodwin13 in forum Queries
    Replies: 4
    Last Post: 03-24-2016, 07:02 PM
  3. Another code error using subquery
    By shani20 in forum Programming
    Replies: 1
    Last Post: 02-03-2015, 09:49 PM
  4. SubQuery Error
    By aboondocksaint2 in forum Queries
    Replies: 12
    Last Post: 06-16-2014, 11:54 AM
  5. subquery error
    By slimjen in forum Queries
    Replies: 16
    Last Post: 06-10-2014, 01:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums