CODEWITHSUNDEEP
sqlopenedgeprogress-db
modhorat
modhorat

Reputation: 51

SQL (in Progress Database) - How to use Where clause on calculated field

I'm pretty new to SQL, I've been using SQL on a Progress Database, I have the following code below:

Select pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col", Sum(pub."GL_Table"."Amount_Col"), ROUND((pub."GL_Table"."AccMnth_Col")/100,0) AS "Year", MOD(pub."GL_Table"."AccMnth_Col",100) As "Month"
FROM pub."GL_Table"
GROUP BY pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col"
ORDER BY pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col"

I want to only display only those rows with "Year" 2017

I have tried the below (but I receive errors):

Select * FROM (
Select pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col", Sum(pub."GL_Table"."Amount_Col"), ROUND((pub."GL_Table"."AccMnth_Col")/100,0) AS "Year", MOD(pub."GL_Table"."AccMnth_Col",100) As "Month"
FROM pub."GL_Table"
GROUP BY pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col"
ORDER BY pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col"
)
WHERE "Year" = 2017

Can anyone assist me?

Thanks in advance

Upvotes: 1

Views: 916

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I am guessing that you need column aliases and maybe table aliases. I am not intimately familiar with Progress, but the following seems more correct:

SELECT *
FROM (SELECT pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col",
             Sum(pub."GL_Table"."Amount_Col") as "Amount", 
             ROUND((pub."GL_Table"."AccMnth_Col")/100, 0) AS "Year", 
             MOD(pub."GL_Table"."AccMnth_Col", 100) As "Month"
      FROM pub."GL_Table"
      GROUP BY pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col"
      ORDER BY pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col"
     ) ym
WHERE "Year" = 2017

Upvotes: 0

Brooke Goldstein
Brooke Goldstein

Reputation: 86

Although a subquery is one approach, you can also reference the calculation in the where clause instead. 

Select pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col", Sum(pub."GL_Table"."Amount_Col"), ROUND((pub."GL_Table"."AccMnth_Col")/100,0) AS "Year", MOD(pub."GL_Table"."AccMnth_Col",100) As "Month"
FROM pub."GL_Table"
WHERE ROUND((pub."GL_Table"."AccMnth_Col")/100,0) = 2017
GROUP BY pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col"
ORDER BY pub."GL_Table"."AccNum_Col", pub."GL_Table"."AccMnth_Col"

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You are missing the subquery alias. And the order by clause should be applied outside the subquery.

select *
from (
    select "gm-accno",
        "gt-accmth",
        Sum("gt-amount"),
        ROUND(("gt-accmth") / 100, 0) as "Year",
        MOD("gt-accmth", 100) as "Month"
    from pub."glsytr"
    group by "gm-accno",
        "gt-accmth"
    ) t
where "Year" = 2017
order by "gm-accno",
    "gt-accmth"

Upvotes: 0

Related Questions