What is wrong with this SQL?

[mxd198]

I am trying to create the column that is adding 2 other columns together, but it says that I have an invalid identifier "Amt_PD_MTD" Can someone tell me why this total column won't work. This select works with Access, but in PL/SQL which is what I am pasting now, won't work.
Please Help!!
THe commented lines are what I need to work and it isn't....

SELECT MEMB.MEMB_APPEAL_CODE, Count(MEMB_ENTITY.MEMB_NUMBER) AS AI, MEMB.MEMB_TYPE_CODE,

NVL((SELECT Sum(m.MEMB_AMT_PAID) AS AmtPdMTD
FROM MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER
WHERE to_char(m.start_date,'MM') || to_char(m.start_date,'YYYY') = to_char(sysdate,'MM')
|| (to_char(sysdate,'YYYY')-1 )
and m.memb_appeal_code = 'AGS' and m.memb_type_code = 'AI'),0) as AMT_PD_MTD,

NVL((SELECT Sum(m.MEMB_AMT_PAID) AS AmtPdYTD
FROM MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER
WHERE m.MEMB_APPEAL_CODE = 'AGS'
AND m.MEMB_TYPE_CODE = 'AI'
AND to_char(m.start_date, 'YYYY') = (case when to_char(m.start_date,'MM') < 7 then
to_char(sysdate,'YYYY')-1
else to_char(sysdate,'YYYY')+1
end)),0) as AMT_PD_FY_YTD,

--AMT_PD_FY_YTD+AMT_PD_MTD as Amt_Pd_Total,

NVL((SELECT Sum(m.MEMB_AMT_PAID) AS AmtDueYTD
FROM MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER
INNER JOIN MEMB_PAY_SCHEDULE ON m.MEMB_NUMBER = MEMB_PAY_SCHEDULE.MEMB_NUMBER
WHERE m.MEMB_APPEAL_CODE = 'AGS'
AND m.MEMB_TYPE_CODE = 'AI'
AND MEMB_PAY_SCHEDULE.SCHED_STATUS_CODE = 'U'
AND to_char(m.start_date, 'YYYY') =
(case when to_char(m.start_date,'MM') < 7 then
to_char(sysdate,'YYYY')-1
else to_char(sysdate,'YYYY')+1
end)),0) AS Amt_Due_FYTD,

NVL((SELECT Sum(m.MEMB_AMT_PAID) AS AmtPdYTD
FROM (MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER)
INNER JOIN MEMB_PAY_SCHEDULE ON m.MEMB_NUMBER = MEMB_PAY_SCHEDULE.MEMB_NUMBER
WHERE m.MEMB_APPEAL_CODE = 'AGS'
AND m.MEMB_TYPE_CODE = 'AI'
AND MEMB_PAY_SCHEDULE.SCHED_STATUS_CODE = 'U'),0) AS Amt_Due_Total



--[Amt_Pd_FY_YTD]+[Amt_Due_FYTD] AS Total_FY_YTD,

--[Amt_Pd_Total]+[Amt_Due_Total] AS OverAllTotal

FROM MEMB INNER JOIN MEMB_ENTITY ON MEMB.MEMB_NUMBER = MEMB_ENTITY.MEMB_NUMBER
GROUP BY MEMB.MEMB_APPEAL_CODE, MEMB.MEMB_TYPE_CODE
HAVING (((MEMB.MEMB_APPEAL_CODE)='AGS')
AND ((MEMB.MEMB_TYPE_CODE)='AI'))

[jp140768]

I wonder if the problem is actually with Amt_PD_MTD? If you select this field from the database, what value do you get? Does the problem occur for all records or only some?

I think in SQL Server, you would get problems with your addition of AMT_PD_FY_YTD because this is a value you have created in your Select statement, not sure how it works in PL/SQL though.

How do you create variables in PL/SQL via a select statement, in SQL Server you say Select variable = fld from table.

Sorry I can't be of more help - if any at all.

[cjard]

Aye carumba.. is that Oracle SQL? That's hideous! Gimme 10 minutes to work out what it's doing and rewrite it..

[cjard]

I cant work out enough from the query alone to do it; the query reuses terms over and over again for different meanings. Perhaps you can tell me the table schema, give me some example data, and tell me what you want on your report?

[cjard]

as an example, here's a transliteration of some of that SQL you gave:


SELECT
m.MEMB_APPEAL_CODE,
m.MEMB_TYPE_CODE,
Count(me.MEMB_NUMBER) AS AI,
SUM( CASE WHEN trunc(m.start_date, 'MONTH') = trunc(add_months(sysdate, -12), 'MONTH') THEN m.MEMB_AMT_PAID ELSE 0 END ) as AMT_PD_MTD,
SUM(
CASE
WHEN round(m.start_date, 'YEAR') =
CASE
WHEN (EXTRACT MONTH FROM m.START_DATE) < 7 THEN
trunc(add_months(sysdate, -12), 'YEAR')
ELSE
trunc(add_months(sysdate, 12), 'YEAR')
END
THEN
m.MEMB_AMT_PAID
ELSE
0
END
) as AMT_PD_FY_YTD

FROM
MEMB m
INNER JOIN
MEMB_ENTITY me
USING
MEMB_NUMBER

INNER JOIN
MEMB_PAY_SCHEDULE mps
USING
MEMB_NUMBER

WHERE
m.MEMB_APPEAL_CODE = 'AGS' AND
m.MEMB_TYPE_CODE = 'AI' AND
m.SCHED_STATUS_CODE = 'U'

GROUP BY
m.MEMB_APPEAL_CODE,
m.MEMB_TYPE_CODE


Those "if the member start month is less than 7 then go back a year else go forward a year and compare their start year with that year..."
euww.. Im sure there is a better way to achieve this with rounding the years (years round up on july 1st) or.. i dont know.. something! ends up, we can do this in one table scan im sure, once we know what we are doing..

so, what are we doing? :D