Need Equivalent Sql Query for the Select Structure(VC++)

prudhvi · Oct 13, 2008

Can any body help me out in writing equivalent SQL query to the following select structure...Thanks in advance...
/**************************************************************************
* Business Function: CreateF03B11SelectStruct
*
* Description: Populates Select Data Structure For F03B11
*
* Parameters:
* LPBHVRCOM lpBhvrCom Business Function Communications
* LPVOID lpVoid Void Parameter - DO NOT USE!
* LPDSD03B0203B lpDS Parameter Data Structure Pointer
*
*************************************************************************/
ID I03B0203_FillF03B11SelectStructure(LPDSD03B0203A lpDS, NEWSELECTSTRUCT* SelectF03B11, int* nNumSelectF03B11)
{

jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_DCT);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_AND;
SelectF03B11[*nNumSelectF03B11].lpValue = "R1";
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_NE;
(*nNumSelectF03B11)++;

jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_DCT);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_AND;
SelectF03B11[*nNumSelectF03B11].lpValue = "R5";
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_NE;
(*nNumSelectF03B11)++;

jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_DCT);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_AND;
SelectF03B11[*nNumSelectF03B11].lpValue = "RU";
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_NE;
(*nNumSelectF03B11)++;

jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_DCT);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_AND;
SelectF03B11[*nNumSelectF03B11].lpValue = "RB";
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_NE;
SelectF03B11[*nNumSelectF03B11].nParen = JDEDB_PAREN_OPEN;
(*nNumSelectF03B11)++;

jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_DCT);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_OR;
SelectF03B11[*nNumSelectF03B11].lpValue = "RB";
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_EQ;
(*nNumSelectF03B11)++;

jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_ICUT);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_AND;
SelectF03B11[*nNumSelectF03B11].lpValue = "IB";
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_EQ;
SelectF03B11[*nNumSelectF03B11].nParen = JDEDB_PAREN_CLOSE;
(*nNumSelectF03B11)++;

jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_POST);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_AND;
SelectF03B11[*nNumSelectF03B11].lpValue = "D";
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_EQ;
SelectF03B11[*nNumSelectF03B11].nParen = JDEDB_PAREN_OPEN;
(*nNumSelectF03B11)++;

jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_POST);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_OR;
SelectF03B11[*nNumSelectF03B11].lpValue = "D";
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_NE;
(*nNumSelectF03B11)++;

jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_ISTR);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_AND;
SelectF03B11[*nNumSelectF03B11].lpValue = "1";
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_EQ;
SelectF03B11[*nNumSelectF03B11].nParen = JDEDB_PAREN_CLOSE;
(*nNumSelectF03B11)++;

if (!IsStringBlank(lpDS->szAccountId))
{
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_AID);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_AND;
SelectF03B11[*nNumSelectF03B11].lpValue = lpDS->szAccountId;
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_EQ;
(*nNumSelectF03B11)++;
}
if (MathZeroTest(&lpDS->mnCentury) != 0)
{
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_CTRY);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_AND;
SelectF03B11[*nNumSelectF03B11].lpValue = &lpDS->mnCentury;
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_GE;
(*nNumSelectF03B11)++;
}
if (MathZeroTest(&lpDS->mnFiscalYear) != 0)
{
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_FY);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_AND;
SelectF03B11[*nNumSelectF03B11].lpValue = &lpDS->mnFiscalYear;
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_GE;
(*nNumSelectF03B11)++;
}
if (MathZeroTest(&lpDS->mnPeriodNumber) != 0)
{
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szDict, NID_PN);
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item1.szTable, NID_F03B11);
SelectF03B11[*nNumSelectF03B11].Item1.idInstance = 0;
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szDict, "");
jdeNIDcpy(SelectF03B11[*nNumSelectF03B11].Item2.szTable, "");
SelectF03B11[*nNumSelectF03B11].Item2.idInstance = 0;
SelectF03B11[*nNumSelectF03B11].nValues = 1;
SelectF03B11[*nNumSelectF03B11].nAndOr = JDEDB_ANDOR_AND;
SelectF03B11[*nNumSelectF03B11].lpValue = &lpDS->mnPeriodNumber;
SelectF03B11[*nNumSelectF03B11].nCmp = JDEDB_CMP_GE;
(*nNumSelectF03B11)++;
}
return (0);

}

JMR · Oct 13, 2008

Well, I'll try to translate for you, but there are some conditional selects in the code based on non-blank/non-zero values of some of the bsfn parameters. A good method for viewing sql's is to turn on debug logging which will log the actual sql statement used.

So here goes:

select ....
from f03b11
where dct <> 'R1'
and dct <> 'R5'
and dct <> 'RU'
and (dct <> 'RB' or dct = 'RB' and icut = 'IB')
and (post = 'D' or post <> 'D' and istr = '1')
and aid = <parameter - if not blank>
and ctry >= <paramter - if not zero>
and fy >= <parameter - if not zero>
and pn >= <parameter - if not zero

Like I mentioned, try the debug logging to see the actual sql statement.

prudhvi · Oct 13, 2008

Jeremy ,

Thanks for your clue on the query. I have one more question. I enabled jdedebug.log in jde.ini but its not generating any SQL Query for the VC++ code. Do i need to change any settings in the jde.ini? please let me know if anything needs to be done to get the sql queries for C Business funtions in JDEDEBUG.log

JMR · Oct 14, 2008

All I can say is that it should be logging the c-code. Are you absolutely sure it is not? Did you search for "F03B11" in the debuglog?

Put a breakpoint in the bsfn immediately after the call to the JDB_SelectKeyed() that is used after the JDB_SetSelectionX(). Then look at the end of the debuglog...you should see the SQL there.

Good luck.

prudhvi · Oct 16, 2008

Yes Jeremy

I can see the SQL statement in the log. Thanks for your help..

Need Equivalent Sql Query for the Select Structure(VC++)

prudhvi

Active Member

JMR

VIP Member

prudhvi

Active Member

JMR

VIP Member

prudhvi

Active Member

Similar threads

We value your privacy