CODEWITHSUNDEEP
javasql
Trends Ave
Trends Ave

Reputation: 1

Can anyone help me with this sql query

I have the following code with a SQL query in PreparedSentence:

public final ProductInfoExt getProductInfoByCode(String sCode, String siteGuid) throws BasicException {
    if (sCode.startsWith("977")) {
        // This is an ISSN barcode (news and magazines) 
        // the first 3 digits correspond to the 977 prefix assigned to serial publications, 
        // the next 7 digits correspond to the ISSN of the publication 
        // Anything after that is publisher dependant - we strip everything after  
        // the 10th character 
        sCode = sCode.substring(0, 10);
    }
    return (ProductInfoExt) new PreparedSentence(s, "SELECT "
            + getSelectFieldList()
            + " FROM STOCKCURRENT AS C RIGHT JOIN PRODUCTS P ON (C.PRODUCT = P.ID) "
            + " WHERE P.CODE OR (P.REFERENCE = ? ) AND C.SITEGUID = ? ",
            new SerializerWriteBasicExt(new Datas[]{Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.STRING}, new int[]{0, 1}),
            ProductInfoExt.getSerializerRead()).find(sCode, siteGuid);
}

It works great if I use search through P.CODE: WHERE P.CODE = ? AND C.SITEGUID = ?. However, lets say I want it to find a result in P.REFERENCE if nothing matches in P.CODE. I tried doing a code statement like this with no success: WHERE P.CODE OR P.REFERENCE = ? AND C.SITEGUID = ?, but I received an error. Any help would be greatly appreciated.

Upvotes: 0

Views: 60

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13026

group your OR statement

return (ProductInfoExt) new PreparedSentence(s, "SELECT "
        + getSelectFieldList()
        + " FROM STOCKCURRENT AS C RIGHT JOIN PRODUCTS P ON (C.PRODUCT = P.ID) "
        + " WHERE (P.CODE = ? OR P.REFERENCE = ?) AND C.SITEGUID = ? ",
        new SerializerWriteBasicExt(new Datas[]{Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.STRING}, new int[]{0, 1, 2}),
        ProductInfoExt.getSerializerRead()).find(sCode, sCode, siteGuid);

Upvotes: 1

Villat
Villat

Reputation: 1475

Your syntax is wrong, it should be

WHERE (P.CODE = ?  OR P.REFERENCE = ?) AND C.SITEGUID = ?

And then you will need to set the third parameter

Upvotes: 0

Related Questions