calculate daily,hourly basis data?

[A_SUSHMA]

Hi,

Code: Select all

select PS_DATE,PS_HOUR,SUBSCRIBER_ID,SUBSCRIBER_TYPE,MESSAGE_TYPE,SUBTYPE_ID,
CIRCLE_ID,SP_ID,SUBSCRIBER_ID,BALANCE_ID,
sum(CHANGE_AMOUNT)CHANGE_AMOUNT,count (*)no_of_SMS from
(select VARCHAR_FORMAT(DATE_time ,'DD-MM-YYYY')PS_DATE
,VARCHAR_FORMAT(DATE_time,'HH24')PS_HOUR        
,SUBSCRIBER_TYPE,MESSAGE_TYPE,SUBTYPE_ID,        CIRCLE_ID,SP_ID,SUBSCRIBER_ID,BALANCE_ID,
CHANGE_AMOUNT
from INUSG.PS_TRANSACTION_DETAILS_JAN2013
where SUBSCRIBER_ID = '0782670472'
)
group by
PS_DATE,PS_HOUR,MESSAGE_TYPE,SUBSCRIBER_ID,SUBSCRIBER_TYPE,SUBTYPE_ID,CIRCLE_ID,
SP_ID,SUBSCRIBER_ID,BALANCE_ID

I am new(6 months exp) in datastage,previously i have work on cognos.In the above query i want daily(PS_DATE),hourly(PS_HOUR) data By using datastage.
Please help me?

[A_SUSHMA]

My input data is

Code: Select all

MESSAGE_TYPE,SUBSCRIBER_ID,SUBSCRIBER_TYPE,SUBTYPE_ID,CIRCLE_ID,
SP_ID,SUBSCRIBER_ID,BALANCE_ID,CHANGE_AMOUNT

I want o/p is

Code: Select all

PS_DATE,PS_HOUR,SUBSCRIBER_ID,SUBSCRIBER_TYPE,MESSAGE_TYPE,SUBTYPE_ID,
CIRCLE_ID,SP_ID,BALANCE_ID,sum(CHANGE_AMOUNT)CHANGE_AMOUNT,count (*)no_of_SMS

[priyadarshikunal]

Welcome aboard.

Where are you really trying to achieve this, you already have a query to get that.

Apart from that, what have you tried?

[ray.wurlod]

Add constraints on PS_TIME (and maybe PS_DATE) with the constants in the WHERE clause being job parameters. For example:

Code: Select all

WHERE PS_TIME > '#jpMinTime#' AND PS_TIME <= '#jpMaxTime#'

If you need a period to run through midnight, construct a "timestamp" in the SELECT query and filter on that.

[A_SUSHMA]

Where i have to use above query in datastage oracle connector and how can i define columns PS_DATE,PS_HOUR in columns tab and how can i calculate these two columns?

[chulett]

The connectors are fully documented. As noted in the Connectivity Guide for Oracle Databases pdf:

1. From the parallel canvas, double-click the Oracle connector icon, and then select the output link to edit
2. Set Read mode to Select
3. Set Generate SQL at runtime to No, and then specify the SELECT statement in the Select statement property

When reading, you only need columns defined in the stage if you need to SELECT them. If they're just in the where clause then you don't need to add them anywhere. Ray is suggesting you set them up as Job Parameters and then reference them (hence the hash signs) in the SQL. Do that if you need to constrain the output to a specific date and/or time.

Otherwise just use the query you have if that's working for you. Hard for me to tell from your post right now.