Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
3
Erster Beitrag:
vor 4 Jahren, 4 Monaten
Letzter Beitrag:
vor 4 Jahren, 4 Monaten
Beteiligte Autoren:
issah, Viggo Poulsen, Peter Adema

Query in SQL and connection with other database

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?

Antworten:

Hi

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
First rule of SQL, write it without the 'where' clause, then add them one by one; then its easy to spot the errant code.

issah

von issah - am 31.08.2013 23:45
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.