SQL Query No Results

ALL,

The query below returns all rspf.rspf_prod_n(product numbers) with barcodes (RSBB.RSBB_POS_N) only.





Select Distinct
CASE
    WHEN CHARINDEX('-',RSPF.RSPF_PROD_N) = 0 THEN LTRIM(RTRIM(RSPF.RSPF_PROD_N))
    ELSE CASE
            WHEN RSPF.RSPF_R_OR_S = 'R' THEN SUBSTRING(LTRIM(RTRIM(RSPF.RSPF_PROD_N)), 1, CHARINDEX('-',LTRIM(RTRIM(RSPF.RSPF_PROD_N)))-1)
            ELSE LTRIM(RTRIM(RSPF.RSPF_PROD_N))
    END
END AS PRODUCT_NUM,
Ltrim(Rtrim(RSBB_POS_N)) AS BarCode,
RSPF.RSPF_R_OR_S AS RENTAL_OR_SALE,
RSPF.RSPF_DESC AS ITEM_DESC,
LTRIM(RTRIM(RSPF.RSPF_CLASS)) AS CLASSNUM,
RSPC.RSPC_DESC AS CLASS_DESC,
LTRIM(RTRIM(RSPF.RSPF_GROUP_N)) AS GROUPNUM,
RSGF.RSGF_DESC AS GROUP_DESC,
RSPF.RSPF_VENDOR_N AS VENDOR_NUM,
RSPF.RSPF_VENDOR_PROD_N AS Vendor_Prod_Num,
(SELECT APVF.APVF_NAME FROM APVF WHERE APVF_VENDOR_N = RSPF.RSPF_VENDOR_N) AS VENDOR_NAME,
CASE
    WHEN RSPF.RSPF_R_OR_S = 'R' THEN ''
    ELSE CONVERT(VARCHAR, RSPF.RSPF_CREATED_DATE, 111)
END AS CREATE_DATE,
(SELECT RSPFC.RSPFC_CODE FROM RSPFC WHERE RSPFC.RSPFC_PROD_N = RSPF.RSPF_PROD_N) AS ORDER_CODE,
RSPF.RSPF_COST AS COST,
CAST(CAST(RSPF.RSPF_REPLACEMENT as numeric(7,2)) as varchar(8)) AS REPLACEMENT_COST,
RSPF.RSPF_REORDER_PRICE AS NEXT_BUY_COST,
RSPF.RSPF_LIST AS LIST_PRICE,
RSPF.RSPF_INV_Y_N
FROM RSPF, RSPC, RSGF, RSBB
WHERE RSPC.RSPC_CLASS=RSPF.RSPF_CLASS
AND RSGF.RSGF_GROUP_N = RSPF.RSPF_GROUP_N
AND RSPF.RSPF_R_OR_S = 'S'
AND RSPF.RSPF_PROD_N = RSBB.RSBB_PROD_N
GO


I need to show all product numbers even if they don't have a barcode, so I tried to do a left join on RSPF. The query just runs without ever returning anything. See below:


SELECT DISTINCT
CASE
    WHEN CHARINDEX('-',RSPF.RSPF_PROD_N) = 0 THEN LTRIM(RTRIM(RSPF.RSPF_PROD_N))
    ELSE CASE
            WHEN RSPF.RSPF_R_OR_S = 'R' THEN SUBSTRING(LTRIM(RTRIM(RSPF.RSPF_PROD_N)), 1, CHARINDEX('-',LTRIM(RTRIM(RSPF.RSPF_PROD_N)))-1)
            ELSE LTRIM(RTRIM(RSPF.RSPF_PROD_N))
    END
END AS PRODUCT_NUM,
Ltrim(Rtrim(RSBB_POS_N)) AS BarCode,
RSPF.RSPF_R_OR_S AS RENTAL_OR_SALE,
RSPF.RSPF_DESC AS ITEM_DESC,
LTRIM(RTRIM(RSPF.RSPF_CLASS)) AS CLASSNUM,
RSPC.RSPC_DESC AS CLASS_DESC,
LTRIM(RTRIM(RSPF.RSPF_GROUP_N)) AS GROUPNUM,
RSGF.RSGF_DESC AS GROUP_DESC,
RSPF.RSPF_VENDOR_N AS VENDOR_NUM,
RSPF.RSPF_VENDOR_PROD_N AS Vendor_Prod_Num,
(SELECT APVF.APVF_NAME FROM APVF WHERE APVF_VENDOR_N = RSPF.RSPF_VENDOR_N) AS VENDOR_NAME,
CASE
    WHEN RSPF.RSPF_R_OR_S = 'R' THEN ''
    ELSE CONVERT(VARCHAR, RSPF.RSPF_CREATED_DATE, 111)
END AS CREATE_DATE,
(SELECT RSPFC.RSPFC_CODE FROM RSPFC WHERE RSPFC.RSPFC_PROD_N = RSPF.RSPF_PROD_N) AS ORDER_CODE,
RSPF.RSPF_COST AS COST,
CAST(CAST(RSPF.RSPF_REPLACEMENT as numeric(7,2)) as varchar(8)) AS REPLACEMENT_COST,
RSPF.RSPF_REORDER_PRICE AS NEXT_BUY_COST,
RSPF.RSPF_LIST AS LIST_PRICE,
RSPF.RSPF_INV_Y_N
FROM RSPF, RSPC, RSGF, RSBB
Left Join RSPF A
on A.RSPF_PROD_N = RSBB.RSBB_PROD_N
WHERE RSPC.RSPC_CLASS=RSPF.RSPF_CLASS
AND RSGF.RSGF_GROUP_N = RSPF.RSPF_GROUP_N
AND RSPF.RSPF_R_OR_S = 'S'
GO



Please advise.