Skip to end of metadata
Go to start of metadata
Sisällysluettelo


KEH-1932 - Getting issue details... STATUS
Tälle sivulle on lisätty usein käytettyjä/kysyttyjä SQL-kyselyitä. Ole hyvä ja tutustu seuraavan sivuston sisältöön: https://www.w3schools.com/SQL/deFault.asp

Yleisiä huomioita

Päivämääräkentät

Pervasive käsittelee päivämääriä tyyliin vvvv-kk-pp

select * from tietokanta where pvm>'2012-05-30'

Osassa tauluissa päivämäärä on asetettu 8 merkin merkkijonolla 'vvvvkkpp'

select * from tietokanta where pvm='20120530'

Mikäli halutaan tehdä kyselyitä näiden kesken, voidaan merkkijonomuotoiset muuttaa pervasiven päivämääräkentiksi seuraavalla kaavalla:

convert(substring(pvm,1,4)+'-'+substring(pvm,5,2)+'-'+substring(pvm,7,2),sql_date)



tai  uudehkoilla ddf:llä
yyyymmddtodate( pmv ) 

eli esimerkiksi:

select * from tietokanta where convert(substring(pvm,1,4)+'-'+substring(pvm,5,2)+'-'+substring(pvm,7,2),sql_date)=curdate()

Jos haluat muuttaa pervasiven päivämääräkentän ns. suomalaiseksi päivämääräksi, voit käyttää seuraavaa kyselyä:

concat(concat(concat(concat(day(perustuspvm),'.'),month(perustuspvm)),'.'),year(perustuspvm))

Kellonaikakentät

Tuotemyynnin kellonajan esitys muodossa hh:mm eli esim 09:48

select pvm,kello, (kello/60) as tunti, mod(kello,60) as minuutti, concat(concat(right('00'+convert(convert(kello/60,sql_integer),sql_char),2),':'),right('00' +convert(mod(kello,60),sql_char),2)) as kloaika from tuotemyy ;


Tuotemyynnin aikaleiman esittämien muodossa "vvvvv-kk-pv hh:mm"

select pvm,kello,(substring(pvm,1,4)+'-'+substring(pvm,5,2)+'-'+substring(pvm,7,2)+ ' ' +concat(concat(right('00'+convert(convert(kello/60,sql_integer),sql_char),2),':'),right('00' +convert(mod(kello,60),sql_char),2))) as transaction_timestamp from tuotemyy ;


Ostohistorian kellonajan esitys muodossa hh:mm eli esim 09:48

select pvm, klo,right('00' +substring(klo,0,length(klo)-2),2) as tun, right('00'+right(klo,2),2) as minuutti, (right('00'+substring(klo,0,length(klo)-2),2)+':' +right('00'+right(klo,2),2)) as aika from osto ; 


Ostohistorian aikaleiman esittämien muodossa "vvvvv-kk-pv hh:mm"

select pvm, klo,(substring(pvm,1,4)+'-'+substring(pvm,5,2)+'-'+substring(pvm,7,2)+' '+right('00'+substring(klo,0,length(klo)-2),2)+':' +right('00'+right(klo,2),2)) as transaction_timestamp from osto ;


Satunnaisia vinkkejä

SKJSQL tuottaa oletuksena tabulaattorierotteisia tiedostoja.
Mikäli haluat konvertoida tabulaattorit esim puolipisteiksi, voit tehdä sen esimerkiksi vbscriptillä:
TabToCsv.vbs:

Const ForReading = 1, ForWriting = 2
Dim fs, txt, contents
Set fs = CreateObject("Scripting.FileSystemObject")
Set txt = fs.OpenTextFile(WScript.Arguments(0), ForReading)
contents = txt.ReadAll
txt.Close
contents = Replace(contents, vbTab, ";")
Set txt = fs.OpenTextFile(WScript.Arguments(0), ForWriting)
txt.Write contents
txt.Close

Ohjelmaa kutsutaan esimerkiksi:

start /wait cscript c:\winskj\TabToCsv.vbs %TEMP%\outputfile.tsv

Reskontran käyttöönotto

--Poistaa kaikki suoritukset
delete from lassaa ;
--Lisää laskuille suoritusrivit
insert into lassaa(laskunro,rivinro,maksupvm,maksettu,kirpvm,maksutapa)
(select laskunro,1,erapvm,laskunsumma,erapvm,1 from laskut where laskunro< 9999999) ;
--Merkitsee laskun päätietueen suoritetuksi
update laskut set tila = 3, suoritettuyhteensa=laskunsumma where laskunro < 999999 ;

Ominaisuuden (verkkokauppa) lisääminen tuotteille

Lisätään tuotteille, joiden seurantaryhmä on 1000, ominaisuus 15 (Tuoteominaisuusid), arvolla Kyllä.

INSERT INTO TUOTEOMI(TUOTE,OMINAISUUSID,ARVOB)
SELECT NUMERO,15,1 FROM TUOTE
WHERE NUMERO NOT IN(SELECT TUOTE FROM TUOTEOMI WHERE OMINAISUUSID=15)
AND SEURRYHMA=1000 ;

"Orpotietueiden" lisääminen tuote/asiakas-tauluihin

Mikäli lisätään esim importilla tjms tuotteita/asiakkaita järjestelmään, jää usein puuttumaan tuotteiden/asiakkaiden tietueet tuote2/asiakas2 -tauluista.

insert into asiakas2 (asiakasnro)
  select asiakas from asiakas
   where not exists (select asiakasnro from asiakas2 where asiakasnro = asiakas) ;
delete from asiakas2
  where not exists (select asiakas from asiakas where asiakas=asiakasnro) ;
 
insert into tuote2 (numero)
  select numero from tuote
   where not exists (select numero from tuote2 where tuote2.numero=tuote.numero) ;
delete from tuote2
  where not exists (select numero from tuote where tuote.numero=tuote2.numero) ;

Asiakashinnaston korotus

Ajetaan kaikille tuotehinnastoilla asiakkaille korotus 5% (asikastyyppi=0 tarkoittaa asiakasta, 1=ryhmä, 2=webryhmä). Tyyppi=1 tuote, 2=tr, 3=pr. Aliryhmät omassa taulussa

update asale set hinta=round(hinta*1.05,2) where tyyppi=1 and AsikasTyyppi=0

Tuotekuvien polun muutos

Mikäli halutaan muuttaa kaikki kuvat polusta F:\KUVAT polkuun \\KUVAPALVELIN\KUVAT, voidaan se tehdä seuraavasti:

UPDATE TUOTEKUV SET TIEDOSTO=REPLACE(TIEDOSTO,'F:\KUVAT','\\KUVAPALVELIN\KUVAT')

Myyntihinnan laskeminen tuoteryhmän kateprosentista

Laskee kaikille tuotteille myyntihinnaksi tuotteen ryhmään määritellyn kateprosentin. Mikäli kateprosentti=0, myyntihinnaksi tulee ostohinta.

update tuote 
set hinta=keskihinta/(select ((100-tavoitekatepros)/100) from tryhma where tuote.ryhma=tryhma.nro)


Asiakaskorttien luonti yhdelle asiakkaalle

Lisää asiakkaalle 1000 asiakaskortit väliltä 20000-39999 käyttäen proseduuria.

Ensin luodaan proseduuri ja tämän jälkeen suoritetaan se oikein parametrein. Proseduuri tallentuu .ddfiin, eli käytännössä häviää seuraavassa ohjelmistopäivityksessä.

create procedure luokortit (in :asiakas integer, in :alku integer, in :loppu integer) ;
begin
  declare :i integer;
  set :i = :alku;
  while (:i <= :loppu) do
    insert into askort(asiakas,kortti) values (:asiakas, convert(:i, sql_varchar));
    set :i = :i + 1;
  end while;
end ;

call luokortit (1000,20000,39999);

Veroluokkien haku tuotteille tuoteryhmiltä

Hakee tuotteille ryhmät tuotteille merkityiltä tuoteryhmiltä:

update tuote set 
 alv=(select vero from tryhma where tuote.ryhma=tryhma.nro),
 ostovero=(select ostovero from tryhma where tuote.ryhma=tryhma.nro)

Asiakasnumeroalueen siirto

Näillä sql-lausekkeilla voidaan siirtää asiakasnumerot toiselle alueelle (esim asiakas 2 -> 20002).
Tämä siirtää myös tuotemyynnit, asiakashinnastot ja asiakaskertymät taannehtivasti.

update asiakas set asiakas=asiakas+20000 ;
update asiakas2 set asiakasnro=asiakasnro+20000 ;
update tuotemyy set asiakas=asiakas+20000 ;
update asker set asiakas=asiakas+20000 ;
update asale set asiakasnumero=asiakasnumero+20000 where asikastyyppi=0 ;

Hyllynreunalappujen luonti

Mikäli halutaan lisätä kaikki tuotteet hyllynreunalappuihin (esimerkiksi uuden myymälän perustamisvaiheessa), voidaan se toteuttaa seuraavalla komennolla:

--Tyhjätään ensin hyllynreunalapputaulu
delete from hr ;
--Lisätään tuotteet
insert into hr(tyyppi,tuote,maara) select 1,numero,-1 from tuote

Varattu / tilattu -saldojen korjausajo

Mikäli syystä tahi toisesta tuotteiden tuotekohtaiset tilattu / varattumäärät ovat väärin, voidaan ne ajaa uudestaan aktiivisista osto- ja myyntitilauksista:

Varattu-saldojen ajo (myyntitilauksista)

HUOM! Varastopaikaksi oletetaan kyselyssä olevan 1. Mikäli varastopaikka on joku muu, muuta se kuuteen(6) kohtaan.

-- laskee toimittamattomat saldot tilauksista joiden
-- toimitustila on pienempi tai yhtasuuri kuin osittain toimitettu ja eivat ole esitilauksia.
-- huomioi värikoko saldot. ei voi ajaa tyhjään varastosaldoon (tietueet pitää olla olemassa)
-- varastopaikka rajataan 1:n. Rajaus on kuudessa kohti. ajo on tehty ajettavaksi yhdelle varastolle
create table varattu (tnro varchar(18),i1 integer,i2 integer, vsaldo double,primary key(tnro,i1,i2))#
insert into varattu (
  select tuote,0,0,sum(toimitettava) from mtrivi,mt where mtrivi.tilausnro=mt.numero and mt.toimitustila <4 
  and mtrivi.varastopaikka in (1)
  and mt.esitilaus=0 and VariKokoLapsi=0
  group by tuote)#
update varsaldo set varattu=0 where varasto in (1)#
update varsaldo set varattu = (select vsaldo from varattu where tuote=tnro),muutospvm=now(), muutosklo=now() where varasto in (1)#
delete from varattu #
insert into varattu (
  select tuote,id1,id2,sum(toimitettava) from mtrivi,mt where mtrivi.tilausnro=mt.numero and mt.toimitustila <4 
  and mtrivi.varastopaikka in (1)
  and mt.esitilaus=0 and VariKokoLapsi=1
  group by tuote,id1,id2)#
update lajvs set varattu=0 where varasto in (1)#
update lajvs set varattu = (
  select vsaldo from varattu where tuote=tnro and i1=Lajitelma1 and i2=Lajitelma2 ), muutospvm=now(), muutosklo=now() where varasto in (1)#
drop table varattu#

Tilattu -saldojen ajo (ostotilauksista)

Muuta varastopaikka kuuteen(6) kohtaan.

-- tämä sql ajo asettaa tuotteiden tilattu saldot toimittamattomilta tilauksilta
-- varastopaikka pitää muuttaa sinne missä siihen viitataan
create table tilattu (tnro varchar(18),i1 integer,i2 integer, vsaldo double,primary key(tnro,i1,i2))#

insert into tilattu (
 select tuote,0,0,sum(tulematta) from winotri,winot where winotri.tilausnro=winot.tilausnro and 
 winot.tila between 1 and 4 and winotri.varastopaikka in (1)
 and VariKokoLapsi=0
 group by tuote)#
update varsaldo set tilattu=0 where varasto in (1)#

update varsaldo set tilattu = (select vsaldo from tilattu where tuote=tnro),muutospvm=now(), muutosklo=now() where varasto in (1)#
delete from tilattu #
insert into tilattu (
 select tuote,id1,id2,sum(tulematta) from winotri,winot where winotri.tilausnro=winot.tilausnro and 
 winot.tila between 1 and 4 and winotri.varastopaikka in (1) and VariKokoLapsi=1
 group by tuote,id1,id2)#
update lajvs set tilattu=0 where varasto in (1)#
update lajvs set tilattu = (
  select vsaldo from tilattu where tuote=tnro and i1=Lajitelma1 and i2=Lajitelma2 ), muutospvm=now(), muutosklo=now() where varasto in (1)#

drop table tilattu#

Tuotenumeroiden muutos

Tuotenumerot voidaan muuttaa seuraavan proceduurin avulla (aja tämä esim Pervasive Control Centerissä):

create procedure tuotenromuutos(in :vanha varchar(18), in :uusi varchar(18)) ;
 begin
update ASALE set tuote=:uusi where tuote =:vanha AND tyyppi=1;
update casiomp set nro = :uusi where nro = :vanha;
update fifokaytto set tuote = :uusi where tuote =:vanha;
update fifosaldo set tuote = :uusi where tuote =:vanha;
update hjteksti set tuote = :uusi where tuote =:vanha;
update hintajul set tuote = :uusi where tuote =:vanha;
update kamppanja set tuote = :uusi where tuote =:vanha;
update kpaal set tuote = :uusi where tuote =:vanha;
update kpaal set kpaaltuote = :uusi where kpaaltuote =:vanha;
update lajivk set tuote = :uusi where tuote = :vanha;
update lajlisatieto set tuote = :uusi where tuote = :vanha;
update lisavaruste set tuote = :uusi where tuote = :vanha;
update lisavaruste set varuste_tuote = :uusi where varuste_tuote = :vanha;
update lajihinta set tuote = :uusi where tuote = :vanha;
update lajpasv set tuote = :uusi where tuote = :vanha;
update lajvs set tuote = :uusi where tuote = :vanha;
update ostokamp set tuote = :uusi where tuote = :vanha;
update resepti set rtuote = :uusi where rtuote = :vanha;
update resepti set tuote = :uusi where tuote = :vanha;
update sarjanro set tuote = :uusi where tuote = :vanha;
update tpyynto set tuote = :uusi where tuote = :vanha;
update trakenne set tuote = :uusi where tuote = :vanha;
update tuote set numero = :uusi where numero =:vanha;
update tuote set myyntilinkitys = :uusi where myyntilinkitys =:vanha;
update tuote set tuotelinkitys = :uusi where tuotelinkitys=:vanha;
update tuote2 set numero = :uusi where numero = :vanha;
update tuote2 set multituote_tuote = :uusi where multituote_tuote= :vanha;
update tuotedoc set tuote = :uusi where tuote = :vanha;
update tuotekuv set tuotenro = :uusi where tuotenro = :vanha;
update tuotetxt set tuotenro = :uusi where tuotenro = :vanha;
update tuottoim set tuote = :uusi where tuote = :vanha;
update tvkoodi set tuote = :uusi where tuote =:vanha;
update UUSIHINTA set tuote = :uusi where tuote = :vanha;
update varsaldo set tuote = :uusi where tuote = :vanha;
update vresepti set tuote = :uusi where tuote = :vanha;
update vresepti set rivituote = :uusi where rivituote = :vanha;
update WEBKAUPPA_ADS_TUOTE set tuote = :uusi where tuote =:vanha;
update WEBTUOTECATEGORIA set tuote = :uusi where tuote =:vanha;
update WEBRYHMAT set tuote = :uusi where tuote =:vanha;
update YLREK set SELITE = :uusi where tyyppi=7 AND SELITE = :vanha ;
update tuotemyy set tuote = :uusi where tuote=:vanha;
update osto set tuote = :uusi where tuote = :vanha;
update mtrivi set tuote = :uusi where tuote = :vanha;
update winotri set tuote = :uusi where tuote = :vanha;
update olematon set tuote = :uusi where tuote = :vanha;
update lasrivi set tuotenro = :uusi where sisalto = 0 and tuotenro = :vanha;
update kassaldo1 set tuote = :uusi where tyyppi = 0 and tuote = :vanha;
update invtulta set tuote = :uusi where tuote = :vanha;
update lajiinvhis set tuote = :uusi where tuote = :vanha;
update tilkori set tuote = :uusi where tuote = :vanha;
update tillista set tuote = :uusi where tuote = :vanha;
update laheteri set tuotenro = :uusi where sisalto = 0 and tuotenro = :vanha;
update asker set tuote = :uusi where tuote = :vanha;
update kerk set tuote = :uusi where tuote = :vanha;
update kerv set tuote = :uusi where tuote = :vanha;
update kerp set tuote = :uusi where tuote = :vanha;
update kerailykortti set tuote = :uusi where tuote = :vanha;
update vakiolaskutus set tuote = :uusi where tuote = :vanha;
update asryhtuot set tuote = :uusi where tuote = :vanha;
update tuoteomi set tuote = :uusi where tuote = :vanha;
update asemyy set tuotenumero = :uusi where tuotenumero = :vanha;
update aseosto set tuotenumero = :uusi where tuotenumero = :vanha;
update patmyy set tuote = :uusi where tuote = :vanha;
update patosto set tuote = :uusi where tuote = :vanha;
end ;

Nyt, kun proceduuri on luotu, voidaan tuotenumerot muuttaa:

call tuotenromuutos('vanha_tuote_numero','uusi_tuote_numero') ;

eli esimerkiksi:

call tuotenromuutos('0000000000013','0000000000014') ;

Muutoksien jälkeen voidaan poistaa proceduuri:

drop procedure tuotenromuutos ;

Tässä pieni apukoodin tynkä:

Esim. muutospyynntöjen tekeminen juoksevalla numeroinnilla (python)
#coding=windows-1250

lista = ["0000000000013","0000000000014"]
laskuri = 9000
SQL = ''

for x in lista:
    numero = (laskuri)
    laskuri = laskuri.__add__(1);
    numerostr = str( numero )
    SQL += 'call tuotenromuutos(\'{0}\',\'{1}\');\n'.format(x, numerostr)

print(SQL)


Tuotemyynti tilisaldoille

Jos asiakas on vahingossa merkattu saldo loppusummalla ja halutaankin tuoteerittely tilisaldolle (esim. laskutetaan) voidaan tuotemyynnistä kaivella tiedot edellyttäen että maksutapoja ei ole ositettu

insert into kassaldo1 
(Asiakas, KassaNro, Tosite, Myymala, Valuutta, Tarjoustuote,  MyyntiPvm,  Klo, Tyyppi, Tuote, Maara, Ahinta, NettoHinta, Ale, OstoHinta, AlvLuokka, AlvPros, AlvOsuus, TuoteNimi)
(select asiakas, kassa, tosite, myymala, 1, KamppanjaKaytossa, pvm, 0, 1, tuote, maara, ahinta, summa, 100*(maara*ahinta-summa)/maara*ahinta,ostohinta, alvluokka, alvpros, 
summa-(summa/(1+alvpros/100)), nimi from tuotemyy, tuote where numero=tuote and pvm>='20120101' and asiakas in (1,2,3) )   

Kantasaldo -taulujen yhdistäminen

Mikäli halutaan yhdistää esimerkiksi kahden myymälän kanta-asiakas ostokertymätiedot, voidaan yhdistäminen (myymäläkannasta keskuspalvelinkantaan) tehdä seuraavalla kyselyllä:

--Lisätään ensin kantasaldoon kaikkien asiakkaiden tietueet--
insert into KOHDE.kantasaldo(kantasaldoasiakas) select asiakas from asiakas where asiakas not in(select kantasaldoasiakas from kantasaldo);
--Summataan ksostokertyma
update KOHDE.kantasaldo k
     set ksostokertyma=
     ksostokertyma+(select ksostokertyma 
         from LAHDE.kantasaldo L 
         where l.kantasaldoasiakas=k.kantasaldoasiakas)
         where  exists (select ksostokertyma 
         from LAHDE.kantasaldo L 
         where l.kantasaldoasiakas=k.kantasaldoasiakas) ;

Orpoviivakoodien poisto

delete from tvkoodi where not exists (select numero from tuote where tvkoodi.tuote=numero) ;

Näppäinlaskurilokin poisto

Kassan näppäinlaskuriloki saattaa täyttää INI-taulua hyvinkin isoksi. Tämä aiheuttaa hitautta kassan käynnistyksessä.

Tiedot voisi poistaa SQL:llä

delete from ini where sektio='NAPPAINLASKURILOKI' ;

Kyseinen SQL kuitenkin saattaa kestää hyvinkin kauan mikäli dataa on paljon / ympäristö on hidas.

Vaihtoehtoinen tapa on tehdä se seuraavasti:

1. Varmuuskopioi INI.DAT talteen varmuuden vuoksi

2. Luo aputaulu:

CREATE TABLE "INIVAR" USING 'INIVAR.DAT' ( 
 "IniNimi" VARCHAR(40) NOT NULL, 
 "Sektio" VARCHAR(60) NOT NULL, 
 "Avain" VARCHAR(60) NOT NULL, 
 "Data" LONGVARCHAR NOT NULL);
CREATE UNIQUE INDEX "key0" IN DICTIONARY ON "INIVAR" ( 
 "IniNimi" , 
 "Sektio" , 
 "Avain" );

3. Kopioi ei-näppäinlaskurivit väliaikaistauluun;

INSERT INTO INIVAR (Ininimi,Sektio,Avain,Data) select ininimi,sektio,avain,data from INI where NOT sektio='NAPPAINLASKURILOKI';

4. Sulje kaikki ohjelmat, jotka käyttävät tietokantaa (SKJ ja PCC ja vastaavat)

5. Poista INI.DAT

6. Luo se uudelleen käynnistämällä SKJ

7. Siirrä tiedot väliaikaistaulusta INI-tauluun:

INSERT INTO INI (Ininimi,Sektio,Avain,Data) select ininimi,sektio,avain,data from INIVAR;

8. Poista aputaulu

DROP TABLE INIVAR;

Etunimen ja Sukunimen erottaminen

Mikäli sinulla on esimerkiksi yhteyshenkilötaulussa nimet kenttässä 'Nimi' muodossa Etunimi Sukunimi, voit listata sieltä Etunimen ja Sukunimen erikseen seuraavasti:

select asiakasnro,
nimi,
substring(nimi,if(locate(' ',nimi,2)=0,300,locate(' ',nimi,2)+1),length(nimi)) as sukunimi,
left(nimi,locate(' ',nimi,2)) as etunimi
from yhthlo

Jos haluat päivittää kenttään Nimi etunimen ja Nimi2 Sukunimen, tee seuraavasti:

update yhthlo set nimi2=substring(nimi,if(locate(' ',nimi,2)=0,300,locate(' ',nimi,2)+1),length(nimi)) ;
update yhthlo set nimi=left(nimi,locate(' ',nimi,2)) ;

Tietokantataulun kentän siirtäminen tietokannasta toiseen

Mikäli halutaan siirtää tietyn kentän sisältö toisesta tietokannasta toiseen, voidaan se hoitaa seuraavalla SQL:llä.

lahde=Lähdetietokanta
kohde=Kohdetietokanta
kentta=Siirrettävä kenttä

update kohde.tuote as k set k.keskihinta=(select l.keskihinta from lahde.tuote as l where k.numero=l.numero) ;

Myynninpäivityksen poispakottaminen

Mikäli myynninpäivityksestä on jäänyt merkintä, että ajo on vielä päällä, voidaan se poistaa tällä:

delete from ini where ininimi='TOIMINTO' and sektio='MYYNNINPAIVITYS' and avain='MENOSSA' ;

Asiakastietojen lookup cachen poistaminen käytöstä

Nopeuttaa myyntitilauksen käynnistystä

hakusanoja: myyntitilaus hidas, myyntitilauksen hitaus, myyntitilauksen avaus hidasta, myyntitilaus ei aukea

insert into ini (ininimi,sektio, avain, data) values('SKJ.INI','TILAUSHAKU','LOOKUPCACHE', 0)

Tuotteen toimittajan/tilauskoodin vieminen tuotetoimittaja-tauluun

INSERT INTO TUOTTOIM (TUOTE,TOIMITTAJA,TILAUSKOODI) SELECT NUMERO,TOIMITTAJA,TOIMTUNNUS FROM TUOTE WHERE NOT TOIMITTAJA='' AND NOT TOIMTUNNUS='' AND NUMERO NOT IN (select TUOTE FROM TUOTTOIM WHERE TUOTE.TOIMITTAJA=TUOTTOIM.TOIMITTAJA AND TOIMTUNNUS=TILAUSKOODI) ;

Tilisaldojen tuonti ulkoa

Tämä tuo yhdellä 24% alv (luokka 1) tuotteella (nro 100) tilimyyntejä. Eli procedure lisää tapahtuman ja enää pitää generoida lauseita call lisaatiliin (asiakasnumero, summa);

create procedure lisaatiliin ( in :Asiakas integer,in :ahinta double );
begin
insert into  kassaldo1
( Asiakas , KassaNro , Tosite , Myymala ,   MyyntiPvm ,   Tyyppi , Tuote , Maara , Ahinta , NettoHinta ,   AlvLuokka , AlvPros , AlvOsuus , TuoteNimi ) 
values       
( :Asiakas ,'00001' ,1 ,1,  '20150615' ,   1     , '100',1 , :Ahinta ,:ahinta, 1, 24 , round(:ahinta - :ahinta/(1+24/100),2), 'SIIRTOSALDO' ) ;
 update assaldo set kassasaldo = kassasaldo + :ahinta where asiakas = :asiakas ;
 if @@rowcount = 0 then
   insert into assaldo (asiakas, kassasaldo, valuutta) values (:Asiakas, :ahinta, 1);
 end if;
end;

call lisaatiliin (2,120);
call lisaatiliin (3,9);

Toimittajien perustaminen asiakkaiksi

Mikäli halutaan, että toimittajat ovat myös asiakkaita, voidaan tehdä esimerkiksi näin:

insert into asiakas (asiakas,ryhma,ryhma2,nimi,nimi2,sahkoposti,LOOsoite,LOPostinro,LOPostitmp,POOsoite,POPostinro,POPostitmp,Puh1,HenkiloTunnus)
select convert(tunnus,SQL_INTEGER)+100000,1000,1000,nimi,nimi2,sahkoposti,osoite,postinro,postitmp,puhelin1,postinro,postitmp,puhelin1,lytun from "TOIMITTAJA";
insert into astxt(asiakas,selite,teksti)
select convert(toimittaja,SQL_INTEGER)+100000,'INFO',teksti from "ToimTxt" where otsikko='' ;
insert into asiakas2 (asiakasnro) select asiakas from asiakas where not exists (select asiakasnro from asiakas2 where asiakasnro = asiakas) ;
delete from asiakas2 where not exists (select asiakas from asiakas where asiakas=asiakasnro) ;


Tämä perustaa siis toimittajan 1000 asiakkaaksi 101000, siirtää otsikottoman toimittajatekstin asiakastekstiksi otsikolle INFO ja tämän jälkeen lisää myös asiakas2-tauluun tietueen. Tämän jälkeen pitää ajaa hakuindeksien päivitys, jotta asiakashaut toimivat oikein.

Asiakkaiden jakaminen myyjittäin kirjaimen perusteella

update asiakas2 set asiakas2.myyja=4 where asiakasnro in (select asiakas from asiakas where asiakas.ryhma in (1,2) and UPPER(substring(asiakas.nimi,1,1)) between 'T' and 'Ö');

Asettaa asiakkaalle myyjän 4 niille asiakkaille, jotka ovat asiakasryhmässä 1 tai 2 ja niiden nimi alkaa T-Ö -kirjainvälillä.

Myyjien korjauksien/palautuksien havainnointi

Tämä kysely listaa kuukausimyynnin myyjittäin ja tulostaa myyjäkohtaiset palautus/korjausprosentit.

select 
myyja,
substring(pvm,1,6) as kk,
round(sum(nettomk),2) as myynti,
sum(palautus1mk) as palautus1e,
round((sum(palautus1mk)*-1/sum(nettomk))*100,2) as PalautusPros,
sum(korjaus1mk) as korjaus1mk,
round((sum(korjaus1mk)*-1/sum(nettomk))*100,2) as KorjausPros,
sum(kuitpalmk) as KuitPalE,
round((sum(kuitpalmk)*-1/sum(nettomk))*100,2) as KuitPalPros
from rahaliik where pvm like '2015%' group by myyja,kk

Tietokannan rivien määrät

Mikäli halutaan selvittää, kuinka iso tietokanta on, voidaan käyttää seuraavaa kyselyä:

select
(select count(*) from "User") as userCount, /* käyttäjälistaus */
(select count(*) from myymalat) as myymalatCount, /* myymälälistaus */
(select count(*) from mt) as mtCount, /* myyntitilauksia */
(select count(*) from mtrivi) as mtriviCount, /* myyntitilausdataa */
(select count(*) from tuotemyy) as tuotemyyCount, /* tuotteiden myyntiä */
(select count(*) from laskut) as laskutCount, /* laskuja */
/* (select count(*) from lasrivi) as lasriviCount,where sisalto=0; /* laskutusta */
(select count(*) from osto) as ostoCount, /* ostoja */
/*(select count(*) from ot) as otCount, /* ostotilauksia */
/*(select count(*) from otrivi) as otriviCount, /* ostotilausdataa */
(select count(*) from varsaldo) as varsaldoCount, /* varastodataa */
(select count(*) from ASIAKAS) as ASIAKASCount, /* asiakkaita */
(select count(*) from ASRYHMA) as ASRYHMACount, /* asiakasryhmiä */
(select count(*) from tuote) as tuoteCount, /* tuotteita */ 
(select count(*) from PRYHMA) as PRYHMACount, /* pääryhmiä */
(select count(*) from tryhma) as tryhmaCount, /* tuotertyhmiä */
(select count(*) from ALIRYHMA) as ALIRYHMACount, /* aliryhmät */
(select count(*) from ALVKANTA) as ALVKANTACount, /* alv-kannat */
(select count(*) from MYYJA) as MYYJACount, /* myyjiä */
(select count(*) from MYYJARYHMA) as MYYJARYHMACount, /* myyjäryhmiä */ 
(select count(*) from MYYMALAT) as MYYMALATCount, /* myymälöitä */
(select count(*) from KAMPTIEDOT) as KAMPTIEDOTCount, /* tarjouskampanjoiden tietoja */
(select count(*) from INI) as INICount, /* ini-tiedostojen asetuksia */
(select count(*) from mtrivi where rakenneemo = 1) as tuoteRakenneEmotuoteCount, /* tuoterakenne-emotuotteiden määrä (emotuotteet, lapsituote...) */
(select count(*) from mtrivi where OnVariKokoEmo = 1) as VarikokolajitelmaEmotuoteCount, /* värikokolajitelma-emotuotteiden määrä */
(select count(*) from mtrivi where VariKOkoLapsi = 1) as VarikokolajitelmaLapsituoteCount; /* värikokolajitelma-lapsituotteiden määrä */

Myymälän vaihto historiaan

Huomioitavaa on, että kohdemyymälässä ei saa olla tietueita alla ennen siirtoa.

Mikäli halutaan vaihtaa myymälän numero myyntihistoriasta ja muista tietokantatauluista, voidaan se tehdä seuraavalla proseduurilla:

create procedure vaihdaMyymala (in :lahdemyymala integer, in :kohdemyymala integer);
begin
update APAINE set MYYMALA = :kohdemyymala where myymala = :lahdemyymala ;
update ASEMYY set Myymala = :kohdemyymala where myymala = :lahdemyymala ;
update ASKER set Myymala = :kohdemyymala where myymala = :lahdemyymala ;
update CASIOMP set Myymala = :kohdemyymala where myymala = :lahdemyymala ;
update GT set Myymala = :kohdemyymala where myymala = :lahdemyymala ;
update KAMPPANJA set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update KASSALDO set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update KELLOK set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update KERK set myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update KERP set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update KERV set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update KOMPENSIO set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update KSNRO set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update Kysely set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update LAHETE set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update LASKUT set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MAARAALE set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MPLAHERA set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MPTAPAH set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MPTILERA set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MT set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MYYJAMYY set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MYYMMT set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MYYNTIVA set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update OLEMATON set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update PATMYY set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update RAHALIIK set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update RXILAH_TAPAH set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update RXILAH_TILSUMMA set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update RYHMAKAMPANJA set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update SALSUOR set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update SOKKOTILITYS set myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update TSEURA set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update TYOHIST set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update ULOSMAKS set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update UUSIHINTA set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update WINIPLOG set MYYMALA = :kohdemyymala where myymala = :lahdemyymala ; 
update ASIAKAS2 set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update VSTOLOG set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MATKUSTAJATOSITE set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update ALVPAL set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update RFIDLOG set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update BUDJETTI set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update TUOTEMYY set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update BONUSTAPAH set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MUUTOSLOG set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update TOSITE set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update TYOTUNNIT set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MAKSTAPAERITTELY set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update MYYMAUKI set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update KIRPMYY set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
update KORTTIKAYTTO set Myymala = :kohdemyymala where myymala = :lahdemyymala ; 
end;


Nyt, kun proseduuri on luotu, voidaan se ajaa seuraavasti:

call vaihdaMyymala(lahdemyymala,kohdemyymala);


Eli jos halutaan siirtää myymälän 20 myynnit myymälään 99, ajettaisiin se seuraavasti:

call vaihdaMyymala(20,99);


Kun ajot on ajettu, voidaan tämä proseduuri poistaa komennolla:

drop procedure vaihdaMyymala ;


Ostotilausnumeroiden kasvatus


create procedure otnumeromuutos(in :muutos integer) ;
begin
update winot set tilausnro=tilausnro+:muutos;
update winotjako set tilausnro=tilausnro+:muutos;
update winotri set tilausnro=tilausnro+:muutos;
update winotsaapuu set tilnro=tilnro+:muutos;
update "WINOTSTATUS.DAT" set tilausnro=tilausnro+:muutos;
update winottoi set tilausnro=tilausnro+:muutos;
delete from "WINOTTXT" where teksti='';
update winottxt set winottxttilausnro=winottxttilausnro+:muutos;
update osto set tilausnro=tilausnro+:muutos;
update mtrivi set ostotilausnro=ostotilausnro+:muutos where not ostotilausnro=0;
end;



call otnumeromuutos(100000) ;

Kasvattaa ostotilausnumeroita +100000.

Myyntitilausnumeroiden kasvatus


create procedure mtnumeromuutos(in :muutos integer);
begin
update winot set myyntitilausnro=myyntitilausnro+:muutos;
update winotri set rivimtnro=rivimtnro+:muutos;
update mt set numero=numero+:muutos;
update mt2 set mt2numero=mt2numero+:muutos;
update mtkerkirj set tilausnro=tilausnro+:muutos;
update mtkuormak set tilaus=tilaus+:muutos;
update mtliite set tilausnro=tilausnro+:muutos;
update mtrivi set tilausnro=tilausnro+:muutos;
update mttoimri set tilaus=tilaus+:muutos;
update laskut set tilausnro=tilausnro+:muutos;
end;



call mtnumeromuutos(100000) ;

Kasvattaa myyntilausnumeroita +100000.

Ryhmähierarkian vieminen tuotteille

Mikäli tuotteille on asetettu aliryhmät, voidaan tällä SQL:llä ajaa ryhmähierarkian mukaiset ryhmittelyt tuotteille.


update tuote set
ryhma=(select tuoteryhma from aliryhma where tuote.aliryhma=aliryhma.nro) where aliryhma<>0 and ryhma=0;
update tuote set
paaryhma=(select paaryhma from tryhma where tuote.ryhma=tryhma.nro) where ryhma<>0 and paaryhma=0;


Yksiköiden periyttäminen tuoteryhmiltä

Mikäli tuotteille halutaan periyttää niitä vastaavien tuoteryhmien yksiköt, voidaan se hoitaa näin:


update tuote
set
yks=(select myks from tryhma where tuote.ryhma=tryhma.nro),
varastoyks=(select vyks from tryhma where tuote.ryhma=tryhma.nro);


Asiakasnumeroiden jakautumisen tarkastelu

-- histogrammia asiakastiedosita (tässä voi näkyä reikiä jos on verkkokaupan kerta-asiakasväliä kasvatettu aiemmin)
select cast(cast(cast((asiakas.asiakas / 1000) as integer)*1000 as char(10)) as integer) as asNroBlokki, count(*) as montakoNroaKaytossaTuhannesta from asiakas group by asNroBlokki order by asnroBlokki;


Verkkokaupan kerta-asiakasvälin täyttymisen tarkistus

select
cast(round(
(cast(ssa2.ValillaAsiakkaita as float) / cast(ssa2.ValinKoko as float))*100,1) as float) as ProsenttiaKaytetty,
cast(round(
((isnull(ssa2.ValinViimeisinAsiakastietue, ssa2.kertaAsiakasvaliAlku - 1) - ssa2.kertaAsiakasvaliAlku + 1) /
cast(ssa2.ValinKoko as float) * 100),1) as float) as ProsenttiaKaytettyVikanMukaan,
(ssa2.kertaAsiakasvaliLoppu - isnull(ssa2.ValinViimeisinAsiakastietue, ssa2.kertaAsiakasvaliAlku - 1)) as ValillaTilaaVikanMukaan,
ssa2.*
from (
select
ssa.kertaAsiakasvaliAlku,
ssa.kertaAsiakasvaliLoppu,
(ssa.kertaAsiakasvaliLoppu - ssa.kertaAsiakasvaliAlku + 1) as ValinKoko,
(select count(*) from asiakas where asiakas.asiakas between ssa.kertaAsiakasvaliAlku and ssa.kertaAsiakasvaliLoppu) as ValillaAsiakkaita,
(select max(asiakas) from asiakas where asiakas.asiakas between ssa.kertaAsiakasvaliAlku and ssa.kertaAsiakasvaliLoppu) as ValinViimeisinAsiakastietue
from (
select
(select top 1 cast(ltrim(data) as integer) from ini where ininimi like '%skj.ini%' and sektio like '%webkauppa%' and avain like '%ASIAKASALKU%') as KertaAsiakasvaliAlku,
(select top 1 cast(ltrim(data) as integer) from ini where ininimi like '%skj.ini%' and sektio like '%webkauppa%' and avain like '%ASIAKASloppu%') as KertaAsiakasvaliLoppu
)ssa
)ssa2;


Pääryhmän tuotteiden ja saldollisten tuotteiden määrä

select paaryhma, count(numero), 
(select count(*) from varsaldo,tuote t where tuote=t.numero and t.paaryhma=tuote.paaryhma and varasto=1 and saldo<>0) 
from tuote group by paaryhma ;


Viivakooditarrojen generointi ostoista lajitelmatasolla

Tämä lisää tiettynä päivän varastoon tullee ostot viivakoodi tarra tulostusjonoon. Olettaa että viivakoodit on jo generoitu

INSERT INTO VKOODI(OIKEATUOTE, NIMI, VARASTO,  SAAPUI, EURO, MAARA, TUOTE, HINTA,  KOKOID, VARIID, KOKO, VARI,shinta)
( SELECT O.TUOTE,CONCAT(CONCAT(NIMI,'/'),NIMI2),VARASTO, PVM,1, MAARA, VIIVAKOODI,HINTA,ID1, ID2,
 (SELECT L.TEKSTI FROM LAJIITEM L WHERE ID=ID1), 
 (SELECT L.TEKSTI FROM LAJIITEM L WHERE ID=ID2),
 CONVERT(HINTA, SQL_VARCHAR)
FROM TUOTE T , OSTO O, OSTOLAJITELMAT OL, LAJIVK VK 
WHERE 
 T.NUMERO=O.TUOTE AND 
 O.PVM='20170428' AND 
 OSTOAVAIN=TAPAHTUMANRO AND
 VK.TUOTE=T.NUMERO AND 
 VK.Lajitelma1detailid=ID1 AND 
 VK.Lajitelma2detailid=ID2
 ) 




Muutostaulujen pienentäminen tietyn rivimäärän verran 

Poistaa 1000 riviä kerralla. Jäljittelee ajatuksellisesti yleisesti tiedettyä TOP funktiota.

delete from muutosdata where muutosid < (select min(avain)+1000 from muutoslog) ;
delete from muutoslogtxt where muutosid < (select min(avain)+1000 from muutoslog) ;
delete from muutoslog where avain  < (select min(avain)+1000 from muutoslog) ;


Kesken jääneen ostotilauksen varastojaon vieminen loppuun

Jos varastojako jostain syystä jää kesken (kaatuu tms) on se loppuunvieminen periaatteessa helppoa.

Alla olevalla kyselyllä voit tukia mitä siirtotapahtumia on kirjautunut tilausesta nro 3769 päivänä 20170724. Tämän avulla havaitaan mm. rivinumero josta aloittaa

select winotri.rivinro, winotri.tuote from winotjako, osto,winotri where 
  osto.tyyppi=1 and osto.tuote=winotri.tuote and
  winotjako.tilausnro=winotri.tilausnro and winotri.tilausnro=3769
  and winotjako.rivinro=winotri.rivinro and osto.pvm='20170724'
order by osto.tapahtumanro;



Alla sql scriptit korjaukseeen tekoon. Tässä on olettu tilauksen varaston olevan 1. Muita vakiota ovat päivä, tilausnumero, toimittaja, kirjaa ja asiakkaan viitte

-- procedure joka muuttaa saldoja, vähentää päävarastosta ja lisää annettuun. Olettaa että päävarastotietue on olemas
create procedure muutasaldo(in :varasto integer, in :tuote varchar(18), in :saldomuutos double, in :tilattumuutos double, in :id1 integer, 
in :id2 integer) ;
begin
  -- päävarasto oletettu 1
  update varsaldo set saldo = saldo - :saldomuutos, tilattu=tilattu-:tilattumuutos, muutospvm=curdate(), muutosklo=curtime() where tuote=:tuote and varasto = 1;
  if (:id1 <> 0) then
     update lajvs set saldo = saldo - :saldomuutos, tilattu=tilattu-:tilattumuutos, muutospvm=curdate(), muutosklo=curtime() where tuote=:tuote and varasto = 1 and lajitema1=:id1 and lajitelma2=:id2 ;
  end if;

  update varsaldo set saldo = saldo + :saldomuutos, tilattu=tilattu + :tilattumuutos, muutospvm=curdate(), muutosklo=curtime() where tuote=:tuote and varasto = :varasto;
  if (@@rowcount = 0 ) then
    insert into varsaldo(tuote, varasto, saldo, tilattu, muutospvm, muutosklo) values (:tuote, :varasto, :saldomuutos, :tilattumuutos, curdate(), curtime());
    insert into skjlog (aika, sovellus, logiteksti) values(now(), 'VARASTOJAKO', concat('lisätty ',CONCAT(:TUOTE,convert(:varasto,sql_varchar))));
  end if ; 

  if (:id1 <> 0) then
     update lajvs set saldo = saldo + :saldomuutos, tilattu=tilattu + :tilattumuutos, muutospvm=curdate(), muutosklo=curtime() where tuote=:tuote and varasto = :varasto and lajitema1=:id1 and lajitelma2=:id2 ;
  end if; 
end;


-- lisätään tapahtuma osto.dat:n
create procedure lisaasiirto(in :pvm varchar(8), in :tuote varchar(18), in :maara double, in :hinta double, in :toim varchar(5), in :varasto integer, in :verolka integer,
in :tilausnro integer, in :viite varchar(20), in :kirjaaja integer) ;
begin
 declare :tr  integer;
 declare :pr  integer;
 declare :ar  integer;
 declare :lvv  double;
 declare :hintaalv0  double;
 declare :mhinta  double;
 declare :valm  varchar(20);
 
 select aliryhma into :ar from tuote where numero =:tuote; 
 select ryhma into :pr from tuote where numero =:tuote; 
 select paaryhma into :tr from tuote where numero =:tuote;
 select valmistaja into :valm  from tuote where numero =:tuote;
 select hinta into :mhinta from tuote where numero =:tuote;
 select prosentti into :lvv from alvkanta where luokka =:verolka;
 set :hintaalv0 = :hinta / ( 1 + :lvv/100);
 
 insert into osto(pvm, tuote, ahinta, kpl, varasto, tyyppi, lahdevarasto, toimittaja,  ahintaveroton, kirjaaja, selite, tilausnro , mhinta,tuoteryhma,
 paaryhma, aliryhma, valmistaja, kirjpvm)
 
 values 
 (:pvm, :tuote, :hinta, :maara, :varasto, 1, 1,:toim, :hintaalv0, :kirjaaja, :viite,:tilausnro, :mhinta, :tr, :pr, :ar,:valm, replace(convert(curdate(),sql_varchar),'-','') );
end;

-- suoritetaan tilauksen jakojen kirjaaminen. tilausnumemo 3769 ja kirjataan ostotilauksen rivistä 56 alkaen
create procedure suoritajako();
begin
  declare :tuote varchar(20);
  declare :maara integer;
  select count(*) into :maara from winotjako,winotri where 
  winotjako.tilausnro=winotri.tilausnro and winotri.tilausnro=3769
  and winotjako.rivinro=winotri.rivinro and winotri.rivinro >56 ;
  
  DECLARE c1 CURSOR FOR select winotri.tuote, winotjako.varasto, winotjako.jaettavamaara, winotri.ostohinta, winotri.alvluokka from winotjako,winotri where 
  winotjako.tilausnro=winotri.tilausnro and winotri.tilausnro=3769
  and winotjako.rivinro=winotri.rivinro and winotri.rivinro >56 AND WINOTJAKO.JAETTAVAMAARA>0
  order by winotri.rivinro ;
  declare :alv integer;
  declare :v integer;
  declare :m double;
  declare :r integer;
  declare :c integer;
  declare :hinta double;
  
  set :c = 0;
  OPEN c1; 
  BulkLinesLoop: 
  LOOP  
  FETCH NEXT FROM c1 into :tuote, :v, :m, :hinta, :alv
  -- päivä 24.7.2017, tilaus 3769, kirjaaja 11071
  call lisaasiirto('20170724',:tuote, :hinta, :m,'221', :v, :alv, 3769,'3769 / 91700 08328', 11071);
  call muutasaldo(:v, :tuote, :m, 0, 0,0); 
  set :c=:c +1;
  if ( :c = :maara) then 
    leave bulklinesloop;
  end if;
END LOOP; 
CLOSE c1;   
end;
-- kutsutan varsinaista jakoa
CALL SUORITAJAKO;



Tietokantataulujen hakeminen tietokannasta kentän nimen perusteella

Mistä taulusta kenttä löytyy?

select
xf$name taulunimi,
"x$field"."xe$name" as kenttanimi,
(case "x$field"."xe$datatype"
       when 0 then   'String                            (0)'
       when 1 then   'Integer                           (1)'
       when 2 then   'Float                  IEEE Float (2)'
       when 3 then   'Date                 Btrieve Date (3)'
       when 4 then   'Time                 Btrieve Time (4)'
       when 7 then   'Boolean                           (7)'
       when 10 then  'String            Pascal Lstring (10)'
       when 11 then  'String                 c-Zstring (11)'
       when 12 then  'String      variable length note (12)'
       when 13 then  'String      variable length Lvar (13)'
       when 14 then  'Integer          binary unsigned (14)'
       when 15 then  'Integer            autoincrement (15)'
       when 16 then  'Bit                              (16)'
       when 20 then  'Timestamp                        (20)'
       when 21 then  'String                   varchar (21)'
       when 27 then  'GUID                             (27)'
       when 227 then 'Integer          primaryindeksi (227)'
       when 255 then '255indeksisuodatapois'
       else 'tuntematontuntematontuntematon'
       end ) as tietotyyppi, 
left(lcase("Xf$Loc"), 33) as tiedostoNimi
from "x$file", "x$field"
where
"x$file"."Xf$Id" = "x$field"."xe$file"
and tietotyyppi != '255indeksisuodatapois'
and kenttanimi like '%nimi%' // tähän haettavan kentän nimi
order by tauluNimi, kenttanimi;

Kyselyn viimeistä edeltävä rivillä lainausmerkeissä oleva merkkijono %nimi% määrittelee, mitä kentän nimessä esiintyy. % on jokerimerkki, mikä vastaa mitä tahansa merkkiä.

Kampanjan lisäys toimittajalle

insert into kamptiedot(Id, Nimi, Alku, Loppu, Summa, Prosentteina, Yksi, kaksi, Kolme )
select (select max(id)+1 from kamptiedot), 'Toimittaja' -- tuossa on kampanjan nimi kampanjahallintaa varten
,'2017-08-23','2017-12-31',30,1,1,2,3 ; 
insert into KAMPPANJA
(Tuote, RiviNro, Tunnus, Alku, Loppu,  Myyntihinta, Prosentti,id) 
(select numero, (select IFNULL(max(rivinro)+1,1) from KAMPPANJA where tuote=numero), TOIMITTAJA, '20170823','20171231', 30,1,(select max(id) from kamptiedot) FROM TUOTE
WHERE TOIMITTAJA ='0001'  -- tähän toimittajan tunnus
) ;



Tuotekortin kuvat-välilehden kuvien esikatselun esiintuominen

update ini set data = 150 where avain like 'KUVASPLITTER.TOP';


Tuotemyy ja MT -taulujen välinen yhteys (arvio)

-- tuotemyynnisssä ei tällähetkellä yhdistä mikään 100% varmasti riviä tilaukseen.
-- tällä kyselyllä tosin saadaan sivistynyt arvaus:

select mt.numero,t.asiakas, mt.tilauspaiva
from
tuotemyy t ,mt , mtkuormak k
where
t.pvm='20171020' and
yyyymmddtodate(t.pvm)=k.pvm and
k.tilaus=mt.numero and
t.asiakas=mt.toimitusasiakas
order by t.tosite;

Asiakkaan siirtäminen tiettyyn ryhmään myynnin perusteella

-- poistetaan data aputaulusta, jos sellainen oli jo olemassa
delete from "#asryhmaapu"; 


-- luodaan aputaulu, antaa virheen jos on jo olemassa
create table "#asryhmaapu" 
  (asiakas integer,
   asryhma integer,
   verotonmyynti double,
   primary key(asiakas,asryhma));


-- laitetaan tauluun myynnit
-- rajataan ajaksi koko vuosi 2017
-- ja vain tietyt asiakasryhmät 
-- reseptituoterajaus (ei ota komponentteja tuplana)
insert into "#asryhmaapu" (asiakas,asryhma,verotonmyynti) 
select
  tuotemyy.asiakas,
  asiakas.ryhma  as  asryhma,
  sum(summa / (1+AlvPros/100)) as VerotonSumma 
from
  tuotemyy
   left outer join asiakas on asiakas.asiakas=tuotemyy.asiakas
where
 pvm like '2017%' and 
 asryhma in(1,2,150) and 
 tuotelaji in (0,2) 
group by 
 asryhma,
 tuotemyy.asiakas ;

-- lisää aputauluun ne asiakkaat, jotka eivät ole ostaneet ollenkaan
insert into "#asryhmaapu" (asiakas,asryhma,verotonmyynti)
 select asiakas,ryhma,0 from asiakas where asiakas not in(select asiakas from "#asryhmaapu") and asiakas.ryhma in(1,2,150) ;


-- asettaa asiakasryhmäksi 2 niille asiakkaille, joiden myynti on yli 3000€ vuodessa
update asiakas set ryhma=2,ryhma2=2 
 where asiakas in(select asiakas from "#asryhmaapu" where verotonmyynti>3000) ;
-- asettaa asiakasryhmäksi 150 niille asiakkaille, joiden myynti on alle 2000€ vuodessa
update asiakas set ryhma=150,ryhma2=150
 where asiakas in(select asiakas from "#asryhmaapu" where verotonmyynti<2000) ;
-- asettaa asiakasryhmäksi 1 niille asiakkaille, joiden myynti on välillä 2000-3000€ vuodessa
update asiakas set ryhma=1,ryhma2=1
 where asiakas in(select asiakas from "#asryhmaapu" where verotonmyynti between 2000 and 3000) ;

Sähköpostiosoitteiden muuttaminen asiakkaan datasta

Tämä toimii ainakin gmailille eli asiakkaan sähköposti muutetaan muotoon minun.nimi+asiakas.nimi_asiakasfirmi.com@gmail.com

update asiakas set sahkoposti = substring(sahkoposti,1,position(',',sahkoposti)-1) where sahkoposti like '%,%' ;
update asiakas2 set LaskutusSahkoPosti = substring(LaskutusSahkoPosti,1,position(',',LaskutusSahkoPosti)-1) where LaskutusSahkoPosti like '%,%' ;
update asiakas set sahkoposti='OMA.NIMI+' + replace(sahkoposti,'@','_') +'@gmail.com' where ltrim(sahkoposti)<>'' ;
update asiakas2 set LaskutusSahkoPosti='OMA.NIMI+' + replace(LaskutusSahkoPosti,'@','_') +'@gmail.com' where ltrim(LaskutusSahkoPosti)<>'' ;

Tuotteen muutospäivämäärien päivittäminen

Päivittää tuotteiden muutospäivämäärät nykyiseen hetkeen.

update tuote set muutettupvm = replace(convert(curdate(),sql_char),'-',''), muutettutime=curtime() ;

Laskutuslogin putsaus

Putsaa turhia rivejä laskutuslogista.

delete from latilog where lokiteksti like 'Lasku tulostettu  %' and length(lokiteksti)=20 ;

Tuotetekstien otsikoiden korjaaminen

Saat tutkittua tuotteille asetettuja Tekstit-välilehden otsikoita.

select * from ini where sektio like '%aset%' and avain like '%txtotsik%';