Seq No based on the column values reappearing.

Question

Seq No based on the column values reappearing.

SSC Enthusiast

Points: 136

February 6, 2017 at 10:13 am

HI All, i have the data in the below format in the table.
What i need to do is i to find the FLD_NM column Value(SEQ_NO) to find the rowPosn for only those records for which the isNewData flag set to 1
for ex : in the attached document i need the result to be rowPosn=10000000000 and rowPosn=10000000014 only as the IsNewData columns are set as 1 only for 10000000003 & 10000000015.
Note that 1. all the data is in SQL table.
2. the columns Starting from RT till Value is one column under the name of RecordData. but its a fixed width record and each have been distributed based on the fixed length, For easier sense i have divided into bunch of columns with each having its own length. its in the below format.

Record Type (RT)	2	1	2
cnsmr_id	20	3	22
Account_ID	20	23	42
TblNm	50	43	92
FldNm	50	93	142
Data type (DT) 00 = Text 01 = Date (format: mmddccyy) 02 = Amount (format: 0000000000.00) 03 = Boolean (0 = false, 1 = true) 04 = Integer (#####)	2	143	144
Value(FldValue)	500	145	644

Let me know if i missed anything in the requirement desc.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63616 More actions · Answer 1

Welcome to SSC. Please help us help you and read this article: Forum Etiquette: How to post data/code on a forum to get the best help

Once we have consumable data, this should be an easy question.

pawan.clong10 SSC Enthusiast Points: 136 More actions · Answer 2

HI i have added this into the temp table for the easyness... Let me know in case i may have missed anything.
However,as i have posted earlier the RecordData is a set of fixed length values with each can be seperated based on the below logic. So you might have to be use Substring to filter the values for uniqueness of a record.

Record Type (RT)	2	1	2
cnsmr_id	20	3	22
Account_ID	20	23	42
TblNm	50	43	92
FldNm	50	93	142
Data type (DT) 00 = Text 01 = Date (format: mmddccyy) 02 = Amount (format: 0000000000.00) 03 = Boolean (0 = false, 1 = true) 04 = Integer (#####)	2	143	144
Value(FldValue)	500	145	644

pietlinden SSC Guru Points: 63616 More actions · Answer 3

I tried running your CREATE TABLE and INSERT scripts, but they wouldn't compile. Oh wait, there were no scripts. Hence no useful reply,,,

pawan.clong10 SSC Enthusiast Points: 136 More actions · Answer 4

Pardon me as i am new to this, i think i have attached the .sql file which i think is not admissible format. i am changing the extension to .txt and appending it.
let me know if that works.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

The first thing to say is that this is a horrible, horrible database design. You've already seen how difficult it is to query, and performance will be atrocious, too. If this is your design (and even if it isn't), then I recommend you read about database normalisation.

Of course, you've probably inherited the design from someone else, for which you have my sympathy. Let's try to make some sense of it. This is an EAV (Entity-Attribute-Value) design, with the added complications that the Es, As and Vs don't each have their own column, and that there doesn't appear to be anything that identifies the individual entities. My question, therefore, is this: how do you know which rows group together to form a single entity? For example, how do you determine that that the RowPosNo 10000000003 (which has IsNewData1) belongs in the same group as RowPosNo 10000000000, and that 10000000015 belongs in the same group as 10000000014?

John