30. marts 2009 - 18:37Der er
10 kommentarer og 1 løsning
Bruge select-værdien i subquery's where
Hej Jeg har en sql-forespørgsel i som indeholder en subquery. Fra subqueryien ønsker jeg Lognummeret i dennes where-betingelse.
Her er forespørgslen før subqueryen: SELECT ActivityID AS Lognummer, CustomerName FROM Activities
Ovenstående skelet giver forskellige lognumre uden dubletter. Jeg vil gerne bruge lognummeret i min subquery - men når jeg forsøger dette fåes fejlbeskeden: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Her er den samlede sql:
SELECT ActivityID AS Lognummer, CustomerName, -- subqery start ( SELECT T2.Amount FROM Navision.dbo.[ServicePartner A_S$Sales Invoice Header] T1 INNER JOIN (SELECT [Document No_] , SUM([Amount Including VAT]) AS Amount FROM Navision.dbo.[ServicePartner A_S$Sales Invoice Line] GROUP BY [Document No_]) T2 ON T1.[No_] = T2.[Document No_] WHERE T1.[Rep_Nr] = (CAST(Activities.activityid AS VARCHAR(20))) ) AS pris -- subquery slut
FROM Activities WHERE VendorID = 33 AND activityclosed = 1 ORDER BY lognummer
Er der nogen som kan fortælle mig hvad jeg skal gøre?
jo, det skriver fejlmedd., men i "hovedforespørgslen"
SELECT ActivityID AS Lognummer, CustomerName FROM Activities Her er ActivityID entydigt. Det er dette activityid, som puttes ind i subqueryien, hvor der kun er een pris pr. activity.
ok, damn - kan godt se nu at supqueryien returnerer flere værdier, for - når jeg putter din "top 1" mangler der nogle aktiviteter. Måske I kan hjælpe mig med at lave den rigtige sql?
Jeg vil forsøge at forklare lidt nærmere:
SELECT ActivityID AS Lognummer , CustomerName FROM Activities
Denne select returnerer samtlige aktiviteter for nogle reparationer. Til hver aktivitet findes flere omkostninger som skal lægges sammen til en samlet pris; det er det som jeg har forsøgt gjort i subquerien (kan jeg evt. kalde en Stored Procedure med inputtet activityid og ouputtet samlet pris?)
Hvis vi tager et eksempel på en enkelt aktivitet, findes den samlede pris:
SELECT SUM(Amount) FROM Navision.dbo.[ServicePartner A_S$Sales Invoice Line] WHERE [Document No_] = ( SELECT No_ FROM Navision.dbo.[ServicePartner A_S$Sales Invoice Header] WHERE Rep_Nr = '200442' )
Denne returnerer summen af af samtlige omkostninger. Altså eet tal, men jeg får fejlmedd: "Subquery returned more than 1 value", når jeg forsøger at indsætte aktivitetsnummeret. Det forstår jeg ikke. Summen er jo kun eet tal.....
Hvordan får jeg samlet brikkerne, hvor Rep_Nr = ActivityID?
Rettelse: Denne returnerer summen af af samtlige omkostninger. Altså eet tal, men jeg får fejlmedd: "Subquery returned more than 1 value", når jeg forsøger at indsætte aktivitetsnummeret. Det forstår jeg ikke. Summen er jo kun eet tal.....
Kun når jeg ikke hardcoder aktivitetsnummeret altså skriver: WHERE Rep_Nr = (CAST(Activities.activityid AS VARCHAR(20)))
Igen ... er Rep_Nr en unik key i din table ? hvis ikke, så har query optimizeren ingen viden om at den er unik og bør virke.
Endnu en ting ... ved ikke hvad Query optimizeren gør ved Unik keys når du caster dem ... så ... TOP 1
Derfor skal du have en TOP 1 med ... hvilket jeg ikke mener der skulle være noget i vejen i at gøre ... og nu ved din query optimizer at der kun kommer en row tilbage lige meget hvad ...
Jeg forstår egentlig heller ikke hvorfor top 1 skulle ændre noget. Det er jo top 1 af summen... Desværre får jeg ikke summen for min test-aktivitet. Der springes nogen over.
Hvis jeg tager denne forespørgsel: SELECT Amount FROM Navision.dbo.[ServicePartner A_S$Sales Invoice Line] WHERE [Document No_] = '277026' (ikke aktivitetsnummeret) får jeg 5 rækker med priser, som det er meningen skal tælles sammen i subquerien. Jeg gætter på at det er alle dem som returnerer mere end een række der bliver udeladt - men hvorfor og hvad jeg skal gøre vides ikke.
Hvis et felt er unik og du i din WHERE clause sætter en betingelse, så ved Query Optimiseren at der _ALDRIG_ kan returneres mere end 1 row ....
Hvis den ikke er unik, men du som programmør ved at den kun kan returnere 1, skal du gøre det ... optimiseren har ingen ide til at vide det ... kan ske det lige nu ville virke ... men at der senere kommer flere rows ... derfor er den vigtig. ( Mener det er sådan det hænger sammen )
Tror du har byttet rundt ... Din yderste må returnere 1000 mill rows, men din query inden i den ... altså: SELECT No_ FROM Navision.dbo.[ServicePartner A_S$Sales Invoice Header] WHERE Rep_Nr = '200442'
Må kun returnere 1 row.
Men må kunne laves nemmere ... med lidt join ...
SELECT H.HeaderText, SUM(L.Amount) FROM Header H JOIN Linje L ON H.HeaderID = L.HeaderID GROUP BY H.HeaderText
Hey buzzz Jeg fandt løsningen (se nedenfor) og havde egentlig fundet den før i mine mange andre forsøg.
Du var inde på det rigtige med at lave en top 1. Det har jeg så gjort i inderste select. Desværre havde jeg lavet en tumpefejl, nemlig at blande vendor-numrene sammen, så jeg forstod ikke at mit test-lognnr ikke kom med på listen. Løsningen var ligefor:testlognummeret havde ikke vendor=33, som jeg bruger i where-clausen. pinligt pinligt pinligt.
Læg et svar, du havde dog løsningen...:-)
SELECT ServicePartner.dbo.Activities.ActivityID AS Lognummer, ServicePartner.dbo.Activities.CustomerName, ( SELECT SUM(Amount) FROM Navision.dbo.[ServicePartner A_S$Sales Invoice Line] WHERE [Document No_] = ( SELECT TOP 1 (No_) FROM Navision.dbo.[ServicePartner A_S$Sales Invoice Header] WHERE Rep_Nr = (CAST(Activities.activityid AS VARCHAR(20))) )
) AS pris
FROM Activities WHERE VendorID = 33 AND activityclosed = 1 ORDER BY lognummer
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.