Using case and between on a left join

1

Hi.

I have this:

LEFT JOIN something COI
		ON	VS.lvouchertypeid = COI.lvouchertypeid

		---
	AND (RIGHT(TransT_strBarcodeRedemp,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1

		AND (RIGHT(TransT_strBarcodeRedemp,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1
		OR RIGHT(TransT_strVoucherBarcode,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1)
		AND COI.lQtySupplied>0

I want to change it into this:

LEFT JOIN something COI
		ON	VS.lvouchertypeid = COI.lvouchertypeid
	AND
	
	
	 CASE LEN(TransT_strBarcodeRedemp) 
			WHEN 20 THEN RIGHT(TransT_strBarcodeRedemp,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1  --20 digits
			ELSE LEFT(VStock_strBookletIdent,4) + '000000' + RIGHT(VStock_strBookletIdent,7) END

But I get an error on the between statement.

Am I doing something wrong or is there another approach?

Thanks.

2

or using something like:

AND (RIGHT(TransT_strBarcodeRedemp,7) between case len(TransT_strBarcodeRedemp)
  when 20 then COI.lStartVoucherNumber  END
  and case len(TransT_strBarcodeRedemp) when 20 then COI.lStartVoucherNumber + COI.lQtySupplied - 1 
  END
ELSE ....

But everything i tried in else cannot do what i want, that is to change the AND (RIGHT(TransT_strBarcodeRedemp,7) between case len(TransT_strBarcodeRedemp) to AND (RIGHT(VStock_strBookletIdent,7) between case len(TransT_strBarcodeRedemp) ...etc

3

In simple words i want a condition to check if TransT_strBarcodeRedemp = 20 digits.
If yes, then use "AND (RIGHT(TransT_strBarcodeRedemp,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1"
If not then use "AND (RIGHT(VStock_strBookletIdent,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1"

I've tried this:

AND (RIGHT(TransT_strBarcodeRedemp,7) between case len(TransT_strBarcodeRedemp) when 20 then COI.lStartVoucherNumber END and case len(TransT_strBarcodeRedemp) when 20 then COI.lStartVoucherNumber + COI.lQtySupplied - 1 END ELSE ?????
ELSE does not allow me to change the "VStock_strBookletIdent" between. I have no idea how to do it.
Thanks.

4

I am using something different, more simple but I'm not 100% sure about this.
Can you confirm?

AND (RIGHT(isnull(VStock_strBookletIdent,TransT_strBarcodeRedemp),7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1

5

This looks very inefficient.

I suspect your error arises from mixing strings and integers. You do not give the datatypes in the CASE but the result needs to be one type. Try explicitly casting to string or integer.

When asking this sort of question, you should post exactly what the error is.

6

Hi.
What is inefficient?
The last isnull attempt or the other attempts?
Here is the specific lines i need to change:

LEFT JOIN tblClientOrderItem COI ON VS.lvouchertypeid = COI.lvouchertypeid AND (RIGHT(TransT_strBarcodeRedemp,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1 OR RIGHT(TransT_strVoucherBarcode,7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1) AND COI.lQtySupplied>0 LEFT JOIN tblClientOrder CO ON CO.lid = COI.lclientorderid LEFT JOIN tblClient VC ON VC.lid = CO.lclientid

but I haven't gone so far as integer and strings, as the query is not complete.

As I've said I need to have the TransT_strBarcodeRedemp to change to VStock_strBookletIdent if it has less than 20 digits

7

The actual error you get would help.
Maybe something like:

LEFT JOIN tblClientOrderItem COI
        ON    VS.lvouchertypeid = COI.lvouchertypeid
                AND COI.lQtySupplied>0
                AND
                    CASE
                        WHEN LEN(TransT_strBarcodeRedemp) = 20
                        THEN CAST(RIGHT(TransT_strBarcodeRedemp,7) AS int)
                        ELSE CAST(RIGHT(TransT_strVoucherBarcode,7) AS int)
                    END
                    BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1
    LEFT JOIN tblClientOrder CO
        ON    CO.lid = COI.lclientorderid
    LEFT JOIN tblClient VC
        ON    VC.lid = CO.lclientid;
1 Like
8

Will check it out.
thanks.

9

Just looking at the post, without any knowledge of the issue of course, I would say that TransT_strBarcodeRedemp ought to be split into two columns, representing the two value-ettes that it contains. Peppering the code with string-splitting functions is a recipe for a future-nightmare (what happens when it becomes RIGHT(TransT_strBarcodeRedemp,8) ?? :frowning: and is also incredibly inefficient as it will mean that indexes cannot be used to resolve the queries (as the query will not be SARGable)

1 Like
10

You are correct but it's not my actual code.
It's a 5000 lines query that I need to implement a fast fix.
In the future I would take in consideration your observation.
thanks.