Re: Concatenating all fields in a resultset - Database Discussion Boards

Mycroft Holmes13-Apr-10 23:38

13-Apr-10 23:38

Prefix the column name with the tablename or the alias.

Also try Google and BOL, the response time is generally quicker and gentler.

Never underestimate the power of human stupidity
RAH

Re: search from multiple tables

Eddy Vluggen14-Apr-10 5:37

Eddy Vluggen

14-Apr-10 5:37

test-09 wrote:
Now i need to search columns from 5 tables How can i do it....

One alternative would be to build some views, consisting of a tablename, columnname and a varchar representing the value. Create a union of those views to search all columns with a single select-statement and the LIKE operator.

What database are you using? If you're on Sql Express, have you considered using the Full Text Search[^] component?

I are Troll Suspicious | :suss:

Concatenating all fields in a resultset

Dewald13-Apr-10 4:46

Dewald

13-Apr-10 4:46

I'm looking for a way to concatenate all the fields in a result set to one value. If I know the names of the fields, it is fairly easy:

SELECT Field1 + Field2 + Field3 FROM MyTable

although it is a bit more tricky if one or more of the fields are numeric in which case the following would do:

SELECT CAST(Field1 AS VARCHAR) + CAST(Field2 AS VARCHAR) + CAST(Field3 AS VARCHAR) FROM MyTable

The above snippets would return a long string (a concatenation of all the fields) for each row in the table.

But what if I don't know the names, number of and types of fields in the table? Is there a way to still accomplish the above?

Thanks in advance.

Re: Concatenating all fields in a resultset

David Mujica13-Apr-10 5:33

David Mujica

13-Apr-10 5:33

You would have to mess around with sys.columns sys.types to determine the datatype of the columns dynamically. Of course you would have to know the table and column name before trying to get the datatype.

OR

Are you constrained by using TSQL ?

If you write a .NET application and put the results into a Datatable, you could then find the datatype by using something like: Datatable.Columns.GetType()

Good luck Thumbs Up | :thumbsup:

Re: Concatenating all fields in a resultset

Dewald13-Apr-10 21:01

Dewald

13-Apr-10 21:01

Thanks,

I was hoping to be able to do it using SQL only but I eventually decided to do it from inside a C# app which does make it a lot simpler.

Re: Concatenating all fields in a resultset

Mycroft Holmes13-Apr-10 22:35

Mycroft Holmes

13-Apr-10 22:35

You can use syscolumns or the system views and basically build a string in TSQL using a loop or cursor and the execute the string. This is a real kludge but there is no other way that I know of. Seems like a rather silly requirement!

Never underestimate the power of human stupidity
RAH

Re: Concatenating all fields in a resultset

Dewald14-Apr-10 0:38

Dewald

14-Apr-10 0:38

Yeah it probably is a silly requirement. The reason I wanted to do so is as follows.

I have a number of tables that I need to check for changes on a regular basis. I can't add columns to the tables so I have to compare them against "backup copies". I decided to create a mirror table for each that holds the last saved version of the table. When changes are made to the original table I wanted my script to compare the table against mine and then process only those rows that are different.

To compare an entire row with my backup table's row, I thought I'd return the row as I described above (ie a long concatenated string). This way I wouldn't need to compare it field by field.

In the end I decided to do most of the work in C# anyway so it didn't matter that I'd have to compare the rows field by field.

Re: Concatenating all fields in a resultset

Mycroft Holmes14-Apr-10 2:06

Mycroft Holmes

14-Apr-10 2:06

I would use a different approach, I presume you can add a new table to the database even if you can't change an existing table.

So have a log table to record changes, depending on requirements it could be as little as and Id, modified and modifiedby columns for each table you are monitoring. Changes can be logged by spit triggers, this is one of the very few valid uses for triggers.

If you need a from => to audit then a more detailed logging process can be implemented.

Never underestimate the power of human stupidity
RAH

What is the Best Approach for this Request

Vimalsoft(Pty) Ltd13-Apr-10 0:56

Vimalsoft(Pty) Ltd

13-Apr-10 0:56

Good Day

Application Design

My application is a web application that works on databases. Meaning that there is a login page that has list of Database. A user logs into the database and do whatever on that database. Now a user can create a new Database from the front end and login to it and do whatever the application does, The new Database is created from a Master Database, this simply means that when a user creates a new Database, i do the following.
Create a new Database--> Restore the Master Database over the newly created database. Now this is fine but i have a challenge. My Challenge is that a user can 10 Databases and i will have Different clients from Different geographic places and if i find a bug in a stored procedure or if i want to change on stored procedure , that means i have to go to each client and update every database. i use Redgate to Script the Master Database.

Challenge

I am looking for a Way to Update all the Databases for each SQl instance in a client centrally without connection to a client and do that manually. if the clients are many it takes too much time.

Thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: What is the Best Approach for this Request

Mycroft Holmes13-Apr-10 2:37

Mycroft Holmes

13-Apr-10 2:37

Holy sh*t, my skin actually crawled as I read this post. Are you freaking nuts, this is the WORST data design you could possibly come up with.

Red-Gate will mitigate the problem, SQL Compare is an excellent product but you are maintaining multiple code bases because you cannot segregate you data within a single database. This is elementary database stuff, your solution stinks, phew, and will/is cause you nightmares.

Vuyiswa Maseko wrote:
Challenge

That's not a challenge that a bloody disaster!

Never underestimate the power of human stupidity
RAH

Re: What is the Best Approach for this Request

Vimalsoft(Pty) Ltd13-Apr-10 2:54

Vimalsoft(Pty) Ltd

13-Apr-10 2:54

Mycroft am not sure why you start cursing but i will decide not to be offended by what you just wrote. I think you should look at your forum guideline and revisit rule number 8

http://www.codeproject.com/Messages/1278600/How-to-get-an-answer-to-your-question.aspx[^]

This is a Project i inherited and i must admit a poor decision has been taken from the beginning and we i can see the decesion takers of that project i will definitely shoot one of them, but now it will not help.

Getting back to the problem. To maintain this application this way its a nightmare and i cant run a script to update a database every a user needs to login. Currently am looking for a thought and in the next version of the Software i will be looking at Redesigning the data structure for this application.

Re: What is the Best Approach for this Request

Mycroft Holmes13-Apr-10 4:15

Mycroft Holmes

13-Apr-10 4:15

Vuyiswa Maseko wrote:
This is a Project i inherited

You have my sympathies. Red-Gate have a product for running a script on multiple database from a single point, I have never looked at it as I only have the 3 (dev/UAT/Production) but it may be useful to this nightmare.

I can understand the desire to redesign, the only reason you should have been offended is if you design the structure.

Never underestimate the power of human stupidity
RAH

Re: What is the Best Approach for this Request

Vimalsoft(Pty) Ltd13-Apr-10 4:17

Vimalsoft(Pty) Ltd

13-Apr-10 4:17

I understand.

Thank you for your help

Kind Regards

Family Tree Database

vdtrip12-Apr-10 10:06

vdtrip

12-Apr-10 10:06

Hi,
I have a project FamilyTree,it's like a Chain Relation Management.But i have no idea that how i will have to manage Database.Can anybody tell me some ideas for database.
VD

Re: Family Tree Database

Jörgen Andersson12-Apr-10 11:51

Jörgen Andersson

12-Apr-10 11:51

My first thought was this:

create table family_relatives (
	relative_id	integer primary key,
	spouse		references family_relatives,
	mother		references family_relatives,
	father		references family_relatives,
	-- in case they don't know the exact birthdate
	birthyear	integer,
	birthday	date,
	-- sadly, not everyone is still with us
	deathyear	integer,
	first_names	varchar(100) not null,
	last_name	varchar(100) not null,
	sex		char(1) check (sex in ('m','f')),
	-- note the use of multi-column check constraints
	check ( birthyear is not null or birthday is not null)
);

but it lacks a bit. There's no control that your father is male and that your mother is female. And it doesn't keep track on ex-spouses. So one might want to drop the spouse column and add a partner table instead.
Keeping track on that your parents are born before you and not dead at your birth is another one. Or at least the mother, the father might actually be dead at your birth

"When did ignorance become a point of view" - Dilbert

Re: Family Tree Database

Mycroft Holmes12-Apr-10 14:18

Mycroft Holmes

12-Apr-10 14:18

SQL Servers hierarchy ID might be an interating tool to use for this.

Never underestimate the power of human stupidity
RAH

Re: Family Tree Database

vdtrip13-Apr-10 1:43

vdtrip

13-Apr-10 1:43

Hi,
How can i use this SQL Server hierarchy ID,please can you explain it.
Thanks

Re: Family Tree Database

J4amieC13-Apr-10 2:11

J4amieC

13-Apr-10 2:11

Google broken where you are?[^]

Re: Family Tree Database

Mycroft Holmes13-Apr-10 2:30

Mycroft Holmes

13-Apr-10 2:30

HierarchyID maitains a parent - child relationhip and may make structuring your data simpler. Do a bit of research in BOL.

Never underestimate the power of human stupidity
RAH

SqlDateTime overflow. - Only on one computer out of 5 running the same code

Marcus J. Smith12-Apr-10 9:06

Marcus J. Smith

12-Apr-10 9:06

I have a program that uses SqlBulkCopy to insert data into a staging table for processing. 5 different computers run the same .NET and database code and only one or a couple occasionally will receive the following error:

"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."

The exact same data will import correctly for other users as well as myself. There are only valid dates in the fields and I verified that their local database tables match the column order I am expecting. The only thing I can think of is that a memory issue is coming into play here and the data is corrupting internally before import. Is there something else I can look at? I dont understand why the same code has different results.

_Cle^aKO

"Now, a man would have opened both gates, driven through and not bothered to close either gate." - Marc Clifton (The Lounge)

Re: SqlDateTime overflow. - Only on one computer out of 5 running the same code

Mycroft Holmes12-Apr-10 14:15

Mycroft Holmes

12-Apr-10 14:15

Bulk copy is fragile, what we do is ELT, Extract, Load then Transform, change all your staging fields to varchar, use bulk copy to shove the data into the table no matter what the format.

Then use a stored proc to transform the data, you can control and manage a proc better than any load process. This same argument applies to SSIS, get the data in then clean it up. We have found this solution both robust and fast, IMHO transforms during load are a disaster.

Never underestimate the power of human stupidity
RAH

determine last patient temprature

Jassim Rahma12-Apr-10 5:49

Jassim Rahma

12-Apr-10 5:49

I am using this code to determine last patient pulse in my vital_signs table.

-- determine pusle;
SET @pulse = (SELECT TOP 1 pulse FROM vital_signs WHERE file_no = @file_no AND visit_id = @visit_id AND pulse IS NOT NULL ORDER BY vital_id DESC)
	
IF (@is_child = 'True' AND (@pulse < (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_CHILD_MIN') OR @pulse > (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_CHILD_MAX')))
BEGIN
	SET @normal_pulse = 'False'
END
ELSE IF (@patient_gender = 'M' AND (@pulse < (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_MALE_MIN') OR @pulse > (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_MALE_MAX')))
BEGIN
	SET @normal_pulse = 'False'
END
ELSE IF (@patient_gender = 'F' AND (@pulse < (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_FEMALE_MIN') OR @pulse > (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_FEMALE_MAX')))
BEGIN
	SET @normal_pulse = 'False'
END

It was so easy as you can see Smile | :)

now I want to do the same for the temprature

but the temperature is more than one field unlike the pulse.. I have the following fields:

temperature_oral
temperature_axillary
temperature_rectal
temperature_tympanic
temperature_skin

How can I do the same code above to check the last temp taken from any field. Only one temp will be taken at at time so i need to get that value and the will compare using the above code...

Re: determine last patient temprature

PIEBALDconsult12-Apr-10 6:13

PIEBALDconsult

12-Apr-10 6:13

The schema you have will lead to trouble. Have the temperature table as a "transaction" sort of table. Then just query for the most recent. Do the same for the pulse, blood pressure, etc.

In fact, probably have one table for all the vital signs, one record per value:

ID, PatientID, TakerID, TimeStamp, ReadingType, Value

Re: determine last patient temprature

Jassim Rahma12-Apr-10 6:27

Jassim Rahma

12-Apr-10 6:27

but changing the table structure now is a major work! any solution with the current structure?

Re: determine last patient temprature

PIEBALDconsult12-Apr-10 9:47

PIEBALDconsult

12-Apr-10 9:47

No, fix it now before it becomes really difficult.

You can always create views to mimic the current schema.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.