Hello there!
I created a report using the SQL Statement type, and now I'd like to build the same report using the query builder tool.
I attempted to do so using the builder tool, but was unsuccessful.
Could you confirm whether there is a way to convert the SQL file that is generated through the export function to the query builder type? That would allow me to import it into the system.
Why do I would like to do that? I’m try to enable the mail merge option on RMB at the query result screen, I managed to do it using the Customer_Info_Contact tab on the query builder
Here is my SQL statement:
SELECT
soa.part_no,
soa.lot_batch_no,
soa.analysis_no,
nt.description AS determinacao,
an.outer_min,
an.outer_max,
nt.unit_code,
qsv.result,
rpc.receiver_id,
rpc.person_id
FROM
shop_order_analysis soa
JOIN (
SELECT COMPONENT_PART FROM (
SELECT
COMPONENT_PART,
ROW_NUMBER() OVER (ORDER BY ENG_CHG_LEVEL DESC) AS rn
FROM PROD_STRUCTURE
WHERE PART_NO = '&PART_NO'
AND BOM_TYPE_DB = 'M'
AND ALTERNATIVE_NO = '*'
AND COMPONENT_PART LIKE 'PSA%'
)
WHERE rn = 1
) cte ON soa.part_no = cte.COMPONENT_PART
JOIN analysis_norm an ON an.analysis_no = soa.analysis_no
JOIN norm_type nt ON nt.norm_type = an.norm_type
JOIN qman_sample_value qsv
ON qsv.analysis_no = an.analysis_no
AND qsv.data_point = an.data_point
LEFT JOIN (
SELECT *
FROM (
SELECT
smrm.receiver_id,
cic.person_id,
smra.part_no,
smra.lot_batch_no,
ROW_NUMBER() OVER (
PARTITION BY smra.part_no, smra.lot_batch_no
ORDER BY smrm.receiver_id
) AS rn
FROM single_manual_reservation_alt smra
JOIN source_manual_reserv_main_alt smrm
ON smrm.source_ref1 = smra.source_ref1
AND smrm.source_ref2 = smra.source_ref2
AND smrm.source_ref3 = smra.source_ref3
AND smrm.source_ref4 = smra.source_ref4
LEFT JOIN customer_info_contact cic
ON cic.customer_id = smrm.receiver_id
WHERE smra.part_no = '&PART_NO'
AND smra.lot_batch_no = '&LOT_BATCH_NO'
AND smra.source_ref1 = '&SOURCE_REF1'
)
WHERE rn = 1
) rpc ON rpc.part_no = '&PART_NO' AND rpc.lot_batch_no = soa.lot_batch_no
WHERE soa.lot_batch_no = '&LOT_BATCH_NO'
AND soa.part_no = cte.COMPONENT_PART
