Startbeitrag von Peter Adema am 30.08.2013 14:22
Hi,
I am struggeling with next topic:
I need to get data from a file in a different database on a server.
As I do not need all the data (600.000 records) I wantto select the data with a query written in SQL.
First I make the connection: I have stored the connection credentials in a separate file.
PROCEDURE Open_interface()
HReadSeekFirst(DBConnect,NameConnect,File_connect)
IF HFound(DBConnect) THEN
GConnectname=DBConnect.NameConnect..Caption // Temporary name
Link_file=DBConnect.FileConnect
IF HDescribeConnection(GConnectname,DBConnect.UserDBConnect,DBConnect.PassDbconnect,...
DBConnect.ServerConnect,DBConnect.Dbconnect,hAccessHFClientServer,hORead)=True THEN
IF HOpenConnection(GConnectname)=True THEN //Opening the connexion
IF HDeclareExternal("."+"\"+Link_file,"Data_source_name",GConnectname)=True THEN //Rename with a virtual Name
GOTO einde
END
ELSE
Error(GConnectname+" : " + HErrorInfo()) //error on connection
END
HCloseConnection(GConnectname) // Close the connection
ELSE
Error("Connection error - Opening:"+ DBConnect.DBConnect +TAB+HErrorInfo())
END
ELSE
Error("Connection error - Opening:"+ DBConnect.DBConnect +TAB+HErrorInfo())
END
einde
Then I call the query written in SQL:
//load of HTRXREP
File_connect="04NL"
Open_interface()
HCreation(Htrxrep)
IF HExecuteQuery(QRY_Test2,hQueryDefault)=True THEN
Info(HRecNum(QRY_Test2))
HCancelDeclaration(QRY_Test2)
ELSE
Error("Error HyperFileSQL : " + HErrorInfo())
END
Close_interface()
This is the SQL of the query:
SELECT DISTINCT
Data_source_name.IREPC AS IREPC,
Data_source_name.CONK AS CONK
iperiod AS iperiod
SUM(IAMT) AS sum_IAMT,
SUM(INBTR) AS sum_INBTR
FROM
Data_source_name
WHERE
IREPC 'HFEES'
Data_source_name.CONK = 'M92'
AND iperiod > '201301'
GROUP BY
IREPC,
CONK,
iperiod
The query does run, but I get a -1 records....
Any ideas what is wrong?
I am struggeling with next topic:
I need to get data from a file in a different database on a server.
As I do not need all the data (600.000 records) I wantto select the data with a query written in SQL.
First I make the connection: I have stored the connection credentials in a separate file.
PROCEDURE Open_interface()
HReadSeekFirst(DBConnect,NameConnect,File_connect)
IF HFound(DBConnect) THEN
GConnectname=DBConnect.NameConnect..Caption // Temporary name
Link_file=DBConnect.FileConnect
IF HDescribeConnection(GConnectname,DBConnect.UserDBConnect,DBConnect.PassDbconnect,...
DBConnect.ServerConnect,DBConnect.Dbconnect,hAccessHFClientServer,hORead)=True THEN
IF HOpenConnection(GConnectname)=True THEN //Opening the connexion
IF HDeclareExternal("."+"\"+Link_file,"Data_source_name",GConnectname)=True THEN //Rename with a virtual Name
GOTO einde
END
ELSE
Error(GConnectname+" : " + HErrorInfo()) //error on connection
END
HCloseConnection(GConnectname) // Close the connection
ELSE
Error("Connection error - Opening:"+ DBConnect.DBConnect +TAB+HErrorInfo())
END
ELSE
Error("Connection error - Opening:"+ DBConnect.DBConnect +TAB+HErrorInfo())
END
einde
Then I call the query written in SQL:
//load of HTRXREP
File_connect="04NL"
Open_interface()
HCreation(Htrxrep)
IF HExecuteQuery(QRY_Test2,hQueryDefault)=True THEN
Info(HRecNum(QRY_Test2))
HCancelDeclaration(QRY_Test2)
ELSE
Error("Error HyperFileSQL : " + HErrorInfo())
END
Close_interface()
This is the SQL of the query:
SELECT DISTINCT
Data_source_name.IREPC AS IREPC,
Data_source_name.CONK AS CONK
iperiod AS iperiod
SUM(IAMT) AS sum_IAMT,
SUM(INBTR) AS sum_INBTR
FROM
Data_source_name
WHERE
IREPC 'HFEES'
Data_source_name.CONK = 'M92'
AND iperiod > '201301'
GROUP BY
IREPC,
CONK,
iperiod
The query does run, but I get a -1 records....
Any ideas what is wrong?
Antworten:
I'm not an sql expert, but you write this
WHERE
IREPC 'HFEES'
Data_source_name.CONK = 'M92'
AND iperiod > '201301'
Should it not be something like this instead :
WHERE
IREPC 'HFEES'
AND Data_source_name.CONK = 'M92'
AND iperiod > '201301'
Best regards
Viggo Poulsen
von Viggo Poulsen - am 30.08.2013 19:19
issah
von issah - am 31.08.2013 23:45