How to create a view in PostgreSQL with where clause

Question

I am trying to create a view in Postgres

I have 3-4 time stamps in each closing_date where I need to select only the latest time stamp of each day
And also I have to restrict the closing_date to only 30 days (shown in SQL query below)

Below is the query from SQL data which I had created

CREATE VIEW dbo.CashBreaks_30Days_View as
SELECT Closing_date,Bo,Desk,Breaks_Staus,Owner,status,Team,
SLA,Age_Bucket_EntryDate,Age_Bucket_ValueDate,Age_EntryDate,Age_ValueDate,
[Type_(2)]
FROM Master_Data_CashBreaks
WHERE Closing_date >= cast(getdate()-37 as date);

Oracle SQL Developer is intended for Oracle only (and to support migrations to Oracle). It's not a general DBMS independent tool that can be used for other products (and surely not easily for Postgres). If you are really using Oracle's SQL Developer I strongly suggest you look for a different SQL GUI client — user330315
– user330315, Commented Apr 14, 2020 at 11:46
I suggest you create a new question for the "latest time stamp of each day" as that will require more information that this question contains — user330315
– user330315, Commented Apr 14, 2020 at 11:54

Littlefoot · Accepted Answer · 2020-04-13 16:20:01Z

0

If I understood you correctly, something like this might return result you want:

create or replace view cash_breaks_30days_view
as
select a.list_of_columns
from master_data_cashbreaks a
where a.closing_date >= trunc(sysdate) - 30             --> the last 30 days
  and a.closing_date = (select max(b.closing_date)      --> subquery is used to return
                        from master_data_cashbreaks b   --  the last timestamp per date
                        where b.id = a.id
                          and trunc(b.closing_date) = trunc(a.closing_date)
                       )

answered Apr 13, 2020 at 16:20

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kiran Over a year ago

Hi i am getting below error "Error report - ERROR: column "sysdate" does not exist Position: 435"

Littlefoot Over a year ago

SYSDATE is a function, it is not a column, and it exists in every Oracle database, available to all users. Error you specify doesn't look like Oracle message. Are you sure you use Oracle?

Kiran Over a year ago

Hi i am trying to create view in Oracle sql Developer.

Littlefoot Over a year ago

That is a tool. I asked about database.

Littlefoot Over a year ago

I don't know whether you're wrong or not, but - you should know which database you use. If it is PostreSQL, then I suggest you fix tags - remove Oracle and add PostgreSQL. Because, the way it is now, people who speak Oracle are answering your question.

Popeye · Accepted Answer · 2020-04-13 16:29:30Z

0

There are few mistakes in your code:

[Type_(2)] - Not a valid SQL
getdate() - There is no such function available in Oracle. you should use the trunc(SYSDATE) instead.
getdate()-37 - Why -37, when you want the last 30 days data. It should be 30.

Your query should look like this in oracle:

CREATE VIEW dbo.CashBreaks_30Days_View as
SELECT * FROM
  (SELECT Closing_date,Bo,Desk,Breaks_Staus,Owner,status,Team,
          SLA,Age_Bucket_EntryDate,Age_Bucket_ValueDate,Age_EntryDate,Age_ValueDate,
          ROW_NUMBER() OVER (PARTITION BY Closing_date ORDER BY Closing_date DESC) AS RN
     FROM Master_Data_CashBreaks
    WHERE Closing_date >= TRUNC(SYSDATE) - 30)
WHERE RN = 1;

answered Apr 13, 2020 at 16:29

Popeye

36k4 gold badges12 silver badges31 bronze badges

2 Comments

Kiran Over a year ago

Hi Tejash, i had given SQL query as an example does your above corrected query satisfies my time stamps on closing date issue

Kiran Over a year ago

Hi Tejash Error starting at line : 1 in command - CREATE VIEW cashbreaks_30days_view_latesttime as SELECT closing_date,age_entrydate,age_valuedate,age_bucket_entrydate,age_bucket_valuedate,owner,team,currency_code, ROW_NUMBER() OVER (PARTITION BY Closing_date ORDER BY Closing_date DESC) AS RN FROM Master_Data_CashBreaks WHERE Closing_date >= TRUNC(SYSDATE) - 30 WHERE RN = 1 Error report - ERROR: syntax error at or near "WHERE" Position: 541

user330315 · Accepted Answer · 2020-04-14 11:53:50Z

0

Your SQL contains a lot of errors

square brackets are invalid in SQL identifiers, if you have such a column you need to use double quotes. It's unclear to me if your column is named "[Type_(2)]" or maybe just `"Type_(2)"
There is no getdate() in SQL or in Postgres. Use current_date instead

So fixing all those error, your statement should look like this:

CREATE VIEW dbo.CashBreaks_30Days_View 
as
SELECT Closing_date, Bo, Desk, Breaks_Staus, Owner, status,
       Team, SLA, Age_Bucket_EntryDate, 
       Age_Bucket_ValueDate, Age_EntryDate, Age_ValueDate,
       "[Type_(2)]" -- or maybe only "Type_(2)"
FROM Master_Data_CashBreaks
WHERE Closing_date >= current_cate - 30;

answered Apr 14, 2020 at 11:53

user330315

Comments

Kiran · Accepted Answer · 2020-04-14 13:03:06Z

0

i am able to create with the below

create or replace view cashbreaks_30days_view_latesttime as select a. Column details

from master_data a where a. Closing_date >= NOW() - interval '40 days' and a.closing_date = (select max(b.closing_date)
from master_data b
where date(b.closing_date) = date(a.closing_date));

answered Apr 14, 2020 at 13:03

Kiran

111 bronze badge

Collectives™ on Stack Overflow

How to create a view in PostgreSQL with where clause

4 Answers 4

5 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related