如何修改SQL路径表达式以让Crystal Report读取多份PDF文件？

It looks like your current SQL generates a single PDF path per order, but you need to pull all related PDFs for the same order. Let’s cover two common scenarios based on how your PDF files are structured:

Scenario 1: PDFs have sequential suffixes (e.g., 53244_Custom_1.pdf, 53244_Custom_2.pdf) #

If your multiple PDFs follow a pattern with sequential numbers at the end, you’ll need to generate multiple rows for each order—one per PDF file. To do this, use a numbers table (or a CTE to generate a range of numbers) to append the suffix to your path.

Here’s an example using a CTE to generate numbers 1 to 5 (adjust the range based on your maximum expected PDFs per order):

WITH NumberRange AS (
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1 FROM NumberRange WHERE Num < 5 -- Update 5 to your max number of PDFs per order
)
SELECT 
    CASE WHEN pd.pdCode LIKE 'CUST%' THEN 
        'Y:\300 ORDER PROCESSING\Majid Ahmadi\' + CAST(ord.ordPONumber AS nvarchar(25)) + '_Custom_' + CAST(n.Num AS nvarchar(2)) + '.pdf'
    ELSE NULL END AS imageFilePath
FROM 
    YourOrderTable ord -- Replace with your actual order table name
JOIN 
    pdTable pd ON ord.pdId = pd.pdId -- Update with your actual join condition
CROSS JOIN 
    NumberRange n
WHERE 
    -- Optional: Only return paths for files that actually exist (adjust for your DBMS)
    EXISTS (
        SELECT 1 FROM sys.fn_file_exists('Y:\300 ORDER PROCESSING\Majid Ahmadi\' + CAST(ord.ordPONumber AS nvarchar(25)) + '_Custom_' + CAST(n.Num AS nvarchar(2)) + '.pdf')
        WHERE value = 1
    )

This will return one row per existing numbered PDF for each order. The existence check helps avoid returning paths to non-existent files.

Scenario 2: PDFs are tracked in a related database table #

If you have a table that stores each PDF filename linked to an order (e.g., OrderPDFs with columns ordPONumber and pdfFileName), join this table to your main query to get all valid paths:

SELECT 
    CASE WHEN pd.pdCode LIKE 'CUST%' THEN 
        'Y:\300 ORDER PROCESSING\Majid Ahmadi\' + op.pdfFileName
    ELSE NULL END AS imageFilePath
FROM 
    YourOrderTable ord -- Replace with your actual order table name
JOIN 
    pdTable pd ON ord.pdId = pd.pdId -- Update with your actual join condition
JOIN 
    OrderPDFs op ON ord.ordPONumber = op.ordPONumber
WHERE 
    op.pdfFileName LIKE CAST(ord.ordPONumber AS nvarchar(25)) + '_Custom%.pdf' -- Filter to custom PDFs if needed

This approach is more reliable if you already have a record of each PDF in your database, as it avoids guessing filenames.

In Crystal Reports #

Once your SQL returns all PDF paths per order, display them using:

• A subreport linked to the main report by ordPONumber, which lists all PDFs for the order.
• A dynamic image control (if you want to display PDFs inline) that uses a formula or custom code to iterate through the paths.

内容的提问来源于stack exchange，提问作者MJX Boz