I have a nasty, nasty data layout that I am forced to work with. I finally got a working query using C# and a for loop executing the same query over and over but adjusting which fields are called, but now I am wondering if it is possible to do it with a while loop. I am getting an error, and I am not sure if it is because I am using Faircom / C-tree as the database, or if there is something wrong with my query. I am normally a Mysql user.
the table has 20 fields I care about and want to extract into a csv list. They are codetype1-codetype20 and I want it to be something like value1, value2, value3... where as it is now I get them all back one at a time. Trouble is that codetype1 is dependent on another field to determine where I go look for the info on that code, which is why the case statements.
DROP PROCEDURE IF EXISTS proc_loop_test;
CREATE PROCEDURE proc_loop_test()
SET @index = 1;
WHILE(@index < 21) DO
SELECT Replace(Concat(To_char(apptid), To_char(.@index) ), ' ', '') AS reference_id,
apptid AS a_reference_id,
CASE
WHEN c.ee > 0 THEN d.amt
ELSE insfee.amt
END AS amount,
CASE
WHEN c.ee > 0 THEN Rtrim(e.moneyname)
ELSE insname.namefeecatid
END AS moneyschedule_name,
CASE codetype@index
WHEN 1 THEN rtrim(a.descript)
ELSE rtrim(b.descript0)
END AS description,
CASE codetype@index
WHEN 1 THEN rtrim(a.abbrevdescript)
ELSE rtrim(b.abbrev0)
END AS abbreviated_description,
CASE codetype@index
WHEN 1 THEN rtrim(a.thiscode)
ELSE rtrim(b.thiscode0)
END AS code
FROM meetings
LEFT JOIN
(
SELECT admin.table2.procid,
admin.table2.this_code_id,
admin.v_table1.descript,
admin.v_table1.abbrevdescript,
admin.v_table1.thiscode
FROM admin.table2
INNER JOIN admin.v_table1
ON admin.table2.this_code_id = admin.v_table1.this_code_id) AS a
ON meetings.codeid@index = a.procid
LEFT JOIN
(
SELECT admin.v_table1.descript AS descript0,
admin.v_table1.abbrevdescript AS abbrev0,
admin.v_table1.thiscode AS thiscode0,
admin.v_table1.this_code_id
FROM admin.v_table1) AS b
ON meetings.codeid@index = b.this_code_id
LEFT JOIN
(
SELECT patid AS id,
ee
FROM admin.customer) AS c
ON meetings.patid = c.id
LEFT JOIN
(
SELECT this_code_id AS redid,
eecategoryid,
amt
FROM admin.eeule) AS d
ON c.ee = d.eecategoryid
AND d.redid = b.this_code_id
LEFT JOIN
(
SELECT eecategoryid AS namefeecatid,
moneyname
FROM admin.eeulenames) AS e
ON d.eecategoryid = e.namefeecatid
LEFT JOIN (SELECT pi.customer_id,
pi.primarykk_id AS picid,
pi.primarykk_name,
pi.first_name,
pi.last_name,
i.groupname,
i.ee
FROM admin.v_pir AS pi
LEFT JOIN admin.money AS i
ON pi.primarykk_id = i.insid) AS
ins
ON ins.customer_id = c.id
LEFT JOIN (SELECT this_code_id AS redid,
eecategoryid,
amt
FROM admin.eeule) AS insfee
ON ins.ee = insfee.eecategoryid
AND insfee.redid = b.this_code_id
LEFT JOIN (SELECT eecategoryid AS namefeecatid,
moneyname
FROM admin.eeulenames) AS insname
ON insfee.eecategoryid = insname.namefeecatid
WHERE codeid@index >= 1
END WHILE;
END;
I have never used a while loop, and while I understand somewhat I am supposed to select this to go INTO something, do I need to create a temp table, or can it all just be stored in memory till the end of the loop and returned.
For what it is worth, the entire SELECT query works in C# when you replace the @index with concatenating format " . index . "
