Skip to Content
0
Former Member
Oct 01, 2007 at 06:17 PM

CR does not select any records

10 Views

Post Author: snufse

CA Forum: Data Connectivity and SQL

I have a Crystal Report that I imported from version 8.5 into XI. It created a sql script identical to the 8.5 version but when I run the report it does not select any records. Records selection is based upon a parameter which is date and time for a sales ticket (last line of the script). I have a sales ticket (primary) table that is linked to 3 other tables (material, mix and mixform)

SELECT "SALESTKT"."TICKET_DATE", "SALESTKT"."TRUCK", "SALESTKT"."TRUCK_LICENSE", "SALESTKT"."TRUCK_DESCRIPTION", "SALESTKT"."MAX_LEGAL_WEIGHT", "SALESTKT"."DRIVER", "SALESTKT"."TARE", "SALESTKT"."TARE_SCALE", "SALESTKT"."TARE_METHOD", "SALESTKT"."GROSS", "SALESTKT"."GROSS_SCALE", "SALESTKT"."GROSS_WEIGHT_METHOD", "SALESTKT"."TARGET_WEIGHT", "SALESTKT"."JOB", "SALESTKT"."CUSTOMER", "SALESTKT"."MATERIAL", "SALESTKT"."HAULER_DESCRIPTION_1", "SALESTKT"."HAULER_DESCRIPTION_2", "SALESTKT"."HAULER_DESCRIPTION_3", "SALESTKT"."HAULER_DESCRIPTION_4", "SALESTKT"."HAULER_CONTACT", "SALESTKT"."HAULER_STREET", "SALESTKT"."HAULER_CITY", "SALESTKT"."HAULER_STATE", "SALESTKT"."HAULER_ZIP", "SALESTKT"."CUSTOMER_PO", "SALESTKT"."JOB_TYPE", "SALESTKT"."PROJECT", "SALESTKT"."JOB_PO", "SALESTKT"."JOB_DESCRIPTION_1", "SALESTKT"."JOB_DESCRIPTION_2", "SALESTKT"."JOB_DESCRIPTION_3", "SALESTKT"."JOB_DESCRIPTION_4", "SALESTKT"."JOB_CONTACT", "SALESTKT"."JOB_STREET", "SALESTKT"."JOB_CITY", "SALESTKT"."JOB_STATE", "SALESTKT"."JOB_ZIP", "SALESTKT"."JOB_INFORMATION_1", "SALESTKT"."JOB_INFORMATION_2", "SALESTKT"."JOB_INFORMATION_3", "SALESTKT"."PAYMENT_METHOD", "SALESTKT"."CHECK_NUMBER", "SALESTKT"."FOB", "SALESTKT"."MATERIAL_TAX_CODE", "SALESTKT"."FREIGHT_TAX_CODE", "SALESTKT"."DOT_PROJECT_CODE", "SALESTKT"."QA_PROJECT_CODE", "SALESTKT"."JOB_PHASE", "SALESTKT"."MATERIAL_TYPE", "SALESTKT"."MATERIAL_DESCRIPTION", "SALESTKT"."MATERIAL_CODE", "SALESTKT"."MIX", "SALESTKT"."ASPHALT_CONTENT", "SALESTKT"."CUSTOMER_TYPE", "SALESTKT"."CUSTOMER_DESCRIPTION_1", "SALESTKT"."CUSTOMER_DESCRIPTION_2", "SALESTKT"."CUSTOMER_DESCRIPTION_3", "SALESTKT"."CUSTOMER_DESCRIPTION_4", "SALESTKT"."CUSTOMER_CONTACT", "SALESTKT"."CUSTOMER_STREET", "SALESTKT"."CUSTOMER_CITY", "SALESTKT"."CUSTOMER_STATE", "SALESTKT"."CUSTOMER_ZIP", "SALESTKT"."CREDIT_LIMIT", "SALESTKT"."CREDIT_BALANCE", "SALESTKT"."NET", "SALESTKT"."UNIT_PRICE", "SALESTKT"."EXTENDED_PRICE", "SALESTKT"."TAX", "SALESTKT"."EXTENDED_TOTAL", "SALESTKT"."EDITED_PRIOR_TICKET", "SALESTKT"."EDITED_TICKET", "SALESTKT"."VOID", "SALESTKT"."VOID_REASON_CODE", "SALESTKT"."WEIGHMASTER", "SALESTKT"."COMMENTS_1", "SALESTKT"."COMMENTS_2", "SALESTKT"."COMMENTS_3", "SALESTKT"."QTY_ORDERED", "SALESTKT"."QTY_SHIPPED_TODAY", "SALESTKT"."LOADS_TODAY", "SALESTKT"."QTY_SHIPPED_PTD", "SALESTKT"."LOADS_PTD", "SALESTKT"."QTY_SHIPPED_YTD", "SALESTKT"."LOADS_YTD", "SALESTKT"."FOB_DESCRIPTION", "SALESTKT"."FOB_STREET", "SALESTKT"."FOB_CITY", "SALESTKT"."FOB_STATE", "SALESTKT"."FOB_ZIP", "SALESTKT"."FOB_PHONE", "SALESTKT"."GENERATE_INVOICE", "SALESTKT"."INCOMING_MATERIAL", "SALESTKT"."METRIC_MAX_LEGAL_WEIGHT", "SALESTKT"."METRIC_TARE", "SALESTKT"."METRIC_GROSS", "SALESTKT"."METRIC_TARGET_WEIGHT", "SALESTKT"."METRIC_NET", "SALESTKT"."PRINTED", "SALESTKT"."MATERIAL_LOCATION", "SALESTKT"."FREIGHT", "SALESTKT"."INVOICE", "SALESTKT"."INVOICE_DATE", "SALESTKT"."MATERIAL_DENSITY", "SALESTKT"."MATERIAL_MOISTURE", "SALESTKT"."MIX_TEMPERATURE", "SALESTKT"."VENDOR", "SALESTKT"."VENDOR_TYPE", "SALESTKT"."VENDOR_DESCRIPTION_1", "SALESTKT"."VENDOR_DESCRIPTION_2", "SALESTKT"."VENDOR_DESCRIPTION_3", "SALESTKT"."VENDOR_DESCRIPTION_4", "SALESTKT"."VENDOR_CONTACT", "SALESTKT"."VENDOR_STREET", "SALESTKT"."VENDOR_CITY", "SALESTKT"."VENDOR_STATE", "SALESTKT"."VENDOR_ZIP", "SALESTKT"."PRODUCT", "MIXFORM"."MIX", "MIXFORM"."CHANNEL_NUMBER", "MIXFORM"."CHANNEL_NAME", "MIXFORM"."MATERIAL_TYPE", "MIXFORM"."MATERIAL", "MIXFORM"."TARGET_PERCENT", "MIXFORM"."TOLERANCE", "MIXFORM"."START_TIME", "MIXFORM"."STOP_TIME", "MIX"."MIX", "MIX"."MIX_DESCRIPTION", "MIX"."PERCENT_RAP", "MIX"."FILE_UPDATED", "MATERIAL"."MATERIAL", "MATERIAL"."MATERIAL_TYPE", "MATERIAL"."MATERIAL_DESCRIPTION", "MATERIAL"."MATERIAL_CODE", "MATERIAL"."AMOUNT_SOLD_TODAY", "MATERIAL"."LOADS_TODAY", "MATERIAL"."SOLD_PTD", "MATERIAL"."LOADS_PTD", "MATERIAL"."SOLD_YTD", "MATERIAL"."LOADS_YTD", "MATERIAL"."RECD_TODAY", "MATERIAL"."LOADS_RECD_TODAY", "MATERIAL"."RECD_PTD", "MATERIAL"."LOADS_RECD_PTD", "MATERIAL"."RECD_YTD", "MATERIAL"."LOADS_RECD_YTD", "MATERIAL"."USED_TODAY", "MATERIAL"."USED_PTD", "MATERIAL"."USED_YTD", "MATERIAL"."ON_HAND", "MATERIAL"."NEEDED", "MATERIAL"."UNIT_COST", "MATERIAL"."LOCATION", "MATERIAL"."UNITS", "MATERIAL"."PRODUCT", "MATERIAL"."MIX", "MATERIAL"."ASPHALT_CONTENT", "MATERIAL"."DAILY_STARTING_INVENTORY", "MATERIAL"."PTD_STARTING_INVENTORY", "MATERIAL"."YTD_STARTING_INVENTORY", "MATERIAL"."LIQUID", "MATERIAL"."VENDOR", "MATERIAL"."DENSITY", "MATERIAL"."MOISTURE", "SALESTKT"."SHIFT_STARTED", "SALESTKT"."TICKET_TIME" FROM (("SALESTKT" "SALESTKT" INNER JOIN "MATERIAL" "MATERIAL" ON ((((((("SALESTKT"."MATERIAL"="MATERIAL"."MATERIAL") AND ("SALESTKT"."MATERIAL_TYPE"="MATERIAL"."MATERIAL_TYPE")) AND ("SALESTKT"."MATERIAL_DESCRIPTION"="MATERIAL"."MATERIAL_DESCRIPTION")) AND ("SALESTKT"."MATERIAL_CODE"="MATERIAL"."MATERIAL_CODE")) AND ("SALESTKT"."MIX"="MATERIAL"."MIX")) AND ("SALESTKT"."ASPHALT_CONTENT"="MATERIAL"."ASPHALT_CONTENT")) AND ("SALESTKT"."VENDOR"="MATERIAL"."VENDOR")) AND ("SALESTKT"."PRODUCT"="MATERIAL"."PRODUCT")) INNER JOIN "MIXFORM" "MIXFORM" ON (("SALESTKT"."MATERIAL"="MIXFORM"."MATERIAL") AND ("SALESTKT"."MATERIAL_TYPE"="MIXFORM"."MATERIAL_TYPE")) AND ("SALESTKT"."MIX"="MIXFORM"."MIX")) INNER JOIN "MIX" "MIX" ON "SALESTKT"."MIX"="MIX"."MIX" WHERE ("SALESTKT"."SHIFT_STARTED">={ts '2007-09-19 19:34:00'} AND "SALESTKT"."SHIFT_STARTED"<{ts '2007-09-19 19:34:01'})