SQL-Question

[Claudius Sailer]

Hi,

at the moment I use a trick to get the result I want. I have a table 
which is only there to handle 1 SQL-Statement which 1.X doesn't 
support. You can see that I fill 2 kinds of records to one table


     //ParserArbeiten
     //Ausgaben
     SQLString="SELECT B.RecID, B.BDatum, KZ.Konto_Name, 
K.Kategorie_Name, B.Betrag, B.Kommentar, B.Blocked"
     SQLString=SQLString+" FROM Buchungen B, Kategorien K, Konten KZ"
     SQLString=SQLString+" WHERE B.Kategorie_ID=K.RecID"
     SQLString=SQLString+ " AND B.Ziel_Konto=KZ.RecID"
     SQLString=SQLString+ " AND B.Deleted='0'"
     SQLString=SQLString+ " AND B.Herk_Konto='"+cstr(AktuellesKonto)+"'"

     cmd= "INSERT INTO Anzeige (Buchungs_ID, BDatum, Gegenkonto, 
Kategorie, Ausgabe, Kommentar, Blocked) "
     cmd=cmd + SQLString
     count=meineDatenbank.SQLExecute(cmd)

     //Einnahmen
     SQLString="SELECT B.RecID, B.BDatum, KO.Konto_Name, 
K.Kategorie_Name, B.Betrag, B.Kommentar, B.Blocked"
     SQLString=SQLString+" FROM Buchungen B, Kategorien K, Konten KO"
     SQLString=SQLString+" WHERE B.Kategorie_ID=K.RecID"
     SQLString=SQLString+ " AND B.Herk_Konto=KO.RecID"
     SQLString=SQLString+ " AND B.Deleted='0'"
     SQLString=SQLString+ " AND B.Ziel_Konto='"+cstr(AktuellesKonto)+"'"

     cmd= "INSERT INTO Anzeige (Buchungs_ID, BDatum, Gegenkonto, 
Kategorie, Einnahme, Kommentar, Blocked) "
     cmd=cmd + SQLString
     count=meineDatenbank.SQLExecute(cmd)

   SQLString="SELECT RecID, * FROM Anzeige ORDER BY BDatum, Gegenkonto, 
Kategorie"
   CurBuchungen=meineDatenbank.SQLselect(SQLString, 
EVCursorLocation.kServerSide, EVLockType.kNoLocks, 
EVCursorDirection.kRandom)
   Ende=CurBuchungen.RecordCount


Is it possible to make this in one SQL-Statement like


     SQLString="SELECT B.RecID, B.BDatum, KZ.Konto_Name, 
K.Kategorie_Name,"
     SQLString=SQLString+" Case when B.Ziel_Konto=KZ.RecID then B.Betrag 
ELSE null END,"
     SQLString=SQLString+" Case when B.Herk_Konto=KZ.RecID then B.Betrag 
ELSE null END,"
     SQLString=SQLString+" B.Kommentar, B.Blocked"
     SQLString=SQLString+" FROM Buchungen B, Kategorien K, Konten KZ"
     SQLString=SQLString+" WHERE B.Kategorie_ID=K.RecID"
     SQLString=SQLString+ " AND (B.Ziel_Konto=KZ.RecID OR 
B.Herk_Konto=KZ.RecID)"
     SQLString=SQLString+ " AND B.Deleted='0'"
     SQLString=SQLString+ " AND B.Herk_Konto='"+cstr(AktuellesKonto)+"'"
     SQLString=SQLString+ " ORDER BY B.BDatum, KZ.Konto_Name, 
K.Kategorie_Name"

thanks for help


Claudius