topic Re: sql select in QlikView

sql select

Fri, 12 Aug 2016 08:16:25 GMT

hello all,

can you tell if the syntax of the function below is correct ?

Normally i must have in the variable (gvNoOfRowsLastLoad ) at least 1 line but apparently i retrieve nothing in the database.

when i execute the request in the database i have a data.

LET pSQLWhereClause = 'WHERE DATE_APPLI = To_Date(' & CHR(39) & Date(Date#($(LVDateModif),'YYYYMMDD'),'MM/DD/YYYY') & CHR(39) & ',' & CHR(39) & 'MM/DD/YYYY' & CHR(39) & ') ';

sub SrcLoad_LISTE_DATE_APPLI (pSQLTableName,pSQLWhereClause )

LET gvNoOfRowsLastLoad = 0 ;

// Load the QVD Table from the SQL table

LISTE_DATE_APPLI_MODIFIED:

LOAD DATE_APPLI; SQL SELECT DATE_APPLI FROM $(gvDBOwner).$(pSQLTableName) $(pSQLWhereClause);

LET gvNoOfRowsLastLoad = NoOfRows('LISTE_DATE_APPLI_MODIFIED') ;

Trace gvNoOfRowsLastLoad : $(gvNoOfRowsLastLoad) ;

for vRow = 0 to NoOfRows('LISTE_DATE_APPLI_MODIFIED')

Let MyVar = Peek('DATE_APPLI',vRow-1,'LISTE_DATE_APPLI_MODIFIED');

CALL srLoad_M2R_DTM_ACKTRANSACTION_INCR( pSQLTableName, MyVar) ;

end sub

Can you help please ?

Regards

Re: sql select

Gysbert_Wassenaar — Fri, 12 Aug 2016 08:22:56 GMT

Check the content of the variables you use. Does LVDateModif have a value? Does pSQLWhereClause get a correct value?

Re: sql select

Fri, 12 Aug 2016 09:23:51 GMT

yes LVDateModif ='20160810' and yes pSQLWhereClause get WHERE DATE_APPLI = To_Date(' & CHR(39) & Date(Date#($(LVDateModif),'YYYYMMDD'),'MM/DD/YYYY') & CHR(39) & ',' & CHR(39) & 'MM/DD/YYYY' & CHR(39) & ')

Re: sql select

Gysbert_Wassenaar — Fri, 12 Aug 2016 09:32:27 GMT

Well, if pSQLWhereClause resolves to pSQLWhereClause = 'WHERE DATE_APPLI = To_Date(' & CHR(39) & Date(Date#($(LVDateModif),'YYYYMMDD'),'MM/DD/YYYY') & CHR(39) & ',' & CHR(39) & 'MM/DD/YYYY' & CHR(39) & ') ' I'm not suprised your SQL database doesn't understand it. It should resolve to something like WHERE DATE_APPLI = To_Date('08/10/2016', 'MM/DD/YYY'),

Re: sql select

Fri, 12 Aug 2016 12:34:29 GMT

that's what i get that means i get --> WHERE DATE_APPLI = ToDate('08/10/2016', 'MM/DD/YYY')

Re: sql select

Gysbert_Wassenaar — Fri, 12 Aug 2016 12:42:00 GMT

Then you have to correct where clause. If your database doesn't like it then you have to figure out what the correct syntax for a where clause is.