quote:

---

Originally posted by kbrizendine

I have a situation where my SQL works everywhere else but my COBOL compiler complains wherever I use PARTITION BY. I can't find a workaround for that problem so I would like to remove all the PARTITION BYs. I'm not confident that I can do this accurately and would like some help getting started.

Here is my simplest example:

SELECT FESOR.REGION, FESOR.TYPE,
COUNT(*) OVER (PARTITION BY FESOR.REGION, FESOR.TYPE) FROM FESOR, FR
where FESOR.phase = 'Ref'
and FESOR.assign is null
and FESOR.comp_date is null
and FESOR.region = FR.REGION
and FESOR.type = FR.TYPE
and FR.REP_ROW='A'
GROUP BY FESOR.REGION, FESOR.TYPE

What I'm looking for is a modified version of the SQL above which returns the same result set without using PARTITION BY.

Thanks in advance for your assistance.

---

quote:

---

Originally posted by Peso

You can use a correlated subquery instead and see performance go down the drain.

---

quote:

---

Originally posted by visakh16


I really cant understand the significance of PARTITION BY here since you're already grouping on the region & type.

---

quote:

---

Originally posted by kbrizendine

quote:

---

Originally posted by Peso

You can use a correlated subquery instead and see performance go down the drain.

---

Could you indulge me with the modified SQL?

---

SELECT 
	FESOR.REGION, 
	FESOR.TYPE,
	COUNT(*)
FROM 
	FESOR, 
INNER JOIN
	FR 
	ON FESOR.region = FR.REGION 
	AND FESOR.type = FR.TYPE 
where 
	FESOR.phase = 'Ref' 
	and FESOR.assign is null 
	and FESOR.comp_date is null 
	and FR.REP_ROW='A'
GROUP BY 
	FESOR.REGION, 
	FESOR.TYPE 

quote:

---

Originally posted by Lamprey

So is this query dynamic sql? I'm assuming that is the case since it is a COBOL compiler complaining. At anyratem I don't see why you need the OVER clause anyway:

SELECT 
	FESOR.REGION, 
	FESOR.TYPE,
	COUNT(*)
FROM 
	FESOR, 
INNER JOIN
	FR 
	ON FESOR.region = FR.REGION 
	AND FESOR.type = FR.TYPE 
where 
	FESOR.phase = 'Ref' 
	and FESOR.assign is null 
	and FESOR.comp_date is null 
	and FR.REP_ROW='A'
GROUP BY 
	FESOR.REGION, 
	FESOR.TYPE 

---

SELECT 
	FESOR.REGION, 
	FESOR.TYPE,
	1
FROM 
	FESOR, 
INNER JOIN
	FR 
	ON FESOR.region = FR.REGION 
	AND FESOR.type = FR.TYPE 
where 
	FESOR.phase = 'Ref' 
	and FESOR.assign is null 
	and FESOR.comp_date is null 
	and FR.REP_ROW='A'
GROUP BY 
	FESOR.REGION, 
	FESOR.TYPE

quote:

---

Originally posted by Lamprey

Ahh try this:

SELECT 
	FESOR.REGION, 
	FESOR.TYPE,
	1
FROM 
	FESOR, 
INNER JOIN
	FR 
	ON FESOR.region = FR.REGION 
	AND FESOR.type = FR.TYPE 
where 
	FESOR.phase = 'Ref' 
	and FESOR.assign is null 
	and FESOR.comp_date is null 
	and FR.REP_ROW='A'
GROUP BY 
	FESOR.REGION, 
	FESOR.TYPE

---

quote:

---

Originally posted by Peso

Why not? It produces the resultset you wanted.

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by kbrizendine
Lol - I hope you're joking because otherwise you must think I'm an idiot.

---

quote:

---

Originally posted by kbrizendine
Lol - I hope you're joking because otherwise you must think I'm an idiot.

---

DECLARE @Foo TABLE (ID INT, Val INT)

INSERT @Foo
SELECT 1, 2
UNION ALL SELECT 1, 2
UNION ALL SELECT 2, 3
UNION ALL SELECT 3, 4

SELECT
	ID,
	Val,
	COUNT(*) OVER (PARTITION BY ID, Val) aS MyCount
FROM
	@Foo
GROUP BY
	ID,
	Val

-- Results
ID          Val         MyCount
----------- ----------- -----------
1           2           1
2           3           1
3           4           1

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by kbrizendine
Lol - I hope you're joking because otherwise you must think I'm an idiot.

---

I don't think your an idiot. I just don't think you understand the SQL you have posted.

If you examine your origial query you will see that you are grouping by two columns FESOR.REGION and FESOR.TYPE. So trying to get a COUNT using the OVER operater by PARTITIONING on FESOR.REGION and FESOR.TYPE will always result in a COUNT of 1.

So, again, I don't see why you need to use a COUNT since the value is always 1. Here is a sample of what happens:

DECLARE @Foo TABLE (ID INT, Val INT)

INSERT @Foo
SELECT 1, 2
UNION ALL SELECT 1, 2
UNION ALL SELECT 2, 3
UNION ALL SELECT 3, 4

SELECT
	ID,
	Val,
	COUNT(*) OVER (PARTITION BY ID, Val) aS MyCount
FROM
	@Foo
GROUP BY
	ID,
	Val

-- Results
ID          Val         MyCount
----------- ----------- -----------
1           2           1
2           3           1
3           4           1

---

quote:

---

Originally posted by Peso

Why not? It produces the resultset you wanted.

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by kbrizendine

Do you agree?

---

quote:

---

Originally posted by Peso

quote:

---

Originally posted by kbrizendine

Do you agree?

---

No. You can make correlated subqueries on composite keys too.

---

E 12°55'05.25"
N 56°04'39.16"

---