locked
using case and between on a left join. RRS feed

  • Question

  • User-2146352328 posted

    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.

    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.

    Wednesday, September 7, 2016 9:34 AM

All replies

  • User-1404113929 posted

    hi,

    can you post full query . i cant understand your query.

    thanks,

    murali

    Wednesday, September 7, 2016 9:59 AM
  • User-2146352328 posted

    Hi.

    The join's are here:

    LEFT JOIN tblClientOrderItem COI ON	VS.lvouchertypeid = COI.lvouchertypeid
    -------
    
    
      AND (RIGHT(isnull(VStock_strBookletIdent,TransT_strBarcodeRedemp),7) BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1
    		                                    OR RIGHT(isnull(VStock_strBookletIdent,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

    I'm trying something more simple like:

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

    But not sure 100% that this is correct. Can you confirm?

    Thanks.

    Wednesday, September 7, 2016 10:12 AM
  • User-2146352328 posted

    So sorry , that is the fixed version I try.

    This is the query:

    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

    Wednesday, September 7, 2016 10:16 AM
  • User-1404113929 posted

    hi,

    can you post full query , i mean with scomplete sql query, and can you please explain about your present issue, i can't figure out what is your problem.

    thanks,

    murali

    Wednesday, September 7, 2016 12:59 PM
  • User-2146352328 posted

    Hi.

    It's a 5000 lines query.

    The problem is here:

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

    With pseudo code what i need is:

    'and' 

    If RIGHT(TransT_strBarcodeRedemp,7 = true then BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1

    Else

    'and'

    RIGHT(VStock_strBookletIdent,7 =  BETWEEN COI.lStartVoucherNumber AND COI.lStartVoucherNumber + COI.lQtySupplied - 1

    Wednesday, September 7, 2016 1:24 PM
  • User-1404113929 posted

    hi,

    this condition is correct. i think you are missed close bracket.

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

    thanks,

    murali.

    Thursday, September 8, 2016 4:32 AM