Cross join not working

Kinetic ERP
, , , , ,
1

I have tried CTE and temporary table to create a cross join which works outside Epicor but not in Epicor.

What am I doing wrong or what should I do?

I have various versions as I tested with substituting test values for database values.

Note: the versions with table guid do return just a single row instead of 30 or relevant number of rows created by the union.

V1.

="WITH RowsQty AS (
SELECT 1 AS RowsX4 
UNION ALL 
SELECT 1 + RowsX4 
FROM RowsQty 
WHERE RowsX4 < 30
) 
SELECT * from (SELECT T1.JobNum,T2.PartNum 
 FROM JobHead_" + Parameters!TableGuid.Value + " T1 
 LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T2 
  ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum) st1 
CROSS JOIN RowsQty"

V2.

="WITH RowsQty AS (
SELECT 1 AS RowsX4 
UNION ALL 
SELECT 1 + RowsX4 
FROM RowsQty 
WHERE RowsX4 < (SELECT ProdQty / 4 + case when ProdQty  % 4 > 0 then 1 else 0 end FROM JobHead_" + Parameters!TableGuid.Value + " T1 where t1.JobNum = '" +Parameters!SR_JobNum.Value.ToString() +"') 
) 
SELECT * from (SELECT T1.JobNum,T2.PartNum 
 FROM JobHead_" + Parameters!TableGuid.Value + " T1 
 LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T2 
  ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum) st1 
CROSS JOIN RowsQty"

V3.

DECLARE @i int = 0
create table RowsQty (RowsX4 int)
WHILE @i < 20
BEGIN
    SET @i = @i + 1
    insert into RowsQty (RowsX4) values (@i)
END; 
Select * from (SELECT 'FRM000123' as JobNum, 'KLM010203' as PartNum) t1 
CROSS JOIN RowsQty

Test link for arbitrary data outside Epicor.

2

My error, the traveller I duplicated to sub report had the last Group by JobNum active. Ignoring the cross join and just returning the 1 row.

Resetting the grouping resolved my problem