Create a view for report with column split in two

[lana123]

Hi, people.
I wrote a view for my report (Version 9.2.2;Oracle database):
CREATE OR REPLACE VIEW RPT_VIEW1 (RECORD_FK, OPENED_DATE, CLOSED_DATE) AS (
Select RECORD_FK,
DECODE (STATUS_CODE,'OPEN', STATUS_START_DATE) AS OPENED_DATE,
DECODE (STATUS_CODE,'CLOSED', STATUS_START_DATE) AS CLOSED_DATE
From STATUS_TABLE)
Everything looks fine, exept one thing: I get two records instead of one: first with OPENED_DATE (CLOSED is empty), another- with CLOSED_DATE (OPEN is empty) with the same RECORD_FK.
I'll appreciate if somebody shows me how to update my view to get only one record (with OPEN and CLOSED DATE accordingly)
Thanks in advance.
Lana

 

[synapsevampire]

Your code looks fine, the table might be your culprit.

Keep in mind that you should be using the CR supplied ODBC driver, NOT Oracles.

The following should work:

CREATE OR REPLACE VIEW RPT_VIEW1 AS (
Select RECORD_FK,
DECODE (STATUS_CODE,'OPEN', STATUS_START_DATE) AS OPENED_DATE,
DECODE (STATUS_CODE,'CLOSED', STATUS_START_DATE) AS CLOSED_DATE
From STATUS_TABLE)

-k

 

[IanWaterman]

Try

CREATE OR REPLACE VIEW RPT_VIEW1 (RECORD_FK, OPENED_DATE, CLOSED_DATE) AS (
Select RECORD_FK,
max(DECODE (STATUS_CODE,'OPEN', STATUS_START_DATE)) AS OPENED_DATE,
Max(DECODE (STATUS_CODE,'CLOSED', STATUS_START_DATE)) AS CLOSED_DATE
From STATUS_TABLE)
group by RECORD_FK

Ian Waterman
UK Crystal Consultant