Transact SQL, Utplukk via kodeverdier med Poivot resultat.

[Gjest Slettet-Pqy3rC]

Meh... dette er ikke mitt felt så jeg ber nok en gang om hjelp fra ekspertene;

 

I en database finnes to tabeller;

 

Tab A eksempel

[table]

IDLowHigh

157

269

379

[/table]

 

ID er en referanse.

Low & High definerer et numerisk område, Low <= High. Hvert enkelt tall i angitt område refereres til som en 'Slot'.

 

Tab B eksempel

[table]

IDPatternAction

10A

11B

10C

23C

32C

[/table]

 

ID er en referanse til post i TAB A.

Pattern er en kode som angir et gjentagelsesmønster, eller hvilke slots som er aktive innen utfra området angitt i tabell A. Se tabell under.

Action er en referanse til hvilken oppgave som skal utføres (annen tabell, ikke viktig for eksempelet).

 

Pattern koder:

[table]

PatternBetydning

0Alle slots

1Kun på første slot (low

2Kun på siste slot (High)

3Kun på andre slot (Low + 1)

[/table]

Det er totalt 48 ulike koder, men har noen et forslag til løsning som jeg forstår skal jeg sikkert få lagt inn resten selv.

 

Ønsker resultat;

 

Jeg skal lage en slags poivot. Med data angitt over skal sluttresultatet bli noe sånt som dette;

 

[table]

ActionSlot 5Slot 6Slot 7Slot 8Slot 9

A111[/td]

B1

C112

[/table]

 

For å ta et spesifikt tilfelle, ta første rad i Tab B:

[table]

IDPatternAction

10A

[/table]

Via ID er denne tilknyttet raden med ID = 1 i Tab A. Denne raden angir Low = 5 og High = 7.

Pattern er oppgitt til 0 = "Alle slots". Det blir da Slot's 5, 6 og 7

Action er A.

A skal altså inn i kolonnene for slots 5, 6 og 7.

 

 

Håper dette ble noenlunde forståelig og takker for all hjelp.

Endret 6. desember 2012 av Slettet-Pqy3rC

[quantum]

hvor er tabellen med alle slot'ene?

[Gjest Slettet-Pqy3rC]

hvor er tabellen med alle slot'ene?

... ?

En slot er bare en del/bit av en rekkefølge, dvs et logisk "hull" hvor en kan plassere en oppgave (Action).

 

 

Dette er utfordringen;

 

Tab A

[table]

IDLowHigh

157

[/table]

Tab B

[table]

IDPatternAction

10A

11B

10C

[/table]

 

Tab A angir altså slot'ene 5,6,7 (fra Low til High), mens Tab B forteller hvilke av, og hvordan, slot'ene (altså 5,6,7 i dette tilfellet) skal benyttes. (Se OP for hva Pattern kodene betyr).

 

Tilsammen utgjør tabell A og B over noe som heller kunne/burde vært oppgitt i en tabell;

 

Tab C

[table]

SlotAction

5A

6A

7A

5B

5C

6C

7C

[/table]

 

...som hadde vært grei å lage en pivot ut fra. Men den finnes altså ikke.

 

Altså; Hvordan kan jeg ved hjelp av Transact SQL:

Lage Tab C ut fra data som finnes i Tab A + Tab B.

Endret 6. desember 2012 av Slettet-Pqy3rC

[quantum]

Kan ikke hjelpe med spesifikk TSQL her, men dersom du kan lage en funksjon getSlots(pattern, low, high) som returnerer en temptabell med de aktuelle slotnumrene, så kan du også joine A og B med denne og få C. Temptabell er ikke nødvendigvis noe man ønsker å bruke hvis man ikke må, så det kan hende det er bedre å gå for en løsning med for-løkker i stedenfor join.

 

Dette er antagelig noe det hadde vært best å løse utenfor databasen, har du mulighet til det?

 

Har du mulighet til å endre databaseskjema, opprette tabeller?

 

Hvordan utvikler pattern->slots mappingen seg utover i rekka? Er det et logisk mønster eller vil det være en «bingo»-mapping som også kan endres over tid?

[Gjest Slettet-Pqy3rC]

Kan ikke hjelpe med spesifikk TSQL her, men dersom du kan lage en funksjon getSlots(pattern, low, high) som returnerer en temptabell med de aktuelle slotnumrene, så kan du også joine A og B med denne og få C. Temptabell er ikke nødvendigvis noe man ønsker å bruke hvis man ikke må, så det kan hende det er bedre å gå for en løsning med for-løkker i stedenfor join.

Ok. Da er det ihvertfall to av oss som er enige om at denne er smått lei med TSQL. Hovedproblemet er at brukere her forventer en kjapp tilbakemelding av sluttresultat. Jeg er smått redd for at før-løkker etc blir noe tidkrevende, ikke minst fordi databasen har en del annet den driver med.

Dette er antagelig noe det hadde vært best å løse utenfor databasen, har du mulighet til det?

Jepp. Hvilket også er planen dersom jeg må.

Jeg leser SQL Server data "direkte" via OLE-DB API'et og fordeler data til "Live tabeller/rapporter" på displayskjermer i produksjonslokaler (eller printe/eksporte PDF, XML, RTF, Excel etc).

 

Problemet med i gjøre databearbeiding i koden (C++/C/ASM) er at produktet blir mye mer versjonsbundet. Alle TSQL statements er konfigurerbare noe som gir stor fleksibiltet for enkeltinstallasjoner, databearbeiding i koden vil ikke være fleksibel mht versjoner av systemet.

 

Har du mulighet til å endre databaseskjema, opprette tabeller?

Ikke endre eksisterende skjema, men jeg kan opprette egne tabeller hvis jeg må.

Hvordan utvikler pattern->slots mappingen seg utover i rekka? Er det et logisk mønster eller vil det være en «bingo»-mapping som også kan endres over tid?

Rimelig tilfeldig, men det er imidlertid ikke altfor mye data i disse tabellene. Dessuten er noen av "Pattern" kodene mye mer brukt enn andre (Det er totalt 48, men jeg har kun sett 6 forskjellige i bruk hittil).

[quantum]

Nei, har ingen grunn til å tro at dette er spesielt vanskelig å løse i TSQL, men jeg har nå engang ikke rørt TSQL på 10 år og det blir for mye styr å gjøre noe med det.

 

Utfra tabellene du har antydet er det ingen grunn til å være redd for at for-løkker ødelegger ytelsen, jeg tror det er mer styr for databasen å produsere tmp-tabeller du kunne ha joinet med.

 

Hvis du til stadighet må legge til og endre logikk i databasen istedenfor i applikasjonskode så betyr det bare at du stapper evt. versjonsproblematikk i databasen også. Det du trenger for å løse opp i versjonsproblematikk er stabile api'er. Det høres ut som du lar databasegrensesnittet være et slikt stabilt api, og det er ikke sikkert det er dumt, men du er ikke *nødt* til å legge grensen der. Du kan legge et appilkasjonslag oppå og la dét være det stabile api'et utad. Da kan du tillate deg å flytte logikk ut av databasen og inn i dette laget.

 

«Rimelig tilfeldig, men det er imidlertid ikke altfor mye data i disse tabellene.»

Fins disse pattern->slots-mappingene i tabeller allerede? Isåfall kan vel dette løses pent og pyntelig med helt vanlig sql skulle jeg tro?

[Gjest Slettet-Pqy3rC]

Utfra tabellene du har antydet er det ingen grunn til å være redd for at for-løkker ødelegger ytelsen, jeg tror det er mer styr for databasen å produsere tmp-tabeller du kunne ha joinet med.

Bør ha resultatet her inn i en tabell fordi dette (Tab C) er bare en del av det endelige resultatet. Jeg binder alle del resultatene sammen til slutt. Mye av det andre har jeg klart.

Hvis du til stadighet må legge til og endre logikk i databasen istedenfor i applikasjonskode så betyr det bare at du stapper evt. versjonsproblematikk i databasen også. Det du trenger for å løse opp i versjonsproblematikk er stabile api'er. Det høres ut som du lar databasegrensesnittet være et slikt stabilt api, og det er ikke sikkert det er dumt, men du er ikke *nødt* til å legge grensen der. Du kan legge et appilkasjonslag oppå og la dét være det stabile api'et utad. Da kan du tillate deg å flytte logikk ut av databasen og inn i dette laget.

Denne databasen benyttes av produkter jeg ikke har kontroll på så den jeg får ikke gjort stort med den. Eventuelle endringer vil i så tilfelle gjøres av andre (lite trolig, men allikevel mulig).

Et applikasjonslag (API) vil begrense funksjonaliteten en del fordi et lag som dekker alt vi skal gjøre vil ta altfor lang tid å lage.

Så, det jeg gjør er i prinsipp å legge alle T-SQL statements konfigurerbare, disse danner således det API'et programmet behøver.

«Rimelig tilfeldig, men det er imidlertid ikke altfor mye data i disse tabellene.»

Fins disse pattern->slots-mappingene i tabeller allerede? Isåfall kan vel dette løses pent og pyntelig med helt vanlig sql skulle jeg tro?

Nei. Kun Tab A og Tab B finnes.

[Gjest Slettet-Pqy3rC]

Okey...

 

Har nå kjørt noen tester med cursor og lager temp tabell (Tab C). Ser ut til å funke ganske greit mht tempo, litt på grunn av at det ikke er allverdens med data i tabellene (Tab A og Tab B).

 

Noe sånt, ca;

 

DECLARE @TabC as TABLE (slot int,action int);
DECLARE @vid int,@vlow int,@vhigh int,@vpattern int,@vaction int
DECLARE @curslot int

DECLARE tabab_curs CURSOR FOR
SELECT
TabA.ID,
TabA.Low,
TabA.High,
TabB.Pattern,
TabB.Action
FROM TabA
INNER JOIN TabB on TabA.ID = TabB.ID

OPEN tabab_curs
FETCH NEXT FROM tabab_curs
INTO @vid,@vlow,@vhigh,@vpattern,@vaction

WHILE @@FETCH_STATUS = 0
BEGIN
 IF @vpattern = 0
	 BEGIN
	 SET @curslot = @vlow
	 WHILE (@curslot <= @vhigh)
	 BEGIN
		  INSERT INTO @TabC (slot,action) VALUES (@curslot,@vact)
		  SET @curslot = @curslot + 1
	 END
	 END
 ELSE IF @vpattern = 1
    INSERT INTO @TabC (slot,action) VALUES (@vlow,@vact)
 ELSE IF @vpattern = 2
    INSERT INTO @TabC (slot,action) VALUES (@vhigh,@vact)
 --.... (more pattern type checks) ...

  FETCH NEXT FROM tabab_curs
  INTO @vid,@vlow,@vhigh,@vpattern,@vaction
END

CLOSE tabab_curs
DEALLOCATE tabab_curs

--... handle the complete TabC here...

Jeg bruker egentlig andre felt/tabell navn, men prinsippet blir jo det samme.

Endret 7. desember 2012 av Slettet-Pqy3rC