DSXchange

Posted: **Fri Apr 12, 2013 10:50 am**

Hi,

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?

Posted: **Fri Apr 12, 2013 11:16 am**

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

Posted: **Fri Apr 12, 2013 1:50 pm**

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?

Posted: **Fri Apr 12, 2013 2:45 pm**

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.

Posted: **Sun Apr 14, 2013 8:22 am**

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?

Posted: **Sun Apr 14, 2013 9:29 am**

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.

DSXchange

calculate daily,hourly basis data?

calculate daily,hourly basis data?

Re: calculate daily,hourly basis data?