[SOLVED] Hybris ImpEx - how to export all Orders, which contain a specific coupon code

Hybris ImpEx - how to export all Orders, which contain a specific coupon code

I want to export all items of type Order, which contain a coupon code named 'TESTCOUPON'. However, when I try to do it, I get this error:

ERROR   line 4 at main script: error executing code line at 4 : SQL search error - ORA-00932: inconsistent datatypes: expected - got BLOB
 query = 'SELECT  item_t0.PK  FROM orders item_t0 WHERE ( item_t0.p_appliedcouponcodes  = 'TESTCOUPON') AND (item_t0.TypePkString IN  (?,?,?) )', values = [8796099149906, 8796099215442, 8796098756690]

I assume from the error that the coupon is stored in a Collection/List - how would I filter by it in this case?

The FlexibleSearch line looks like this:

SELECT {C:PK} FROM {Order as C} WHERE {C:appliedCouponCodes} = 'TESTCOUPON'

I tried using the PK of the coupon code, but it doesn't work either and it presents the same error. I tried using LIKE '%TESTCOUPON%' but then it said 'expected CHAR got BLOB'.

Solution

As per the Table structure CouponRedemption having reference of Order .

so correct query will be as below.

select { o.code },{o.pk},{cr.couponCode} from {Order as o Join CouponRedemption as cr on {cr.order}={o.pk}} where {code}='TESTCOUPON'

Output:

code        PK             p_couponcode
1050156308  9499773075501   BUY4
1044303645  9499775172653   BUY4
1042057811  9499796897837   BUY4
1049853832  9499798863917   BUY4