Problem gdy SELECT prowadzi do wiersza, który nie istnieje w bazie

Witam,

Sytuacja: Zlecenie serwisowe które może składać z części i usług, samych części lub samych usług, Potrzebne jest wyliczenie marży dla takich przypadków. Czynności i części zlecenia są zapisane w dwóch odrębnych tabelach. Może być ich dowolnie wiele lub wcale.
Chcę uzyskać zapytanie które wyświetli wartość lub 0 gdy w którymś zapytaniu zabraknie argumentu o nadanej wartości (gdy nie ma pustych wartości w obu zapytaniach do obu tabel działa to poprawnie)
Zrobiłem coś takiego, jednak nie chce to zadziałać:

(SELECT SUM(ISNULL(SrY_WartoscNetto,0)) - SUM(ISNULL(SrY_KosztUslugi,0)) 
FROM SrsCzynnosci  WHERE (SrZ_SrZId  = SrY_SrZId)) + (SELECT SUM(ISNULL(SrC_WartoscNetto,0)) - SUM(ISNULL(SrC_WartoscZakupu,0))
FROM SrsCzesci WHERE (SrZ_SrZId=SrC_SrZId))

W tabeli SrsCzesci może brakować wpisów w tym sensie, że żadne części w zleceniu nie były użyte. Analogicznie w tabeli SrsCzynnosci - może brakować wierszy/-a dla takiego zlecenia.

zrob moze right join,

masterc napisał(a):

zrob moze right join,

Nie zrobi żadnego złączenia, bo nie ma do czego się łączyć w tym zapytaniu - brakuje tam tabeli z nagłówkiem tego zlecenia serwisowego.
Zakładam, że wiersz zlecenia serwisowego będzie istniał zawsze i do niego trzeba dołączać tabele SrsCzynnosci i SrsCzesci.

A poza tym, to zadanie na 5 min dla przedszkolaka bazodanowego... Żart jakiś czy o co chodzi?

Takie zapytanie może wyglądać tak, z wykorzystaniem outer apply, które działa sobie w MSSQL ale nie w każdej wersji, bodajże od wersji 2012.
Coś takiego, pisałem z łapy i mogą być błędy.

select 
  HDR.*,
  isnull(R.SrY_WartoscNetto, 0) - isnull(R.SrY_KosztUslugi, 0) as Robocizna,
  isnull(M.SrC_WartoscNetto, 0) - isnull(M.SrC_WartoscZakupu, 0) as Materialy
-- tabela ze zleceniami serwisowymi, zgaduję że tak się nazywa...
from SrsZlecenia HDR
-- pobierz dane o czynnościach, a jeśli nic nie ma to zapytanie nic nie zwróci
outer apply (
  SELECT 
      SUM(SrY_WartoscNetto) as SrY_WartoscNetto ,
      SUM(SrY_KosztUslugi) as SrY_KosztUslugi
  from SrsCzynnosci WHERE SrZ_SrZId = HDR.SrC_SrZId
) as R
-- pobierz dane o częściach (materiałach), a jeśli nic nie ma to zapytanie nic nie zwróci
outer apply (
SELECT 
   SUM(SrC_WartoscNetto) as SrC_WartoscNetto,
   SUM(SrC_WartoscZakupu) as SrC_WartoscZakupu
FROM SrsCzesci WHERE SrZ_SrZId = HDR.SrC_SrZId
) as M

@wloochacz: outer apply to trochę overkill, to wykorzystuje się raczej do joina z funkcjami.
Twoje zapytanie spokojnie mozna zamienic na joiny:

select 
  HDR.*,
  isnull(R.SrY_WartoscNetto, 0) - isnull(R.SrY_KosztUslugi, 0) as Robocizna,
  isnull(M.SrC_WartoscNetto, 0) - isnull(M.SrC_WartoscZakupu, 0) as Materialy
-- tabela ze zleceniami serwisowymi, zgaduję że tak się nazywa...
from SrsZlecenia HDR
-- pobierz dane o czynnościach, a jeśli nic nie ma to zapytanie nic nie zwróci
Left join (
  SELECT 
      SrZ_SrZId,
      SUM(SrY_WartoscNetto) as SrY_WartoscNetto ,
      SUM(SrY_KosztUslugi) as SrY_KosztUslugi
  from SrsCzynnosci 
  group by SrZ_SrZId 
) as R on r.SrZ_SrZId = HDR.SrC_SrZId
-- pobierz dane o częściach (materiałach), a jeśli nic nie ma to zapytanie nic nie zwróci
Left join (
SELECT 
   SrZ_SrZId,
   SUM(SrC_WartoscNetto) as SrC_WartoscNetto,
   SUM(SrC_WartoscZakupu) as SrC_WartoscZakupu
FROM SrsCzesci 
  group by SrZ_SrZId 
) as M on m.SrZ_SrZId = HDR.SrC_SrZId

Panczo napisał(a):

@wloochacz: outer apply to trochę overkill, to wykorzystuje się raczej do joina z funkcjami.

Overkill? A dlaczego niby?
To po prostu wygodne i jak dla mnie czytelne.

Twoje zapytanie spokojnie można zamienić na joiny:

Pewnie, że można - tylko mi się to nie podoba, wygląda to dla mnie nie tak elegancko; po prostu nie lubię derived tables.
Poza tym ja bym raczej napisał to z wykorzystaniem CTE, bo lubię i rozumiem.
A sama baza to wykona... pewnie różnice będą pomijalne dla tych różnych zapytań.

@wloochacz: @Panczo

Dzięki za merytoryczny wkład w moje przedszkolne problemy.
Mnie co prawda dopiero do żłobka zapisali.:)
Sprawdzę jak to zadziała w programie docelowym.

Overkill? A dlaczego niby?

Nie ma moim zdaniem potrzeby stosowania składni dla "sekwencyjnego " przetwarzania podzapytania. w tym wypadku plan zapytania będzie podobny, ale kilka razy się przejechalem na wydajności takich zapytań. Dlatego stosuje w swojej praktyce, ze apply tylko jeżeli musze "policzyć" coś dla każdego wiersza i nie da się joinami.

To po prostu wygodne i jak dla mnie czytelne

O estetyce nie będę dyskutował ;)

W programie muszę wbić to w jednej linii:

(SELECT HDR.*,ISNULL(R.SrY_WartoscNetto, 0) - ISNULL(R.SrY_KosztUslugi, 0) as Robocizna, ISNULL(M.SrC_WartoscNetto, 0) - ISNULL(M.SrC_WartoscZakupu, 0) as Materialy FROM SrsZlecenia HDR LEFT JOIN (SELECT SrZ_SrZId, SUM(SrY_WartoscNetto) AS SrY_WartoscNetto , SUM(SrY_KosztUslugi) AS SrY_KosztUslugi FROM SrsCzynnosci GROUP BY SrZ_SrZId) AS R ON r.SrZ_SrZId = HDR.SrC_SrZId)

Po wprowadzeniu tego pojawia się taki komunikat:

Each GROUP BY expression must contain at least one column that is not an outer reference.
Invalid column name 'SrC_SrZId'.
The multi-part identifier "M.SrC_WartoscNetto" could not be bound.
The multi-part identifier "M.SrC_WartoscZakupu" could not be bound.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

@Grzegorz3000: W programie wchodzi tekst tylko do pierwszego "entera"

W SQLu nie stosuje się kamelizacji nazw, bo jest to tam koszmarnie nieczytelne. Wszędzie słowa oddzielaj underscorem (_) i małe litery, nazwy w pierwszej osobie liczby pojedynczej.

@wloochacz:
Po drobnych poprawkach poniższe zapytanie działa w MMS:

select 
  HDR.*,
  isnull(R.SrY_WartoscNetto, 0) - isnull(R.SrY_KosztUslugi, 0) as Robocizna,
  isnull(M.SrC_WartoscNetto, 0) - isnull(M.SrC_WartoscZakupu, 0) as Materialy
-- tabela ze zleceniami serwisowymi, zgaduję że tak się nazywa...
from CDN.SrsZlecenia HDR
-- pobierz dane o czynnościach, a jeśli nic nie ma to zapytanie nic nie zwróci
outer apply (
  SELECT 
      SUM(SrY_WartoscNetto) as SrY_WartoscNetto ,
      SUM(SrY_KosztUslugi) as SrY_KosztUslugi
  from SrsCzynnosci WHERE SrY_SrZId = HDR.SrZ_SrZId
) as R
-- pobierz dane o częściach (materiałach), a jeśli nic nie ma to zapytanie nic nie zwróci
outer apply (
SELECT 
   SUM(SrC_WartoscNetto) as SrC_WartoscNetto,
   SUM(SrC_WartoscZakupu) as SrC_WartoscZakupu
FROM SrsCzesci WHERE SrC_SrZId = HDR.SrZ_SrZId
) as M

Rezultatem są dwie dodatkowe kolumny Robocizna i Materiały z odpowiednimi wartościami zapisanymi w zleceniu lub 0 gdy wpisu brak (NULL w bazie).
Jednak w docelowym programie to nie przechodzi (komunikat Only one expression can be specified in the select list when the subquery is not introduced with EXISTS). Zapytanie tam musi zwróć sumę tych kolumn.
Czy ktoś z Was ma pomysł jak to osiągnąć?

Grzegorz3000 napisał(a):

Rezultatem są dwie dodatkowe kolumny Robocizna i Materiały z odpowiednimi wartościami zapisanymi w zleceniu lub 0 gdy wpisu brak (NULL w bazie).
Jednak w docelowym programie to nie przechodzi (komunikat Only one expression can be specified in the select list when the subquery is not introduced with EXISTS). Zapytanie tam musi zwróć sumę tych kolumn.

Czy ktoś z Was ma pomysł jak to osiągnąć?

Spróbuj podać jawnie wszystkie kolumny z HDR, a więc zamiast HDR.* napisz HDR.Kolumna1, HDR.Kolumna2, itd. - co tam Ci potrzebne.
A jak dalej nie zadziała (co to za program, że w nim nie działa?) to zawsze możesz zamknąć to zapytanie np. w widoku w bazie danych i potem zapytania robić już z tego widoku, jak z tabeli - to pewnie na pewno zadziała wszędzie.