September 1, 2010 at 11:34 am
Greetings super wizards,
I have a fairly complicated query need.
I am trying to get my data laid out like the following sample data:
Name Rel EstNo DtFild Pub TypeOfDocument Btyp BKNo PGNO DISP DISPDT
BOLTON, THERON MILTON MIN 145523 013091 N PET VEST GDNSHIP OF PROPERTY MIN 1257 001 GRANTED 020691
020891 N LETTS GDN PROPERTY ONLY LGDN 0028 198 ORD REC 020891
021291 Y PET LVE SELL LAND PRIV SALE MIN 1268 071 GRANTED 031191
112195 N INVENTORY MIN 1830 215 ORD REC 112195
112195 N ANNUAL RETURN RB 0634 311 GRANTED
112895 Y PET LVE ENCUMBER MIN 1864 280 GRANTED 013096
I am having a few problems.
1, regular query such as select * from PCS60418_MTHLY_XREF where name like '%@name%' only gives me one row of data if name is found.
That doesn't give me all the data associated with that name.
And I cannot use the following query:
select * from PCS60418_MTHLY_XREF where dtfiel in ('value1' , 'value' '...valuenN'] doesn't work either because first I have to know the date values up front and second other names also use same date values.
So, I am really stumped on how to write the query so that I can get a name displayed only ones and all data associated with it are displayed like the sample I showed above.
I can really use your expert help, please.
Below is the table structure:
CREATE TABLE [dbo].[PCS60418_MTHLY_XREF](
[NAME] [nvarchar](50) NULL,
[REL] [nvarchar](50) NULL,
[ESTNO] [nvarchar](50) NULL,
[DTFILD] [nvarchar](50) NULL,
[PUB] [nvarchar](50) NULL,
[TYPEOFDOCUMENT] [nvarchar](50) NULL,
[BTYP] [nvarchar](50) NULL,
[BKNO] [nvarchar](50) NULL,
[PGNO] [nvarchar](50) NULL,
[DISP] [nvarchar](50) NULL,
[DISPDT] [nvarchar](50) NULL
) ON [PRIMARY]
Thank you very, very much in advance
September 1, 2010 at 1:13 pm
This looks like something that is being displayed to the user. This type of hiding of repeated values is usually handled in the displaying application.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 1, 2010 at 1:24 pm
Thanks Wayne,
I would have been able to display it as I showed in the example but the problem is that when I query the db, it displays only the first row with the Name.
Other rows are ignored.
That's the cruz of the issue I am having.
September 1, 2010 at 1:39 pm
Can you give us some test data as it would be stored in the table (formatted as an insert statement)?
September 1, 2010 at 4:11 pm
Since no test data, I have no easy way to test if this approach might work.
You could probably include a Row_Number column partitioned/ordered by the appropriate column then
simply use a Case Statement as you traverse the rows and set to blank/empty string where the Row_Number
<>1.
It is, however, as previously mentioned best to do this kind of stuff in the UI somewhere.
September 1, 2010 at 4:28 pm
Sim, is the data actually stored in the way you listed the entry in the first code snippet above, where only one row carries the name and the next x rows are blank and belong to the first row before it?
If so, you broke your database, but the way you're describing the results makes it sound like that's how the data is stored.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 1, 2010 at 6:11 pm
Hello good people, sorry for late response.
We have to leave the building at certain time of day and I just got here from heavy traffic.
Let me start responding from bottom up.
Craid, yes, however, the only way you can tell is by opening the table.
You know, you right-click on table and select Open.
Then I scrolled down. It is about 596,000 rows all together.
Rather, it is all scattered all over the db which is why it is very difficult to query them and display them like the layout I showed.
Nevin and Wayne, here is some test data.
I am hoping that it is the way the insert is set up.
I say this because, the users use some system and insert data into a main frame and I as it was explained to me, next time the need to add another record, they just pull up the name and add a new record.
I am told that's why it is laid out the way it is.
Nevin, like I explained to Wayne, if I can use some select statement to up all records associated with a particular name, I can use UI to format the layout.
I am just not able to select associated records.
For instance, the data I displayed, belonged to one name.
However, if I query that record, something like select * from table where name='somename', I get only the records where that name appears on the db. If that name is blank as shown in my layout above, only the first row is displayed, making it seem like it is only one row of data.
Below is my best guess as to how data was inserted.
Unfortunately, I know nothing about mainframe and the code they used as I under is cics.
Please, please let me know what else I can provide.
Insert into [dbo].[PCS60418_MTHLY_XREF](
[NAME],
[REL],
[ESTNO],
[DTFILD],
[PUB],
[TYPEOFDOCUMENT],
[BTYP],
[BKNO],
[PGNO],
[DISP],
[DISPDT])
Values('BOLTON, THERON MILTON','MIN','145523','013091','N','PET VEST GDNSHIP OF PROPERTY','MIN','1257','001','GRANTED','020691');
Values(NULL,NULL,NULL,'020891','N','LETTS GDN PROPERTY ONLY','LGDN','0028','198','ORD REC','020891');
Values(NULL,NULL,NULL,'021291','Y','PET LVE SELL LAND PRIV SALE','MIN','1268','071','GRANTED','031191');
Values(NULL,NULL,NULL,'112195','N','INVENTORY','MIN','1830','215','ORD REC','112195');
Values(NULL,NULL,NULL,'112195','N','ANNUAL RETURN','RB','0634','311','GRANTED',NULL);
Values(NULL,NULL,NULL,'112895','Y','PET LVE ENCUMBER','MIN','1864','280','GRANTED','013096')
And I am extremely grateful for your assistance.
September 1, 2010 at 7:32 pm
Um, Sim? You've got badly related flat file data there... no wonder it doesn't work. Um, wow, where to start here.
First, google up normalization. Secondly you need to do 'joins', one table for the name with an ID recognizing it, the second table with the rest of the data carrying that ID on *every* line.
You... um... you broke it. That data will NEVER come up with the secondary data. Ever. The *first* thing you need to do is repeat the first two columns for every line of data associated with it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 1, 2010 at 7:59 pm
Craig,
The data was just dumped from mainframe to a .txt file and I just imported it to sql server db.
September 1, 2010 at 8:43 pm
simflex-897410 (9/1/2010)
Craig,The data was just dumped from mainframe to a .txt file and I just imported it to sql server db.
Nasty. Alright, quick fix. First, back the table up. Then, if needed, add an Identity(1,1) column to the data to hold the data in its current organization so that every row below continues to identify with the row above.
Make sure the blank entries in name and rel are NULL, not '' strings.
UPDATE PCS60418_MTHLY_XREF SET name = NULL where RTRIM( LTRIM( name)) = ''
UPDATE PCS60418_MTHLY_XREF SET rel = NULL where RTRIM( LTRIM( rel)) = ''
Then, run this:
DECLARE @name VARCHAR(1000),
@rel VARCHAR(1000)
UPDATE PCS60418_MTHLY_XREF
SET @name = name = isnull( name, @name),
@rel = rel = isnull( rel, @rel)
That will repeat name and rel into every column below it that's null.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 1, 2010 at 8:58 pm
Craig Farrell (9/1/2010)
simflex-897410 (9/1/2010)
Craig,The data was just dumped from mainframe to a .txt file and I just imported it to sql server db.
Nasty. Alright, quick fix. First, back the table up. Then, if needed, add an Identity(1,1) column to the data to hold the data in its current organization so that every row below continues to identify with the row above.
Make sure the blank entries in name and rel are NULL, not '' strings.
UPDATE PCS60418_MTHLY_XREF SET name = NULL where RTRIM( LTRIM( name)) = ''
UPDATE PCS60418_MTHLY_XREF SET rel = NULL where RTRIM( LTRIM( rel)) = ''
Then, run this:
DECLARE @name VARCHAR(1000),
@rel VARCHAR(1000)
UPDATE PCS60418_MTHLY_XREF
SET @name = name = isnull( name, @name),
@rel = rel = isnull( rel, @rel)
That will repeat name and rel into every column below it that's null.
Actually, there are a few other things you need to know to use this! Please read this article[/url] for all the rules in using this form of the update statement - they must be followed or you can get erroneous results!
This update statement is missing:
1. clustered index on the new identity column.
2. anchor column
3. TABLOCKX hint
4. MAXDOP 1 option
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 1, 2010 at 9:16 pm
ok, doing it now.
be back in a sec.
Thanks a lot Wayne
September 1, 2010 at 9:22 pm
WayneS (9/1/2010)
this article[/url] for all the rules in using this form of the update statement - they must be followed or you can get erroneous results!This update statement is missing:
1. clustered index on the new identity column.
2. anchor column
3. TABLOCKX hint
4. MAXDOP 1 option
Thanks Wayne, I couldn't seem to find that article when I wanted it. I assumed, though, that this data was just table dumped so he'd be fine, but there was a reason I mentioned the backup. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 1, 2010 at 9:24 pm
Ok, I just performed those actions you asked for.
They seem successful.
September 1, 2010 at 9:31 pm
I have a quick question Wayne.
Why didn't I just do that for only name since that's the only one we are interested in displaying first value only while leaving the rest blank?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply