大家说下思路吧,提点意见吧……
CREATE PROCEDURE PROC_SALES_POSITION_GETMILL (
companyid integer,
ctrbookid integer)
returns (
contractbookid integer,
regionid integer,
regionname varchar(100),
millno integer,
firstivlocno integer,
millname1 varchar(100),
millname2 varchar(100),
millname3 varchar(100),
millid1 integer,
millid2 integer,
millid3 integer)
as
BEGIN contractbookid = :ctrbookid;
/* regionid = :region; */
FOR
SELECT
DISTINCT(JR.regionname),
JR.regionid
FROM JCIVLOC JL
JOIN JCREGION JR ON (JR.regionid = JL.regionid)
JOIN CTRBOOK CB ON (CB.mbrcompany = JL.mbrid)
WHERE CB.ctrbookid = :ctrbookid
AND JL.mbrid = :companyid
ORDER BY 1,2
INTO :regionname,
:regionid
DO
BEGIN
millname1 = null; /*Get Count of IVLOC*/
SELECT COUNT(JL.ivlocid)
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
INTO :millno; /*Get First Ivloc No*/
SELECT FIRST 1 JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
ORDER BY JL.longname
INTO :firstivlocno; WHILE(:millno > 0 )
DO
BEGIN
if(:millname1 IS NOT NULL)
then
begin
/*Get Mill Param1*/
/*Not first time into circle*/
SELECT
FIRST 1 JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname3
ORDER BY JL.longname
INTO
:millname1,
:millid1;
end
else
begin
/*First time into circle*/
SELECT
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
WHERE JL.ivlocid = :firstivlocno
INTO
:millname1,
:millid1;
end /*Get Mill Param2*/
SELECT
FIRST 1
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname1
ORDER BY JL.longname
INTO
:millname2,
:millid2; /*Get Mill Param3*/
SELECT
FIRST 1
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname2
ORDER BY JL.longname
INTO
:millname3,
:millid3; /*Chekc Part*/
if(:millno -3 < 0)
then
begin
millname3 = null;
end
if(:millno -2 < 0)
then
begin
millname2 = null;
end
if(:millno -1 < 0)
then
begin
millname1 = null;
end MILLNO = :millno - 3;
SUSPEND;
END
END
end 这个最近写的。不过只能到工厂级别。
问题是找到最多合同的工厂进行循环,循环的同时也必须给MILL2,MILL3赋合同CTRNO.
正常的循环是MILL1循环结束在进入下一个工厂的,即编程MILL1的CTR1有值,CTR2,CTR3为空,
然后MILL2的CTR2有值,CTR1,CTR3为空。MILL3相同,CTR1,CTR2为空。结果与需求不符……
CREATE PROCEDURE PROC_SALES_POSITION_GETMILL (
companyid integer,
ctrbookid integer)
returns (
contractbookid integer,
regionid integer,
regionname varchar(100),
millno integer,
firstivlocno integer,
millname1 varchar(100),
millname2 varchar(100),
millname3 varchar(100),
millid1 integer,
millid2 integer,
millid3 integer)
as
BEGIN contractbookid = :ctrbookid;
/* regionid = :region; */
FOR
SELECT
DISTINCT(JR.regionname),
JR.regionid
FROM JCIVLOC JL
JOIN JCREGION JR ON (JR.regionid = JL.regionid)
JOIN CTRBOOK CB ON (CB.mbrcompany = JL.mbrid)
WHERE CB.ctrbookid = :ctrbookid
AND JL.mbrid = :companyid
ORDER BY 1,2
INTO :regionname,
:regionid
DO
BEGIN
millname1 = null; /*Get Count of IVLOC*/
SELECT COUNT(JL.ivlocid)
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
INTO :millno; /*Get First Ivloc No*/
SELECT FIRST 1 JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
ORDER BY JL.longname
INTO :firstivlocno; WHILE(:millno > 0 )
DO
BEGIN
if(:millname1 IS NOT NULL)
then
begin
/*Get Mill Param1*/
/*Not first time into circle*/
SELECT
FIRST 1 JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname3
ORDER BY JL.longname
INTO
:millname1,
:millid1;
end
else
begin
/*First time into circle*/
SELECT
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
WHERE JL.ivlocid = :firstivlocno
INTO
:millname1,
:millid1;
end /*Get Mill Param2*/
SELECT
FIRST 1
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname1
ORDER BY JL.longname
INTO
:millname2,
:millid2; /*Get Mill Param3*/
SELECT
FIRST 1
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname2
ORDER BY JL.longname
INTO
:millname3,
:millid3; /*Chekc Part*/
if(:millno -3 < 0)
then
begin
millname3 = null;
end
if(:millno -2 < 0)
then
begin
millname2 = null;
end
if(:millno -1 < 0)
then
begin
millname1 = null;
end MILLNO = :millno - 3;
SUSPEND;
END
END
end 这个最近写的。不过只能到工厂级别。
问题是找到最多合同的工厂进行循环,循环的同时也必须给MILL2,MILL3赋合同CTRNO.
正常的循环是MILL1循环结束在进入下一个工厂的,即编程MILL1的CTR1有值,CTR2,CTR3为空,
然后MILL2的CTR2有值,CTR1,CTR3为空。MILL3相同,CTR1,CTR2为空。结果与需求不符……
2 TEST2
3 TEST3
4 TEST4
5 TEST5
6 TEST6
7 TEST7
8 TEST8
9 TEST9
10 TEST10 TABLE MILLTEST: CTRID MILLID REGIONID MILLNAME 1 1 1 MILL1
2 1 1 MILL1
3 2 1 MILL2
4 2 1 MILL2
5 2 1 MILL2
6 2 1 MILL2
7 3 1 MILL3
8 3 1 MILL3
9 3 1 MILL3
10 4 1 MILL4 TABLE REGIONTEST: REGIONID REGIONNAME 1 REGION
希望得到的效果: COMMODITYID REGIONNAME MILL1 MILL2 MILL3 CTRREF1 CTRREF2 CTRREF3 COMMODITY REGION1 MILL1 MILL2 MILL3 CTR1 CTR2 CTR3
COMMODITY REGION1 MILL1 MILL2 MILL3 CTR4 CTR5 CTR6
COMMODITY REGION1 MILL1 MILL2 MILL3 CTR7 CTR8
COMMODITY REGION1 MILL1 MILL2 MILL3 CTR9
COMMODITY REGION1 MILL4 MILL5 MILL6 CTR10 CTR11
COMMODITY REGION1 MILL4 MILL5 MILL6 CTR12
谢谢~
1.表结构与测试数据:
create table tb(...)
insert tb ...
2.相关算法:
主要描述一下各表间的关系,以及是怎么得到你想要的结果的.
3.最终得到的结果:
贴出最终得到的结果.