ADS SQL SELECT statement

[unixkd]

HI All

Can an ADS SQL SELECT statement have user defined function like:

SELECT * FROM MYTABLE WHERE MyFuction()

I learnt this is possible in Universal SQL of 2.0

Thanks.

Joe

[JManuelVar]

Buenas Tardes.
Yo utilizo no Utilizo Funciones en la Select del ADS.
Te Indico un Ejemplo

STATIC FUNCTION CargaVista(entrada)

if !entrada = Nil
Select (cAli30_01)
aiCierra((cAli30_01))
oStat30_01:close()
Endif
oStat30_01 := ""

mCadena := "SELECT * FROM TORDCA WHERE 1=1 "

if cSelAct="C"
mCadena += " AND ( OFAC = ' ' or OFAC = 'P' ) "
Elseif cSelAct="P"
mCadena += " AND ( OFAC = 'F' or OFAC = 'P' ) "
Endif
if !Empty(mSLBas)
mCadena += " AND (( obas LIKE '%"+alltrim(mSLBas)+"%') OR ( omat LIKE '%"+alltrim(mSLBas)+"%')) "
Endif

if !Empty(mSLCod)
mCadena += " AND ( ocli = "+str(mSLCod)+" ) "
Endif

cSelBusCli:=""
mBusqCad:=""

if !Empty(mSLCli)
mBusqCad:=alltrim(mSLCli)
mCadena += " AND ocli IN ( SELECT f7.ccod FROM fclien f7 Where f7.cnom LIKE '%"+alltrim(mBusqCad)+"%') "
Endif


if !Empty(mSLDoc)
mCadena += " AND (( odoc IN ( SELECT f8.odoc FROM tordtt f8 Where f8.ofdo = "+alltrim(str(mSLDoc))+" ) ) "
mCadena += " OR ( odoc IN ( SELECT f9.odoc FROM tordtt f9 Where f9.ogdo = "+alltrim(str(mSLDoc))+" ) ) "
mCadena += " OR ( odoc = "+alltrim(str(mSLDoc))+") )"
Endif

if !Empty(mSLCip)
mCadena += " AND (( odoc IN ( SELECT f10.odoc FROM tordtt f10 Where f10.ocip = '"+alltrim(mSLCip)+"' ) )) "
Endif

if !Empty(mSLMar) .or. !Empty(mSLMod)
if !Empty(mSLMar) .or. Empty(mSLMod)
mCadena += " AND ( ( emid IN ( SELECT f12.mid FROM jgmodelo f12 Where f12.mmar = '"+alltrim(mSLMar)+"' ) ) ) "
Elseif Empty(mSLMar) .or. !Empty(mSLMod)
mCadena += " AND ( ( emid IN ( SELECT f12.mid FROM jgmodelo f12 Where f12.mnom LIKE '%"+alltrim(mSLMod)+"%' ) ) ) "
Else
mCadena += " AND ( ( emid IN ( SELECT f12.mid FROM jgmodelo f12 Where f12.mmar = '"+alltrim(mSLMar)+"' AND f12.mnom LIKE '%"+alltrim(mSLMod)+"%' ) ) ) "
Endif
Endif

if mSLOrd="B"
mCadena += " ORDER BY obas "
Elseif mSLOrd="F"
mCadena += " ORDER BY oape DESC "
Else
mCadena += " ORDER BY odoc DESC "
Endif

cStat30_01 := mCadena

SELECT 0
oStat30_01 := AdsStatement():New(alltrim(cStat30_01),oSession)
IF oStat30_01:LastError > 0
MBox1("",str(oStat30_01:LastError))
RETURN .f.
ENDIF

cAli30_01 := oStat30_01:Execute()

SELECT (cAli30_01)
dbGoTop()

if !entrada = NIL
oBrowse30_01:refreshAll()
Endif

Return(.t.)

si tienes alguna duda enviame un email

[rdonnay]

I learnt this is possible in Universal SQL of 2.0

I have not been able to get Universal SQL of 2.0 to work. That's one of the reasons I still use ADS.

No you cannot call an Xbase++ function in the WHERE clause.

What do you want the WHERE clause to do?

[unixkd]

I have a complex Function that returns BOOLEAN which I want the returned value be passed to the WHERE clause of the SELECT statement.

e.g.

Function MyFuction(I)
Local lTest := (lAllo .Or. lDedu) .And. aPara[I, PARAMETER_STATUS] .And.;
InRange(aPara[I, PARA_TABLE_TYPE ], TableType) .And.;
InRange(aPara[I, EMPLOYEE_ID_RANGE ], EmpID) .And.;
InRange(aPara[I, EMPLOYEE_CATEGORY_RANGE], EmpCate) .And.;
InRange(aPara[I, STAFF_POST_RANGE ], Post) .And.;
InRange(aPara[I, ORG_CODE_RANGE ], OrgCode) .And.;
InRange(aPara[I, TYPE_APPOINTMENT_RANGE ], AppStatus) .And.;
InRange(aPara[I, COST_CENTRE_RANGE ], CC) .And.;
InRange(aPara[I, SEX_RANGE ], Sex) .And.;
ASCAN( aPara[I, MARITAL_STATUS_RANGE ], MStatus) # 0 .And.;
InRange(aPara[I, QUALIF_RANGE ], Qualif ) .And.;
InRange(aPara[I, SALARY_LEVEL_RANGE ], SalLevel ) .And.;
InRange(aPara[I, PAY_MODE_RANGE ], PayMode ) .And.;
InRange(aPara[I, BANK_RANGE ], BankCode ) .And.;
InRange(aPara[I, DATE_EMPLOYED_RANGE ], DateEmp ) .And.;
InRange(aPara[I, EMPLOYEE_STATUS_RANGE ], EmpStatus) .And.;
InRange(aPara[I, PERIOD_RANGE ], nPeriod )
Return(lTest)


Thanks.

Joe

[unixkd]

Hi all

Can ORDER BY clause be a concatenation of multiple fields e.g.

SELECT * FROM MyTable ORDER BY ORGCODE+IDNO

If Universal SQL works in 2.0, that will be a huge huge relief for Xbase++ community.

Thanks

Joe

[reganc]

Hi all

Can ORDER BY clause be a concatenation of multiple fields e.g.

SELECT * FROM MyTable ORDER BY ORGCODE+IDNO

If Universal SQL works in 2.0, that will be a huge huge relief for Xbase++ community.

Thanks

Joe

I believe that you can just do:

SELECT * FROM MyTable ORDER BY ORGCODE , IDNO

but I'm no expert...

[rdonnay]

I can show you how to do this without a function, but I need to know what the array aPara contains:

aPara[I, PARA_TABLE_TYPE ]

[omni]

Reginald is correct. order by can be as many fields as needed. I have some with 6 or more using the commas.

Fred
Omni

[unixkd]

Hi Roger

aPara is a multi-dimensional array with I representing the current row, while other constants such as PARAMETER_STATUS represent column.

Thanks.

Joe

[rdonnay]

This doesn't make sense.

You are saying that you have an array with the same number of elements as the number of rows in the database?

If you want to do range checking in the WHERE clause, you probably should use the BETWEEN clause.