How to find account number of bank from given records in table

Question

How to find account number of bank from given records in table

nikku

Ten Centuries

Points: 1103
More actions
February 18, 2016 at 4:47 am

#311450

Hi All
I have two tables t1 & t2 which contains the same records. t1 is for bank1 & t2 is for bank2
i.e t1
accno name balance
001000 a 5555
t2
accno name balance
001000 b 6000
I have to insert both table records in file t3. What i want to find is which account number is from wich bank
file t3 will be created same as that of t1/t2 columns. We cannot use extra columns in file.

Viewing 15 posts - 1 through 15 (of 16 total)

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

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

You can't fit a square peg into a round hole. If you don't want to add and extra column to t3, you'll have to resort to unsatisfactory methods such as adding something to the account name or number that identifies the bank.

John

nikku Ten Centuries Points: 1103 More actions · Answer 2

Thanks John 🙂

If we prefix the LHS bit with a “1” or any other numeral, then the users will then always have to key loads of zeroes and they may get grumpy about it. what you say ?

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

Grumpiness will be the least of your worries. It will be more difficult to validate entries, and you're breaking the rules of normalisation by forcing two attributes into one column. Why can't you just add another column to the third table?

John

Phil Parkin SSC Guru Points: 246993 More actions · Answer 4

John Mitchell-245523 (2/18/2016)
Why can't you just add another column to the third table?
John

Exactly what I was thinking.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Nikku (2/18/2016)
Hi All
I have two tables t1 & t2 which contains the same records. t1 is for bank1 & t2 is for bank2
i.e t1
accno name balance
001000 a 5555
t2
accno name balance
001000 b 6000
I have to insert both table records in file t3. What i want to find is which account number is from wich bank
file t3 will be created same as that of t1/t2 columns. We cannot use extra columns in file.

Then create another table t4 which has account no and bank.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nikku Ten Centuries Points: 1103 More actions · Answer 6

nikku

Ten Centuries

Points: 1103

February 22, 2016 at 12:04 am

#1860014

Thanks Guys 🙂

MMartin1 One Orange Chip Points: 27799 More actions · Answer 7

ChrisM@Work (2/18/2016)
Nikku (2/18/2016)
Hi All
I have two tables t1 & t2 which contains the same records. t1 is for bank1 & t2 is for bank2
i.e t1
accno name balance
001000 a 5555
t2
accno name balance
001000 b 6000
I have to insert both table records in file t3. What i want to find is which account number is from wich bank
file t3 will be created same as that of t1/t2 columns. We cannot use extra columns in file.
Then create another table t4 which has account no and bank.

The problem here is that account numbers from the banks can collide (be the same).

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 8

Please identify the banks you're doing this for so we can make sure that none of us will be affected by this. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

Jeff Moden (2/23/2016)
Please identify the banks you're doing this for so we can make sure that none of us will be affected by this. 😛

+1 to that! 😛

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

MMartin1 (2/23/2016)
ChrisM@Work (2/18/2016)
Nikku (2/18/2016)
Hi All
I have two tables t1 & t2 which contains the same records. t1 is for bank1 & t2 is for bank2
i.e t1
accno name balance
001000 a 5555
t2
accno name balance
001000 b 6000
I have to insert both table records in file t3. What i want to find is which account number is from wich bank
file t3 will be created same as that of t1/t2 columns. We cannot use extra columns in file.
Then create another table t4 which has account no and bank.
The problem here is that account numbers from the banks can collide (be the same).

Yup - I'm working with bank data right now. Hence another table with account no and bank.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

aaron.reese SSChampion Points: 13498 More actions · Answer 11

Nikku,

On a less facetious note, The other are correct, in a straight file format (CSV, EDI, flat file) or another table you would not be able to identify the bank without either adding another column which you say is not possible, or contatenating the bank ID to the account number, thereby effectively doing the same thing and causing additional processing further down the line.

You could however identify the bank if you use XML

<BANK>

... whatever bank details you need in here

</ACCOUNT>

</ACCOUNT>

</ACCOUNTS>

</BANK>

XML is easily read by computers and still readable by humans; you can provide DTD or DSX format rules

It does lead to quite large files though

You could also use JSON, although that notation is not supported by SQL out of the box so you would have to write your own

But in general I would challenge the assumption that you can't modify the columsn because whoever is going to read the data will have the same problems as you are creating the file/table

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 12

I definitely wouldn't use XML for anything even close to this. Also, computers don't "easily" read XML. There are programs that make it easy for Developers to incorporate it but it's pretty brutal behind the scenes all the way around. If you do a deep dive on it in SQL Server, it actually builds a temporary "Edge Table" in memory, which contains the XML as an "Adjacency List" and all the problems associated with such a structure. From what I understand, XML "indexes" are actually a "columnar normalization" of the XML data. I say cut out the middle man and don't use it for data transmittal or storage.

I view JSON as "only" being half as bad but half as bad is still a whole lot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246993 More actions · Answer 13

Jeff Moden (2/24/2016)
I definitely wouldn't use XML for anything even close to this. Also, computers don't "easily" read XML. There are programs that make it easy for Developers to incorporate it but it's pretty brutal behind the scenes all the way around. If you do a deep dive on it in SQL Server, it actually builds a temporary "Edge Table" in memory, which contains the XML as an "Adjacency List" and all the problems associated with such a structure. From what I understand, XML "indexes" are actually a "columnar normalization" of the XML data. I say cut out the middle man and don't use it for data transmittal or storage.
I view JSON as "only" being half as bad but half as bad is still a whole lot.

Hmmm, sounds like the basis for an XML article, Jeff. I know how much you like it :w00t:

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

aaron.reese SSChampion Points: 13498 More actions · Answer 14

aaron.reese

SSChampion

Points: 13498

February 24, 2016 at 10:46 am

#1860808

Bump, Set, Spike!