Grouping the records of DS_AUDIT

neeraj · Post by **neeraj** » Tue Feb 10, 2009 6:06 am

Hello All,

when I am running the below mentioned query to fetch the records from DS_AUDIT table

SELECT MAX(DTM),DTM,REASON FROM DS_AUDIT WHERE INSTANCE='test1' GROUP BY MODIFIER,REASON

I am getting below mentioned error
DataStage/SQL: GROUP BY columns must be single valued

My Requirement is as below

After executing below mentioned query
>SELECT DTM,DTM,REASON FROM DS_AUDIT WHERE INSTANCE='test'1
I am getting the below mentioned records

Modified on......... Modified by......... Reason for modification...............
2008-11-21 12:50:41 User1 Imported on 2008-11-21 12:49:04
2008-12-05 11:49:40 User1 Designer edit
2008-12-05 11:49:40 User2 Designer edit
2008-12-15 06:35:53 User2 Designer edit
2008-12-15 06:35:54 User2 Designer edit
2008-12-15 06:35:54 User2 Designer edit
2008-12-15 07:39:12 User3 Designer edit
2008-12-15 07:39:13 User3 Designer edit
2008-12-15 07:39:13 User3 Designer edit

But I want to group the record on modified by and reason and want to fetch the last modification done by a particular user

The result should look like:-
Modified on......... Modified by......... Reason for modification...............
2008-11-21 12:50:41 User1 Imported on 2008-11-21 12:49:04
2008-12-05 11:49:40 User1 Designer edit
2008-12-15 06:35:54 User2 Designer edit
2008-12-15 07:39:13 User3 Designer edit

Please let me know how can I get the output.

Regards
Neeraj

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Feb 10, 2009 7:06 am

Both DTM and REASON are multi-valued. You see a flattened view.

chulett · Post by **chulett** » Tue Feb 10, 2009 8:42 am

In other words, search the forums for UNNEST to find the syntax that will help.

kduke · Post by **kduke** » Tue Feb 10, 2009 9:50 am

If I was you then I would exact this information and do the group by in your target database. UNNEST is very important in multivalued hashed files. Hard to explain but trust Craig.

neeraj · Post by **neeraj** » Wed Feb 11, 2009 11:42 pm

Hello,

Thanks for the help. I have got the records as per my requirement.

If I am executing below mentioned query
select max(dtm) from unnest ds_audit on mods where instance='JOB1' group by modifier,reason

I am getting below mentioned records
MAX ( DTM )......... Modified by......... Reason for modification...............

2008-11-21 12:50:41 User1 Imported on 2008-11-21 12:49:04
2008-12-24 07:11:59 User2 Designer edit
2008-12-24 13:19:00 User3 Designer edit
2008-12-15 08:43:31 User4 Designer edit

Here the output is a bit wrong, If the user2 has modified the job on 2 different dates, I need 2 records for that. I believe I need to use DTM in the group by clause. But in group by clause, I have to use DTM as yyyy-mm-dd instead of 'yyyy-mm dd HH:MI:SS'

Please tell me how can I do it. I tried OCONV and ICONV. but it is throwing an error.

And Second question is:-

Can I use the In Clause to fetch the other information from the DS_AUDIT table?

Please valudate the query mesntioned below. I am not getitng epected records. Instead of getting the matching record, I am getting all the records avaliable in the DS_AUdit table.

select instance,dtc,creator,prevdtd,prevdeletor,dtm,modifier,reason from ds_audit where dtm in (select max(dtm) from unnest ds_audit on mods where instance='JOB1' group by modifier,reason)

Waiting for your expert comments.
Regards
Neeraj

ray.wurlod · Post by **ray.wurlod** » Thu Feb 12, 2009 12:40 am

1. DTM is VarChar, so you can use LIKE operator.

Code: Select all

WHERE DTM LIKE '2009-02-11%'

2. It's the MAX(DTM) in your query that is eliminating some of the User2 modifications. Lose the MAX() function.

3. You don't need IN if the sub-query is SELECT MAX(DTM)... because the sub-query can only ever return one row, therefore = is the appropriate operator.

neeraj · Post by **neeraj** » Thu Feb 12, 2009 2:58 am

Hello Ray,

thanks for the reply. But I want to use DTM in Group by clause and that in date format.

Regards
Neeraj

ray.wurlod · Post by **ray.wurlod** » Thu Feb 12, 2009 1:54 pm

What do you mean by "date format"? Give an example of your desired output.

Also, since you don't have premium membership yet, you can't read all of my previous answer.

neeraj · Post by **neeraj** » Fri Feb 13, 2009 1:46 am

Hello Ray,

My requirement is as follows:

My Input is

Start_date =2008-12-31
End_date=2009-12-31
************************************************************
1. I want to have a query on DS_AUdit table and want to fetch all the record where which lies between Start_date and End_Date.

Select * from ds_audit where DTM > Start_date and DTM < end_date

Please let me know how can I achieve this requirement.
**********************************************************
2. Please help me with the proper query. I am facing some date format issues which passing the input date as '2008-12-31' . it is converting it into 2009 Dec 31...

I am using below mentioned statement
select cast(cast('2008-12-31 as varchar(10)) || ' ' || cast(current_time as varchar(10)) as varchar(20)) as DT fmt '40l', instance from ds_audit
Regards
Neeraj

ray.wurlod · Post by **ray.wurlod** » Fri Feb 13, 2009 4:20 am

Code: Select all

SELECT *
FROM DS_AUDIT
WHERE DTM >= '2008-01-01'
AND DTM < '2009-01-01'
WHEN DTM >= '2008-01-01'
AND DTM < '2009-01-01' ;

neeraj · Post by **neeraj** » Fri Feb 13, 2009 5:20 am

Thanks a lot.

I am still waiting for my second reply.

i.e

for e.g
Modified on......... Modified by......... Reason for modification...............

2008-05-06 07:04:09 User1 Designer edit
2008-05-06 07:07:24 User1 Designer edit
2008-05-06 07:07:25 User1 Designer edit
2008-05-06 07:07:26 User1 Designer edit
2008-05-06 07:14:24 User1 Designer edit
2008-05-06 07:14:27 User1 Designer edit
2008-05-06 07:14:28 User1 Designer edit
2008-05-06 10:32:08 User1 Designer edit

and I execute my below mentioned quesry, I want total count=8 because all the date is 2008-05-06.

SELECT COUNT(*) FROM UNNEST DS_AUDIT ON MODS WHERE INSTANCE='job_SL0_SL_RF_CAPACITY_PLANNER_Load' GROUP BY DTM

COUNT ( * )
8

But as the DTM is 'yyyy-dd-mm HH:MI:SS' that is why I am not getting the proper result and some function need to be used to use only date part in the group by clause i.e. 'yyyy-dd-mm'.

So that my query should look like as below:-
SELECT COUNT(*) FROM UNNEST DS_AUDIT ON MODS WHERE INSTANCE='job_SL0_SL_RF_CAPACITY_PLANNER_Load' GROUP BY '2008-06-06'

Can you please correct the query.

Regards
Neeraj

kduke · Post by **kduke** » Fri Feb 13, 2009 11:51 am

When you start dealing with multivalued fields then you better know what you are doing. Otherwise extract the data into a table and do it in another database. You will be playing with this for days and not get your desired results. Ray can do it in seconds because he understands multivalues.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 13, 2009 2:06 pm

Recall that DTM is VARCHAR. Therefore you can use substring. Within SQL the syntax is

Code: Select all

SUBSTRING (DTM FROM 1 FOR 10)

I would probably add an I-descriptor to the dictionary of DS_AUDIT and use that in the query.

Code: Select all

0001: I
0002: SUBSTRINGS(DTM,1,10)
0003:
0004: Date Modified
0005: 10R
0006: M
0007: MODS

neeraj · Post by **neeraj** » Tue Feb 17, 2009 1:33 am

Thanks Kim and Ray..

I tried the Substring. It isworking find if I am using below mentioned query

SELECT SUBSTRING (DTM FROM 1 FOR 10),MODIFIER FROM UNNEST DS_AUDIT ON MODS WHERE INSTANCE='job1'

But If I used below mentioned query, it doesn't work
>SELECT MAX(SUBSTRING (DTM FROM 1 FOR 10)), MODIFIER,REASON FROM UNNEST DS_AUDIT ON MODS WHERE INSTANCE='job1' GROUP BY MODIFIER,SUBSTRING (DTM FROM 1 FOR 10)
SQL+
DataStage/SQL: syntax error. Unexpected symbol. Token was "SUBSTRING".
Please help.

Regards
Neeraj

ray.wurlod · Post by **ray.wurlod** » Tue Feb 17, 2009 1:50 am

I couldn't get that to work either. But if you create the I-type in the file dictionary of DS_AUDIT and use that, it's all good.

Code: Select all

SELECT MOD_DATE, COUNT(*) FROM UNNEST DS_AUDIT ON MODS GROUP BY MOD_DATE;

To get the I-type into the file dictionary and functional you need two commands - one is SQL (with trailing semi-colon), the other is not.

Code: Select all

INSERT INTO DICT DS_AUDIT(F0,F1,F2,F4,F5,F6,F7) VALUES ('MOD_DATE', 'I', 'SUBSTRINGS(DTM,1,10)', 'Date Modified', '10R', 'M', 'MODS');

CD DS_AUDIT MOD_DATE