Studio - Sorting Sql Output For Timestamp Column

Teradata Studio
Enthusiast

Studio - Sorting Sql Output For Timestamp Column

I am finding that in my sql ouput if I try to sort on a column that is a timestamp by clicking the column header it does not sort properly. The data order is changed but not in proper sequence. It does sort properly say for an integer column but for a timestamp column it does not sort the same as it would if I had used an order by clause in the original sql. Any way around this? Thank you.

3 REPLIES
Teradata Employee

Re: Studio - Sorting Sql Output For Timestamp Column

The sort in the Result Set Viewer uses the timestamp value from the database so should be sorting correctly. I have run some tests but not seeing any issues. Can you send your DDL and example data that I can try to recreate your issue.

Enthusiast

Re: Studio - Sorting Sql Output For Timestamp Column

Francine,

 
Below is ddl and export file. I tested loading file via Studio since original data was loaded by an application. Load ran fine but timestamps are not coming out correctly if I compare back to the source txt file. If you look at source and then loaded data they are not matching. That was not my original question so maybe it something to do with options I selected to load via Studio. If I do try to sort on a timestamp column it does not sort correctly after I do this load. Most rows are sorted correctly but if I go through them all the sort sequence eventually breaks..
 
The input I gave you is tab delimited, labels in first row, no char string delimiter. Please let me know if I left anything out. Thank you.
 
CREATE MULTISET TABLE PROD_MPERK_OFR_RNK.MODEL_RUN_LIST_JQ ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      JQ_UPDT_AUDIT_DTTM TIMESTAMP(6),
      OFFER_GROUP_ID INTEGER NOT NULL,
      MODEL_TYPE_CD VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      MODEL_SUB_TYPE_CD VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      M_ADS_ID INTEGER NOT NULL,
      EXCPT_REASON_CD VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
      MODEL_ID INTEGER,
      NBR_MODEL_CREATION_ATTEMPTS SMALLINT NOT NULL,
      LAST_MODEL_STATE_CD VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC,
      INS_AUDIT_DTTM TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      UPDT_AUDIT_DTTM TIMESTAMP(6))
UNIQUE PRIMARY INDEX ( OFFER_GROUP_ID ,MODEL_TYPE_CD ,MODEL_SUB_TYPE_CD );
JQ_UPDT_AUDIT_DTTM	OFFER_GROUP_ID	MODEL_TYPE_CD	MODEL_SUB_TYPE_CD	M_ADS_ID	EXCPT_REASON_CD	MODEL_ID	NBR_MODEL_CREATION_ATTEMPTS	LAST_MODEL_STATE_CD	INS_AUDIT_DTTM	UPDT_AUDIT_DTTM
2017-04-11 12:17:46.220000	235	NN	NN	1	NULL	40	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 12:17:46.220000
2017-04-11 12:12:28.130000	528	BN	2P	1	NULL	37	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 12:12:28.130000
2017-04-11 12:17:11.320000	235	BN	1	1	NULL	38	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 12:17:11.320000
2017-04-11 11:40:48.020000	521	BN	1	1	NULL	2	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:40:48.020000
2017-04-11 12:02:59.990000	523	BN	1	1	NULL	28	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 12:02:59.990000
2017-04-11 12:11:34.790000	504	NN	NN	1	NULL	33	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 12:11:34.790000
2017-04-07 11:45:24.570000	317	NN	NN	1	BLW	NULL	0	NULL	2017-04-07 11:35:38.420000	2017-04-07 11:45:24.570000
2017-04-11 12:11:17.080000	521	BN	2P	1	NULL	32	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 12:11:17.080000
2017-04-11 12:12:10.270000	499	BN	1	1	NULL	35	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 12:12:10.270000
2017-04-11 12:16:53.220000	521	NN	NN	1	NULL	36	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 12:16:53.220000
2017-04-11 11:42:44.730000	524	BN	2P	1	NULL	10	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:42:44.730000
2017-04-07 11:49:58.740000	523	BN	2P	1	LCNT	NULL	0	NULL	2017-04-07 11:45:25.660000	2017-04-07 11:49:58.740000
2017-04-11 11:40:29.680000	524	NN	NN	1	NULL	1	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 11:40:29.680000
2017-04-11 12:01:04.170000	361	BN	2P	1	NULL	21	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 12:01:04.170000
2017-04-11 12:10:59.390000	445	NN	NN	1	NULL	31	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 12:10:59.390000
2017-04-11 11:41:05.900000	483	NN	NN	1	NULL	3	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 11:41:05.900000
2017-04-11 11:50:41.880000	499	NN	NN	1	NULL	8	3	ACTV	2017-04-07 11:35:38.420000	2017-04-11 11:50:41.880000
2017-04-11 11:51:36.030000	526	BN	2P	1	NULL	13	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:51:36.030000
2017-04-11 12:00:38.780000	526	NN	NN	1	NULL	20	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 12:00:38.780000
2017-04-11 12:02:23.330000	504	BN	2P	1	NULL	25	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 12:02:23.330000
2017-04-11 12:02:05.810000	235	BN	2P	1	NULL	24	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 12:02:05.810000
2017-04-11 11:52:13.030000	498	NN	NN	1	NULL	15	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 11:52:13.030000
2017-04-11 11:53:26.030000	524	BN	1	1	NULL	19	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:53:26.030000
2017-04-11 11:50:23.800000	528	NN	NN	1	NULL	7	3	ACTV	2017-04-07 11:35:38.420000	2017-04-11 11:50:23.800000
2017-04-11 12:01:47.450000	522	BN	2P	1	NULL	23	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 12:01:47.450000
2017-04-11 12:02:42.440000	361	NN	NN	1	NULL	27	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 12:02:42.440000
2017-04-11 12:11:52.570000	526	BN	1	1	NULL	34	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 12:11:52.570000
2017-04-11 11:42:04.410000	498	BN	2P	1	NULL	6	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:42:04.410000
2017-04-11 12:10:23.720000	522	NN	NN	1	NULL	26	3	ACTV	2017-04-07 11:35:38.420000	2017-04-11 12:10:23.720000
2017-04-07 11:45:24.570000	317	BN	BN	1	BLW	NULL	0	NULL	2017-04-07 11:35:38.420000	2017-04-07 11:45:24.570000
2017-04-11 11:41:42.660000	361	BN	1	1	NULL	5	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:41:42.660000
2017-04-11 11:51:17.150000	498	BN	1	1	NULL	12	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:51:17.150000
2017-04-11 11:42:25.310000	499	BN	2P	1	NULL	9	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:42:25.310000
2017-04-11 11:50:59.500000	519	BN	1	1	NULL	11	3	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:50:59.500000
2017-04-11 11:52:50.510000	528	BN	1	1	NULL	17	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:52:50.510000
2017-04-11 12:01:28.670000	523	NN	NN	1	NULL	22	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 12:01:28.670000
2017-04-11 11:53:08.270000	483	BN	2P	1	NULL	18	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:53:08.270000
2017-04-11 12:17:28.610000	445	BN	BN	1	NULL	39	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 12:17:28.610000
2017-04-11 11:41:23.520000	522	BN	1	1	NULL	4	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:41:23.520000
2017-04-11 11:52:32.060000	519	NN	NN	1	NULL	16	1	ACTV	2017-04-07 11:35:38.420000	2017-04-11 11:52:32.060000
2017-04-11 11:51:53.670000	483	BN	1	1	NULL	14	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 11:51:53.670000
2017-04-11 12:10:41.420000	519	BN	2P	1	NULL	30	1	ACTV	2017-04-07 11:45:25.660000	2017-04-11 12:10:41.420000
2017-04-11 12:02:39.480000	504	BN	1	1	NULL	29	3	UMDL	2017-04-07 11:45:25.660000	2017-04-11 12:02:39.480000
Teradata Employee

Re: Studio - Sorting Sql Output For Timestamp Column

We are looking into this problem and hope to have a resolution soon.