RoboTask forum

NvM · Post by **NvM** » Mon Aug 18, 2014 9:36 am

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

Post by **Oleg** » Mon Aug 18, 2014 10:30 am

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)

NvM · Post by **NvM** » Mon Aug 18, 2014 10:43 am

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"

Post by **Oleg** » Mon Aug 18, 2014 11:04 am

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.

NvM · Post by **NvM** » Mon Aug 18, 2014 11:24 am

In Management Studio (2008 R2) everything works well. The statement is correct (field value "2014006 is correct).

Post by **Oleg** » Mon Aug 18, 2014 12:40 pm

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"?

NvM · Post by **NvM** » Mon Aug 18, 2014 3:02 pm

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

Post by **Oleg** » Mon Aug 18, 2014 7:58 pm

Where are fields Periode and Belegart ?
I'm sorry I don't understand how your query works...

NvM · Post by **NvM** » Tue Aug 19, 2014 8:49 am

To copy fields does not work well in Management Studio:

Code: Select all

Periode	int	Checked
Belegart	varchar(50)	Checked

Post by **Oleg** » Tue Aug 19, 2014 10:40 am

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"

RoboTask forum

SQL

SQL

Re: SQL

Re: SQL

Re: SQL

Re: SQL

Re: SQL

Re: SQL

Re: SQL

Re: SQL

Re: SQL