sql query to format row to column

Consider these two tables,


Table:1
LOAN_NO      REASON_CODE
 100                        A
 100                        B
 200                       A
 300                       A
 300                       B

Table:2

LOAN_NO  REASON_CODE DESCRIPTION
  100                        A                    XYZ
  100                        A                    YSN
  100                        A                    dfsdf
  100                        B                     HJBNDJ

Expected result:

LOAN_NO      DESCRIPTION_A     DESCRIPTION_B
 100                           XYZ                         HJBNDJ
 100                           YSN                       
 100                          dfsdf

Here in the table 1, reason code A or B and both will be inserted only once.
Table 2, will have any number of descriptions under 'A' followed by 'B' inserted row by row.

In the expected resultset, we want all the A descriptions and B descriptions to be displayed parallelly to each other , like may A has 10 descriptions and B has only 2 or vice versa. Here instead of telling A and B, we are using lender error message and DU error message.

SELECT

LOAN.LOAN_NO, /*Arrangement*/
LOAN.CREATE_DT, /*AUS Submitted Date*/
CHANNEL.TAKEN_CD, /*Channel type*/
LOAN_ADDL.PROP_SELECTED_YN, /*Application type*/
DECODE(TEMP.ROW_NO,TEMP.DU_ROW_NO,TEMP.DU_EMSG)DU_ERROR_MSG, /*DU Error message*/
DECODE(TEMP.ROW_NO,TEMP.LENDER_ROW_NO,TEMP.LENDER_EMSG)LENDER_ERROR_MSG /*Lender Error message*/


FROM


/*Inline view contains the logic to obtain the resultset of DU Error message and Lender Error Message parallel to each other for each an arrangement */

(SELECT
NVL(DU.LOAN_NUMBER,LENDER.LOAN_NUMBER)TEMP_LOAN_NO,
DU.AUS_ERROR_MSG DU_EMSG,
LENDER.AUS_ERROR_MSG LENDER_EMSG,
DU.DU_ROW_NO,LENDER.LENDER_ROW_NO,
/*Function to join the DU and Lender Error messages each other based on row number that obtain from the child inline views */
ROW_NUMBER()OVER(PARTITION BY NVL(DU.LOAN_NUMBER,LENDER.LOAN_NUMBER)ORDER BY NVL(DU.LOAN_NUMBER,LENDER.LOAN_NUMBER),DU.DU_ROW_NO,LENDER.LENDER_ROW_NO)ROW_NO


FROM

/*Child inline view for DU Error messages*/
(SELECT
LOAN_ERROR.AUS_ERROR_MSG,
LOAN_AUS.LOAN_AUS_LOG_ID,
LOAN_AUS.LOAN_NUMBER ,
/*Function to assigning row number to the error messages */
ROW_NUMBER()OVER(PARTITION BY LOAN_AUS.LOAN_NUMBER
ORDER BY LOAN_AUS.LOAN_NUMBER,LOAN_ERROR.AUS_ERROR_MSG)DU_ROW_NO
FROM LOAN_AUS_LOG LOAN_AUS,LOAN_AUS_ERROR_LOG LOAN_ERROR
WHERE LOAN_ERROR.LOAN_AUS_LOG_ID= LOAN_AUS.LOAN_AUS_LOG_ID
AND LOAN_AUS.AUS_CODE='DU'
)DU


FULL OUTER JOIN


/*Child inline view for Lender Error messages*/
(SELECT
LOAN_ERROR.AUS_ERROR_MSG,
LOAN_AUS.LOAN_AUS_LOG_ID,
LOAN_AUS.LOAN_NUMBER ,
/*Function to assigning row number to the error messages */
ROW_NUMBER()OVER(PARTITION BY LOAN_AUS.LOAN_NUMBER
ORDER BY LOAN_AUS.LOAN_NUMBER,LOAN_ERROR.AUS_ERROR_MSG)LENDER_ROW_NO
FROM LOAN_AUS_LOG LOAN_AUS,LOAN_AUS_ERROR_LOG LOAN_ERROR
WHERE LOAN_ERROR.LOAN_AUS_LOG_ID= LOAN_AUS.LOAN_AUS_LOG_ID
AND LOAN_AUS.AUS_CODE='LENDER'
)LENDER


ON DU.DU_ROW_NO=LENDER.LENDER_ROW_NO AND DU.LOAN_NUMBER=LENDER.LOAN_NUMBER)TEMP ,
LOANS LOAN,
MISC1003 CHANNEL,
PT_T_LOANSADDL LOAN_ADDL


WHERE
LOAN.LOAN_NO= LOAN_ADDL.LOAN_NO
AND LOAN_ADDL.LOAN_NO=CHANNEL.LOAN_NO
AND CHANNEL.LOAN_NO=TEMP.TEMP_LOAN_NO
AND LOAN_ADDL.PROP_SELECTED_YN IN ('Y','N') /*Full Application, CCB Arrangements */
AND CHANNEL.TAKEN_CD IN ('I','T') /*Channel, I-Internet, T-Telephone*/
AND TRUNC (LOAN.CREATE_DT) BETWEEN TRUNC(to_date('2010/03/20','yyyy/mm/dd')) AND TRUNC(to_date('2010/03/20','yyyy/mm/dd'))

Some behavior of the crystal reports

Here are some of the behaviors of the crystal reports that can not be done by using it.

1.There is no option to display all the dates between the date ranges that passed through the parameters
2.If data is not available Bar chart will be empty, even axis will not be populated.
3.Cross tab reports is good choice for displaying categorized counts as vertical columns, but if any of the category is not present for a given time period, it will suppress the entire column even not leave a empty column and records marked as zero
For a case, we want how many applications received through internet and post .
we can get it as

Date       Internet      Post
01/01           5                2
02/01           3                6
03/01           8               12
04/01           6               10

if none of the dates having count under Post, the whole column will not be printed even the header
like this,
Date   Internet

01/01       5

02/01       3
03/01       8
04/01       6
4. Business objects enterprise repository server, There is an option to schedule the reports when to run(i mean the day,weekly, monthly), but there is no option for what date limts it has to fetch automatically.

That is weekly reports i can schedule to run on every sunday, but i can't specify the dates that monday to saturday data's it has to fetch.

Oracle query to display all the dates in a month or date ranges

In some requirements we have to show all the dates between the date range though a particular date entry is not  present in the DB. We can not acheive it in the crystal report straight forwardly. The only the way is writting query to bring all the dates in the data ranges and make left outer join with your core resultset by joining the dates.

For an example we need to find day wise count of particular entity,

SELECT
         TEMP.TEMP_DATE,
         (
           SELECT COUNT(ENTITY.ID) FROM
                (SELECT ENTITY.ID ....... YOUR CORE CONDITIONS ) INLINEVIEW  
           WHERE INLINEVIEW.DATE(+)=TEMP.TEMP_DATE
        )ENTITY_COUNT
FROM
  ( SELECT ({?FROMDATE}-1)+ LEVEL TEMP_DATE
     FROM DUAL CONNECT BY LEVEL <= ({?TODATE})-({FROMDATE}-1)  )TEMP
 GROUP BY TEMP.TEMP_DATE
 ORDER BY TEMP.TEMP_DATE

Crystal reports: Can we use two different queries in a report

We cannot use two queries in a report unless we make a join between those two queries.
In this case crystal reports will make cartesian product as that of the database makes cartesian product while using two tables without having any join between them.

Solution to use two different queries in a report:
                                                                       We can acheive it by an option called Sub-report.
Sub report is a report which is created normally as any other report, and it can be included in the calling report.once inserted (using insert option) the sub report will stay inside the main report though we delete the actual report which is about to be inserted as sub report and can be treated single report.

Crystal Reports Monthly Reports

Monthly Reports:


Monthly reports are the reports which are scheduled in the server to run monthly once and pulls the previous month data's. In crystal reports server, we can schedule the reports, when to run (i mean the day of the month), but there is no option to specify on which days(from and to dates) it has to show the data.

ORACLE Query:
To get the date of the last month's first day.
 TO_DATE( (ADD_MONTHS (TRUNC (SYSDATE,'MM'),-1) ) ) )
To get the date of the last month's last day.
 TO_DATE( (ADD_MONTHS (TRUNC (SYSDATE,'MM'),0)-1 ) ) )

Crystal reports Formula for last month first date and last date:
Formula to get the date of the last month's first day.
DateAdd('m',-1,CurrentDate-day(CurrentDate)+1)-1+1
Formula to get the date of the last month's last day.
DateAdd('m',0,CurrentDate-day(CurrentDate)+1)-1

Crystal Reports daily reports

Daily Reports:
                     Daily reports are the reports which are scheduled in the server to run on the daily basis and pulls the previous date data's. In crystal reports server, we can schedule the reports, when to run (i mean the time of the day), but there is no option to specify for which day it has to show the data.

We can acheive it by embedding the logic in the query itself, In the sql queries we can add filters like "where employnment_date between sysdate-1 and sysdate-1". it queries for the previous date data's.

To show the previous date range in the reports, we can use this formula:
DataDate-1(It returns the previous date to the current system date).

Here we are using DataDate, because it keeps the last ran date of the report with the report in the server. This is useful if we are scheduling our reports to run daily that fetches data for a day previous.
for an example if today you want to see the report for 01/01/1996, u can get it from the history of the scheduled report in the server. If you use another date formula to address this CurrentDate-1. it will be parsed each time you open the report i-e: if you open a report for 01/01/1996, it will show the data of the CurrentDate though it ran many years ago.