Page 1 of 2
SQL
Posted: Mon Aug 18, 2014 9:36 am
by NvM
Hello together,
I want to use SQL-Statements in my RoboTasks. It seems to be a problem, if I use statements with some where-clauses.
This version for example works:
Code: Select all
SELECT Belegnummer, Belegart FROM KHKVKBelege WHERE (Belegart = 'Sofortrechnung' OR Belegart = 'Direktrechnung' OR Belegart = 'Rechnung')
But this one seems not to work:
Code: Select all
SELECT Belegnummer, Belegart FROM KHKVKBelege WHERE (Periode = 2014006) AND ((Belegart = 'Sofortrechnung' OR Belegart = 'Direktrechnung' OR Belegart = 'Rechnung'))
There is no error-message. In the debug window only this message is displayed:
Code: Select all
I: 18.08.2014 10:23:16: Executing "1.Dataset Loop"
I: 18.08.2014 10:23:16: Task executed successfully
In the query I used the SQL-statement as Variable. For that reason in "Dataset Loop"-window is saved in the "SQL Query"-Field this data: {SQLVKBELEGE}. It is defined in "Local variables" as above:
Code: Select all
SQLVKBELEGE = SELECT Belegnummer, Belegart FROM KHKVKBelege WHERE (Periode = 2014006) AND ((Belegart = 'Sofortrechnung' OR Belegart = 'Direktrechnung' OR Belegart = 'Rechnung'))
What could be the reason?
Greetings from Germany
Re: SQL
Posted: Mon Aug 18, 2014 10:30 am
by Oleg
There is no error-message. In the debug window only this message is displayed:
Code: Select all
I: 18.08.2014 10:23:16: Executing "1.Dataset Loop"
I: 18.08.2014 10:23:16: Task executed successfully
This means that the query selects nothing (no records are selected). But query text is correct and database engine doesn't return any error messages.
Also it would be great if you sent the whole task (example)
Re: SQL
Posted: Mon Aug 18, 2014 10:43 am
by NvM
Code: Select all
;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|0
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task3"
Hide=INTEGER|0
ID=INTEGER|1924937830
LocalVariables=STRING|"""CONNECTION = Provider=SQLOLEDB.1;Password=abcdef;Persist Security Info=True;User ID=abcdef;Initial Catalog=TESTDB;Data Source=DB-SERVER"",""'SQLVKBELEGE = SELECT Belegnummer, Belegart FROM KHKVKBelege WHERE (Periode = 2014006) AND ((Belegart = 'Sofortrechnung' OR Belegart = 'Direktrechnung' OR Belegart = 'Rechnung'))"",""SQLVKBELEGE = SELECT Belegnummer, Belegart FROM KHKVKBelege WHERE (Belegart = 'Sofortrechnung' OR Belegart = 'Direktrechnung' OR Belegart = 'Rechnung')"",BELEG=,BELEGANZAHL=,""ERLEDIGT = 0"",""FIELDNAMES = "",""FIELDVALUES = """
LogOnAsUser=INTEGER|1
Name=STRING|"Datenbankabfrage"
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0
[Actions]
Action1=FOLDER
Action10=FOLDER
Action11=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER
[Actions\Action1]
ActionID=STRING|"A_DIALOG_QUESTION"
Enabled=INTEGER|-1
Name=STRING|"Question Dialog"
Params=FOLDER
[Actions\Action1\Params]
abortif=STRING|"0"
action=STRING|"0"
autotime=STRING|"0"
buttons=STRING|"1"
message=STRING|"""Wollen Sie die Erlösberechnung automatisiert ausführen?"""
time=STRING|"60"
[Actions\Action10]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|0
Name=STRING|"End Loop"
[Actions\Action11]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action2]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""PROTOKOLL"" with value ""Robotask Roherlös-Neuberechnung{EOL}Versandkostenp..."""
Params=FOLDER
[Actions\Action2\Params]
expand=STRING|"0"
varkind=STRING|"1"
varname=STRING|"PROTOKOLL"
varvalue=STRING|"Robotask Roherlös-Neuberechnung{EOL}Versandkostenproblem{EOL}{DateTime} - {TaskName}"
[Actions\Action3]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""BELEG"" with value ""0"""
Params=FOLDER
[Actions\Action3\Params]
expand=STRING|"1"
varkind=STRING|"1"
varname=STRING|"BELEG"
varvalue=STRING|"0"
[Actions\Action4]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""BELEGANZAHL"" with value ""0"""
Params=FOLDER
[Actions\Action4\Params]
expand=STRING|"1"
varkind=STRING|"1"
varname=STRING|"BELEGANZAHL"
varvalue=STRING|"0"
[Actions\Action5]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""ERLEDIGT"" with value ""0"""
Params=FOLDER
[Actions\Action5\Params]
expand=STRING|"1"
varkind=STRING|"1"
varname=STRING|"ERLEDIGT"
varvalue=STRING|"0"
[Actions\Action6]
ActionID=STRING|"A_WINDOW_COMMAND"
Enabled=INTEGER|-1
Name=STRING|"Focus window ""Sage Office Line Evolution 2012 Warenwirtschaft"""
Params=FOLDER
[Actions\Action6\Params]
action=STRING|"0"
caption=STRING|"1"
child=STRING|"0"
class=STRING|"1"
classexact=STRING|"0"
exact=STRING|"0"
findmode=STRING|"0"
hidden=STRING|"0"
processall=STRING|"0"
wincaption=STRING|"Sage Office Line Evolution 2012 Warenwirtschaft"
winclass=STRING|"OMain"
[Actions\Action7]
ActionID=STRING|"A_LOOP_DATASET"
Enabled=INTEGER|-1
Name=STRING|"Dataset Loop"
Params=FOLDER
[Actions\Action7\Params]
commatext=STRING|"0"
connectionstring=STRING|"{Connection}"
override=STRING|"0"
password=STRING|"1980020874190831959321137"
sql=STRING|"{SQLVKBELEGE}"
timeout=STRING|"60"
var1=STRING|"FIELDNAMES"
var2=STRING|"FIELDVALUES"
withmemo=STRING|"0"
[Actions\Action8]
ActionID=STRING|"A_LOOP_DATASET"
Enabled=INTEGER|0
Name=STRING|"Dataset Loop"
Params=FOLDER
[Actions\Action8\Params]
commatext=STRING|"0"
connectionstring=STRING|"{Connection}"
override=STRING|"0"
password=STRING|"1980018827211281908518827"
sql=STRING|"""SELECT Belegnummer FROM KHKVKBelege"",""WHERE Belegnummer = {FieldValues(0)}"""
timeout=STRING|"60"
var1=STRING|"FIELDNAMES"
var2=STRING|"FIELDVALUES"
withmemo=STRING|"0"
[Actions\Action9]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""{FieldNames(0)}={FieldValues(0)}"""
Params=FOLDER
[Actions\Action9\Params]
icon=STRING|"1"
msg0=STRING|"{FieldNames(0)}={FieldValues(0)}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"
Re: SQL
Posted: Mon Aug 18, 2014 11:04 am
by Oleg
As I said earlier the query returns nothing
it seem the condition (Periode = 2014006) is incompatible with condition ((Belegart = 'Sofortrechnung' OR Belegart = 'Direktrechnung' OR Belegart = 'Rechnung'))
Maybe you should use 201406 instead of 2014006? (Pay attention that you wrote superfluous parentheses in 2-nd expression)
In any case you should check this SQL
You can specify SQL query and connection string explicitly the Dataset Loop editor and press "Test SQL" button
Also you can test SQL text in some of database utilities.
Re: SQL
Posted: Mon Aug 18, 2014 11:24 am
by NvM
In Management Studio (2008 R2) everything works well. The statement is correct (field value "2014006 is correct).
Re: SQL
Posted: Mon Aug 18, 2014 12:40 pm
by Oleg
Unfortunately I can't say something else.
SQL query selects no records for some reason. I think that the problem is in the condition.
Could you send the structure of the table "KHKVKBelege"?
Re: SQL
Posted: Mon Aug 18, 2014 3:02 pm
by NvM
Thanks for your help. Here are the fields and datatypes for KHKVKBelege:
Code: Select all
Mandant smallint Unchecked
TransferID int Unchecked
Transferart varchar(40) Unchecked
BelID int Checked
Belegtyp smallint Checked
Zeitpunkt datetime Checked
Information text Checked
Dateiinhalt text Checked
Parameter text Checked
Antwort text Checked
Kto varchar(20) Checked
IstImport smallint Checked
IstAufgeloest smallint Checked
Belegdatum datetime Checked
Matchcode varchar(50) Checked
Lieferdatum datetime Checked
KtoExtern varchar(40) Checked
Referenznummer varchar(20) Checked
Belegjahr smallint Checked
Belegnummer int Checked
Projektnummer varchar(31) Checked
Projektgeber varchar(20) Checked
Vorgang int Checked
Name1 varchar(50) Checked
Name2 varchar(50) Checked
Lieferzusatz varchar(50) Checked
Lieferstrasse varchar(50) Checked
LieferPLZ varchar(10) Checked
LieferOrt varchar(40) Checked
Lieferland varchar(10) Checked
EMail varchar(128) Checked
Telefon varchar(40) Checked
Nettosumme money Checked
Steuersumme money Checked
Gesamtbetrag money Checked
Zahlungskondition varchar(20) Checked
Gesamtgewicht money Checked
Belegnummerformatiert varchar(20) Checked
KtoTransferprofil varchar(20) Checked
ExtID int Checked
Strasse varchar(50) Checked
Ort varchar(50) Checked
PLZ varchar(7) Checked
Land varchar(50) Checked
Liefername1 varchar(50) Checked
Liefername2 varchar(50) Checked
Zusatz varchar(50) Checked
BelegKz varchar(1) Checked
BelIDBasis int Checked
Bemerkung text Checked
Vertreter varchar(10) Checked
Kostenstelle varchar(50) Checked
Abteilung varchar(50) Checked
Referenzzeichen varchar(80) Checked
Zahlungsart varchar(20) Checked
OPNummer varchar(20) Checked
Verwendungszweck varchar(40) Checked
Telefax varchar(20) Checked
Geloescht smallint Checked
KtoRechnung varchar(20) Checked
RechnungName1 varchar(50) Checked
RechnungName2 varchar(50) Checked
RechnungZusatz varchar(50) Checked
RechnungStrasse varchar(50) Checked
RechnungPLZ varchar(7) Checked
RechnungOrt varchar(50) Checked
RechnungLand varchar(3) Checked
Anrede varchar(50) Checked
Lieferanrede varchar(50) Checked
RechnungAnrede varchar(50) Checked
IBAN varchar(20) Checked
BIC varchar(20) Checked
KKNummer varchar(20) Checked
KKPruefziffer varchar(5) Checked
KKGueltigBis varchar(4) Checked
Institut varchar(30) Checked
Inhaber varchar(30) Checked
UStID varchar(15) Checked
Steuernummer varchar(15) Checked
Rechnungsliste int Checked
ILNDatenempfaenger varchar(15) Checked
KontaktAnrede varchar(20) Checked
KontaktVorname varchar(30) Checked
KontaktNachname varchar(30) Checked
ExtGUID varchar(50) Checked
VIND smallint Checked
Direktlieferung smallint Checked
Zahlbetrag money Checked
ZahlungBestaetigt smallint Checked
TransaktionsID varchar(50) Checked
Bruttopreise smallint Checked
BelIDVKFolgeBeleg int Checked
Versand varchar(10) Checked
Ansprechpartner int Checked
ExtStatus varchar(50) Checked
LastUpdateExtern datetime Checked
KtoExternMarketplace varchar(50) Checked
MitSteuerinformationen smallint Checked
BelegdatumExtern datetime Checked
MarketplaceReportId varchar(20) Checked
Provision money Checked
WkzProvision varchar(3) Checked
KampagnenID varchar(20) Checked
Rabattbetrag1 money Checked
Rabattbetrag2 money Checked
Rabattbetrag3 money Checked
Rabattsatz1 money Checked
Rabattsatz2 money Checked
Rabattsatz3 money Checked
IstAngehalten smallint Checked
IstStorniert smallint Checked
ILNBY varchar(15) Checked
ILNDP varchar(15) Checked
ILNIV varchar(15) Checked
Lieferadresse int Checked
KundengruppeExtern varchar(35) Checked
RechnungsnummerExtern varchar(20) Checked
UrsprungsReferenznummer varchar(35) Checked
IstExterneAbwicklung smallint Checked
LieferTelefon varchar(40) Checked
LieferTelefax varchar(40) Checked
LieferEMail varchar(128) Checked
Wkz varchar(3) Checked
IstAutodruckerledigt smallint Checked
IDMailRechnung int Checked
Rabatttext1 varchar(40) Checked
Rabatttext2 varchar(40) Checked
Rabatttext3 varchar(40) Checked
StaffelRabatttext1 varchar(40) Checked
StaffelRabatttext2 varchar(40) Checked
StaffelRabatttext3 varchar(40) Checked
StaffelRabattsatz1 money Checked
StaffelRabattsatz2 money Checked
StaffelRabattsatz3 money Checked
StaffelRabattbetrag1 money Checked
StaffelRabattbetrag2 money Checked
StaffelRabattbetrag3 money Checked
Re: SQL
Posted: Mon Aug 18, 2014 7:58 pm
by Oleg
Where are fields Periode and Belegart ?
I'm sorry I don't understand how your query works...
Re: SQL
Posted: Tue Aug 19, 2014 8:49 am
by NvM
To copy fields does not work well in Management Studio:
Code: Select all
Periode int Checked
Belegart varchar(50) Checked
Re: SQL
Posted: Tue Aug 19, 2014 10:40 am
by Oleg
I created the test table with same structure and several records
Database server MSSQL 2005
Your SQL query works.
I used this example:
Code: Select all
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|933306365
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task658"
Hide=INTEGER|0
ID=INTEGER|869606980
LocalVariables=STRING|"""connection=Provider=SQLOLEDB.1;Persist Security Info=False;User ID=oleg;Initial Catalog=tempdb;Data Source=win7x64\mssql1"",""SQL=SELECT Belegnummer, Belegart FROM KHKVKBelege WHERE (Periode = 2014006) AND ((Belegart = 'Sofortrechnung' OR Belegart = 'Direktrechnung' OR Belegart = 'Rechnung'))"""
LogOnAsUser=INTEGER|1
Name=STRING|"Dataset Loop..."
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0
[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
[Actions\Action1]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""TEXT"" with value """""
Params=FOLDER
[Actions\Action1\Params]
expand=STRING|"0"
varkind=STRING|"1"
varname=STRING|"TEXT"
[Actions\Action2]
ActionID=STRING|"A_LOOP_DATASET"
Enabled=INTEGER|-1
Name=STRING|"Dataset Loop"
Params=FOLDER
[Actions\Action2\Params]
commatext=STRING|"0"
connectionstring=STRING|"{connection}"
override=STRING|"1"
password=STRING|"3286031434329583143432958"
sql=STRING|"{sql}"
timeout=STRING|"60"
userid=STRING|"oleg"
var2=STRING|"AAA"
withmemo=STRING|"0"
[Actions\Action3]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|0
Name=STRING|"Show ""{aaa}"""
Params=FOLDER
[Actions\Action3\Params]
icon=STRING|"1"
msg0=STRING|"{aaa}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"
[Actions\Action4]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""TEXT"" with value ""{text}{aaa}{eol}"""
Params=FOLDER
[Actions\Action4\Params]
expand=STRING|"1"
varname=STRING|"TEXT"
varvalue=STRING|"{text}{aaa}{eol}"
[Actions\Action5]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action6]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""{Text}"""
Params=FOLDER
[Actions\Action6\Params]
icon=STRING|"1"
msg0=STRING|"{Text}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"