Board index » delphi » SQL with feedback

SQL with feedback

I'm working on a program where on a perticulair point must look if a
combination of 2 values already
Exists in a database.

If they Exist I have to look if there is another value present (it doesn't
matter wich one) and then copy
this value to an Edit-box.

I've tried to do this with an Query
where here is the SQL Code, all variables are of the type integer.

-------------------------------

select NrVordering, ArchiefNr, NrDebiteur
from Doctyped DT

where
         NrVordering   =      :ClaimNr
and   NrDebiteur       =      :Debtor

group by NRVordering,  ArchiefNr, NrDebiteur

-------------------------------

Then I tried to get an value back in this way:

-------------------------------

begin

//** serching for documents that have the same archive numer as te selected

 fDatMod.qSearchArchive.ParamByName('ClaimNR').AsInteger :=
StrToInt(VorderingNr.text);
 fDatMod.qSearchArchive.ParamByName('Debtor').AsInteger  :=
StrToInt(DebiteurNr.text);

 fDatMod.qSearchArchive.Prepare;
 fDatMod.DataBaseMain.Open;

 Arch_Nr.text :=
IntToStr(fDatMod.qSearchArchive.FieldByName('Debtor').AsInteger);

 fDatMod.qSearchArchive.Close;

end;

-------------------------------

I've tried it also with te function GetFieldData etc.
But nothing seems to work, afther a couple huors I gave up
so does someone know how to do this ??

Without any help from Visible components, just with an query.

Sincerrely

Eduard

 

Re:SQL with feedback


"Saiwan" <Sai...@hetnet.nl> skrev i melding
news:eGHGbxoHAHA.287@net003s...

Quote
> I'm working on a program where on a perticulair point must look if a
> combination of 2 values already
> Exists in a database.

> If they Exist I have to look if there is another value present (it
doesn't
> matter wich one) and then copy
> this value to an Edit-box.

> I've tried to do this with an Query
> where here is the SQL Code, all variables are of the type integer.

> -------------------------------

> select NrVordering, ArchiefNr, NrDebiteur
> from Doctyped DT

> where
>          NrVordering   =      :ClaimNr
> and   NrDebiteur       =      :Debtor

> group by NRVordering,  ArchiefNr, NrDebiteur

> -------------------------------

> Then I tried to get an value back in this way:

> -------------------------------

> begin

> file://** serching for documents that have the same archive numer as
te selected

>  fDatMod.qSearchArchive.ParamByName('ClaimNR').AsInteger :=
> StrToInt(VorderingNr.text);
>  fDatMod.qSearchArchive.ParamByName('Debtor').AsInteger  :=
> StrToInt(DebiteurNr.text);

>  fDatMod.qSearchArchive.Prepare;
>  fDatMod.DataBaseMain.Open;

Hi !

You don't need a GROUP by part, but you may need a DISTINCT SELECT,
maybe together with a NOT NULL :

select DISTINCT
  NrVordering,
  ArchiefNr,
  NrDebiteur
from
  Doctyped DT
where
  NrVordering = :ClaimNr
   and NrDebiteur = :Debtor
  and ArchiefNr IS NOT NULL

...this returns all distinct "ArchiefNr" - values where the two params
match. No need for DISTINCT if there is never more than one such record
or if they have all the same value in this field, of course.

To get the value, do simply write:

Arch_Nr.text :=
IntToStr(fDatMod.qSearchArchive.FieldByName('ArchiefNr').AsInteger);

...much like you suggested yourself.
--
Bjoerge Saether
Consultant / Developer
Asker, Norway
bsaether.removet...@online.no (remove the obvious)

Other Threads