[es] - Pomoć oko kompleksnog upita

rambo
Dejan Petković
Beograd

Član broj: 6095
Poruke: 190
*.eunet.yu.

+6 Profil

^{27.04.2009. u 14:50 - pre 182 meseci}

Treba mi pomoć da napišem jedan malo kompleksniji upit, ili ako ne može sa SELECT, onda objašnjenje kako da napišem SP ili funkciju.

Dakle, imam tri tabele:

Code:

BA (ID, BID, AID, Cena, Kolicina, ...)
RS (RSID, BID, ...)
RSA (RSID, AID, Kolicina, ...)

Relacije:

Code:

1. RS.BID = BA.BID
2. RSA.RSID = RS.RSID
3. RSA.AID = BA.AID

Kratko pojašnjenje:
Tabela BA predstavlja stavke porudžbenica. Tabela RS je master tabela iz sistema za praćenje reklamacija. Tabela RSA je detail tabela iz sistema za praćenje reklamacija. Po svakoj porudžbenici može da se napravi jedna ili više reklamacija (relacija 1). Za svaku reklamaciju može da se unese jedan ili više proizvoda sa porudžbenice (relacija 2). Svaki proizvod iz porudžbenice može da se pojavi jedan ili više puta u detaljima reklamacije zato što za svaku jedinicu proizvoda može da se unese drugačiji razlog za reklamaciju (relacija 3). Pri svakom unošenju nove stavke u detaljima reklamacije unosi se i količina istog proizvoda kome se dodeljuje isti razlog za reklamaciju. Ukupna količina za reklamaciju za jedan proizvod sa porudžbenice ne može da bude veća od količine tog proizvoda na porudžbenici.

Prilikom kreiranja nove reklamacije, potrebno je prikazati sve proizvode koji se nalaze na porudžbenici pod sledećim uslovima:
1. - ako za tu porudžbenicu nema reklamacija prikazati sve artikle
2a. - ako se proizvod ne nalazi u tabeli RSA (ima reklamacija za druge proizvode sa te porudžbenice),
2b. - ako se proizvod nalazi u tabeli RSA ali je ukupna količina za taj proizvod manja od količine u porudžbenici,
3. - prikazana količina mora da bude jednaka količini iz porudžbenice ili razlika između ukupne količine za taj proizvod iz reklamacije i količine iz porudžbenice.

Nadam se da nisam nešto propustio da definišem. Ja sam napravio jednostavan upit koji mi vraća jedan deo rezultata ali ne mogu da nađem optimalan način da ispoštujem sve navedene uslove. Jedino što mi pada na pamet jeste da pišem SP ili funkciju (zato što rezultat mora da bude DataSet), ali bih da probam da to izbegnem.

Pitajte ako nešto nije jasno ili ako treba još pojašnjenja.

PS: Ovo nije zadatak već praktičan problem. Svakako mogu da rešim sam (za nešto više vremena metodom probe i greške ili pomoću SP/Funkcije), ali bih voleo da vidim dali ipak može upit da reši celu stvar.

Unapred hvala

"There is a theory which states that if ever anybody discovers exactly what the
Universe is for and why it is here, it will instantly disappear and be replaced by
something even more bizarre and inexplicable. There is another theory which states
that this has already happened."
-- Douglas Adams

Odgovor na temu

dekibre
Dejan Mladenovic
Oslo, Norveska

Član broj: 21820
Poruke: 246
*.79-161-70.customer.lyse.net.

Sajt: dekibre.on.w802.net/index..

+4 Profil

Re: Pomoć oko kompleksnog upita

^{27.04.2009. u 23:39 - pre 182 meseci}

Skript tabela koje sam ja koristio za tvoj primer:

Code:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Reklamacija_Porudzbenica]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Reklamacija] DROP CONSTRAINT FK_Reklamacija_Porudzbenica
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_StavkePorudzbenice_Porudzbenica]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[StavkePorudzbenice] DROP CONSTRAINT FK_StavkePorudzbenice_Porudzbenica
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ReklamacijaDetalji_Reklamacija]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ReklamacijaDetalji] DROP CONSTRAINT FK_ReklamacijaDetalji_Reklamacija
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ReklamacijaDetalji_StavkePorudzbenice]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ReklamacijaDetalji] DROP CONSTRAINT FK_ReklamacijaDetalji_StavkePorudzbenice
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ReklamacijaDetalji]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ReklamacijaDetalji]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Reklamacija]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reklamacija]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StavkePorudzbenice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StavkePorudzbenice]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Porudzbenica]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Porudzbenica]
GO

CREATE TABLE [dbo].[Porudzbenica] (
    [PorudzbenicaID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Reklamacija] (
    [ReklamacijaID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [PorudzbenicaID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StavkePorudzbenice] (
    [PorudzbenicaID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ProizvodID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ImeProizvoda] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Cena] [decimal](10, 2) NOT NULL ,
    [Kolicina] [decimal](18, 2) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ReklamacijaDetalji] (
    [ReklamacijaID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [RazlogID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [PorudzbenicaID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ProizvodID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Kolicina] [decimal](18, 2) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Porudzbenica] ADD
    CONSTRAINT [PK_Porudzbenica] PRIMARY KEY  CLUSTERED
    (
        [PorudzbenicaID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Reklamacija] ADD
    CONSTRAINT [PK_Reklamacija] PRIMARY KEY  CLUSTERED
    (
        [ReklamacijaID],
        [PorudzbenicaID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[StavkePorudzbenice] ADD
    CONSTRAINT [PK_StavkePorudzbenice] PRIMARY KEY  CLUSTERED
    (
        [PorudzbenicaID],
        [ProizvodID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[ReklamacijaDetalji] ADD
    CONSTRAINT [PK_ReklamacijaDetalji] PRIMARY KEY  CLUSTERED
    (
        [ReklamacijaID],
        [RazlogID],
        [PorudzbenicaID],
        [ProizvodID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Reklamacija] ADD
    CONSTRAINT [FK_Reklamacija_Porudzbenica] FOREIGN KEY
    (
        [PorudzbenicaID]
    ) REFERENCES [dbo].[Porudzbenica] (
        [PorudzbenicaID]
    )
GO

ALTER TABLE [dbo].[StavkePorudzbenice] ADD
    CONSTRAINT [FK_StavkePorudzbenice_Porudzbenica] FOREIGN KEY
    (
        [PorudzbenicaID]
    ) REFERENCES [dbo].[Porudzbenica] (
        [PorudzbenicaID]
    )
GO

ALTER TABLE [dbo].[ReklamacijaDetalji] ADD
    CONSTRAINT [FK_ReklamacijaDetalji_Reklamacija] FOREIGN KEY
    (
        [ReklamacijaID],
        [PorudzbenicaID]
    ) REFERENCES [dbo].[Reklamacija] (
        [ReklamacijaID],
        [PorudzbenicaID]
    ),
    CONSTRAINT [FK_ReklamacijaDetalji_StavkePorudzbenice] FOREIGN KEY
    (
        [PorudzbenicaID],
        [ProizvodID]
    ) REFERENCES [dbo].[StavkePorudzbenice] (
        [PorudzbenicaID],
        [ProizvodID]
    )
GO

Upit koji bi moga da ti vrsi posao (ulazni parametar PorudzbenicaID):

Code:

SELECT distinct sp.[PorudzbenicaID], sp.[ProizvodID], sp.[ImeProizvoda],sp.[Kolicina] -
isnull((
SELECT sum(isnull(RDinner.[Kolicina],0))
FROM [Test].[dbo].[ReklamacijaDetalji] RDinner
where
rd.PorudzbenicaID = RDinner.PorudzbenicaID
and rd.ProizvodID = RDinner.ProizvodID
group by RDinner.[PorudzbenicaID], RDinner.[ProizvodID]

)
, 0 ) as resto
FROM [Test].[dbo].[StavkePorudzbenice] sp
left outer join
[Test].[dbo].[ReklamacijaDetalji] rd
on
sp.PorudzbenicaID = rd.PorudzbenicaID
and sp.ProizvodID = rd.ProizvodID
where sp.PorudzbenicaID = '00001'
and
sp.[Kolicina] - isnull(rd. [Kolicina], 0) > 0

You can fool some people sometimes,
But you can't fool all the people all the time. (Bob Marley)

Odgovor na temu

rambo
Dejan Petković
Beograd

Član broj: 6095
Poruke: 190
*.eunet.yu.

+6 Profil

Re: Pomoć oko kompleksnog upita

^{28.04.2009. u 13:22 - pre 182 meseci}

Dejane, hvala na odgovoru, ali nije to to.

Prvo, ostao sam dužan jednu informaciju. U bazi ne postoje nikakve fizičke relacije između tabela (nema stvarnog referencijalnog integriteta), već se sve "veze" između tabela ostvaruju kroz upite (ne pitaj zašto je to tako).

Drugo, upit koji si dao mi ne vraća rezultat koji mi treba. Tu je negde, ali opet nije to to. Rezultat u koloni Resto je uvek negativan što nije cilj. Takođe, čini mi se da isti upit pokriva samo neke od zadatih uslova.

Sada mi preostaje da probam da nekako sam sklepam taj upit (ako je ikako moguće da se SVI traženi uslovi zadovolje jednim upitom), ili da u krajnjem slučaju napišem funkciju koja kao rezultat vraća Table i da tu onda vratim rezultate za sve varijante koje mi trebaju.

Ne znam šta su ostali zaključili na osnovu svega što sam prvobitno napisao. Zahtevi verovatno izgledaju malo čudno, ali to je tako jer se tako traži. Ja samo moram sve to da zadovoljim.

Toliko za sada. Ako nađem rešenje, postaviću ovde da svi vide. Ako se nađe još neko ko bi mogao da proba ovo da reši, bio bih mu zahvalan pre svega što bi mi svaka ideja, pa makar i pogrešna, značila da sam dođem do rešenja problema.

Još jednom, unapred hvala.

Odgovor na temu

rambo
Dejan Petković
Beograd

Član broj: 6095
Poruke: 190
*.eunet.yu.

+6 Profil

Re: Pomoć oko kompleksnog upita

^{28.04.2009. u 16:13 - pre 182 meseci}

Problem je rešen!

Trenutno ne mogu da postujem rešenje jer je zadatak bio "apstraktan". Kada budem prepravio SQL da odgovara zadatku, postovaću ovde.

Problem je, kao prvo, bio u jednom JOINu, ali i u onom delu gde se proverava preostala količina. To je rešeno korišćenjem CASE..WHEN konstrukcije.

Dejan je ipak malo pomogao u rešavanju problema pa njemu još jednom hvala.

Konačno rešenje stiže uskoro.

Odgovor na temu

dekibre
Dejan Mladenovic
Oslo, Norveska

Član broj: 21820
Poruke: 246
*.79-161-70.customer.lyse.net.

Sajt: dekibre.on.w802.net/index..

+4 Profil

Re: Pomoć oko kompleksnog upita

^{28.04.2009. u 21:43 - pre 182 meseci}

Citat:

rambo: Problem je rešen!

Cestitam, kad stignes ti posalji resenje.

Citat:

rambo: Dejan je ipak malo pomogao u rešavanju problema pa njemu još jednom hvala.

Nema na cemu.

Citat:

rambo: Dejane, hvala na odgovoru, ali nije to to.

Neznam dje je ba zapelo, ali to sada nije ni vazno bitno je da si resio problem.

Citat:

rambo:
Prvo, ostao sam dužan jednu informaciju. U bazi ne postoje nikakve fizičke relacije između tabela (nema stvarnog referencijalnog integriteta), već se sve "veze" između tabela ostvaruju kroz upite (ne pitaj zašto je to tako).

Nije to nista cudno, mi imamo u nasem resenju preko 1500 tabela i nijedan primary key i nijedan foreign key tj. bez referencijalnog integriteta celokupnu bazu, pa resenje fercera bez problema po citavom svetu. Ako znas sta radis nijedno resenje nije cudno.

You can fool some people sometimes,
But you can't fool all the people all the time. (Bob Marley)

Odgovor na temu