Query where cause 2 field ID

srattha@hitt.hitachi-asia.com

        Editor::inst($db, **tablename**)
        ->pkey('ID')
        ->fields(
            Field::inst('ID'),
            Field::inst('ORGANIZATION_ID'),
            Field::inst('BARCODE'),
            Field::inst('ITEM_NO'),
            Field::inst('DESCRIPTION'),
            Field::inst('ONHAND_QTY'),
            Field::inst('ONHAND_UOM'),
            Field::inst('ACTUAL_QTY'),
            Field::inst('ACTUAL_UOM'),
            Field::inst('REMARK')
        )
        ->where('ACTUAL_QTY',null,'<>')
        ->where(**Field::inst('ACTUAL_QTY')**, **Field::inst('ONHAND_QTY')**,'<')
        ->debug(true)
        ->process($_POST)
        ->json();

colin

We're happy to help, but please can you give some clues as to what you're querying? What's the problem?

Colin

srattha@hitt.hitachi-asia.com

How to query where cause 2 field ID but It error
ex.
Editor::inst($db, tablename)
->pkey('ID')
->fields(
Field::inst('ID'),
Field::inst('ORGANIZATION_ID'),
Field::inst('BARCODE'),
Field::inst('ITEM_NO'),
Field::inst('DESCRIPTION'),
Field::inst('ONHAND_QTY'),
Field::inst('ONHAND_UOM'),
Field::inst('ACTUAL_QTY'),
Field::inst('ACTUAL_UOM'),
Field::inst('REMARK')
)
->where('ACTUAL_QTY',null,'<>')
->where('ACTUAL_QTY', 'ONHAND_QTY,'<') --> error this line
->debug(true)
->process($_POST)
->json();

I

colin

I'm sorry, I'm not following. What's the "cause 2"? And if you're seeing an error, what's the error you're getting. Please give as much information as you can, otherwise we won't have the information to help you.

Colin

srattha@hitt.hitachi-asia.com

Error is
{"fieldErrors":[],"error":"Oracle SQL error: ORA-01722: invalid number","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT \"ID\" \"ID\", \"ORGANIZATION_ID\" \"ORGANIZATION_ID\", \"BARCODE\" \"BARCODE\", \"ITEM_NO\" \"ITEM_NO\", \"DESCRIPTION\" \"DESCRIPTION\", \"ONHAND_QTY\" \"ONHAND_QTY\", \"ONHAND_UOM\" \"ONHAND_UOM\", \"ACTUAL_QTY\" \"ACTUAL_QTY\", \"ACTUAL_UOM\" \"ACTUAL_UOM\", \"REMARK\" \"REMARK\" FROM \"HITT_UND_BARCODE\" WHERE \"ACTUAL_QTY\" IS NOT NULL AND \"ACTUAL_QTY\" < :where_1 ","bindings":[{"name":":where_1","value":"ONHAND_QTY","type":null}]}]}

but I want query where cause ACTUAL_QTY column < ONHAND_QTY column

allan

Got it - the issue here is that Editor->where() will always "bind" the parameters - so it is treating it as if it were:

WHERE ACTION_QTY < 'ONHAND_QTY' % Note the string

The way to address this is described here. So what you want to do is:

->where( function ($q) {
  $q->where( 'ACTION_QTY', 'ONHAND_QTY', '<', false );
} )

The false in the fourth parameter means that the ONHAND_QTY will not be bound as a string, but treated as a raw SQL statement.

Allan

srattha@hitt.hitachi-asia.com

@allan thank you.