Database

Roger Wright29-Sep-10 17:00

Roger Wright

29-Sep-10 17:00

Cool! I'll be looking forward to seeing the result. I've learned a bunch from both of you, and appreciate your clear, informative presentation style. I expect it will be a great article! Big Grin | :-D

Will Rogers never met me.

MSBassSinger30-Sep-10 3:24

MSBassSinger

30-Sep-10 3:24

I am a firm believer in not passing datasets (or recordsets) around as universal data objects. I create objects that represent the real-world object, and aggregations of those objects where it makes sense. Those objects may span more than one table, the the consumers of my objects have no knowledge of a database.

For aggregation objects, I create a class that inherits from CollectionBase. That allows me to add a class within the aggregation class that inherits from IComparer so I can provide sorting capabilities. The aggregation class contains the code to handle CRUD, and to allow specifying a filter for querying.

The class that is used for the objects (the item class) collected by the aggregation class inherits from IDataObjectBase. The item class has code to validate, indicate if any values have been changed, etc.

That allows me to use both the aggregation class and the item class for objects that easily bind and can be found in VS's Data Source Explorer.

In a few cases, where I need hierarchical objects, an aggregation class can also be a property within an item class.

Yes, it takes more coding than passing around datasets, but it is more durable. Besides, once you have the first one coded the way you want, you can use it as a template for others, which reduces the coding time.

Mycroft Holmes30-Sep-10 12:03

30-Sep-10 12:03

You use a more advanced structure than I do Blush | :O

as I use a List<> when coding for the web and datatable for winforms (I like the datagridviews native sorting support for datatable).

You should do an article on your structure, I'd be interested in seeing it as I am always open to new and better ideas.

Never underestimate the power of human stupidity
RAH

MSBassSinger30-Sep-10 12:24

MSBassSinger

30-Sep-10 12:24

There are times I just use List<> or Dictionary<> when I don't need anything more than that. The main reason I use what I described is that 1) I want to encapsulate the CRUD operations at that level so the item classes are lighter weight, and 2) I want to provide other functionality than I get with List<> or Dictionary<>. I could, and have, used factory classes, but for some reason I am partial to strong encapsulation.

patzerFish30-Sep-10 4:20

patzerFish

30-Sep-10 4:20

Thanks for the feedback! I have begun to implement my own DAL building the CRUD (create, retrieve, update, delete) methods - but I've managed to do it without using datatables, following the model I used in a project where I was using C# to MySQL:

private static void executeNonQuery(String query) {
    SqlCommand command = new SqlCommand(query, Connection);
    try {
        openConnection();
        command.ExecuteNonQuery();
    } catch {
        throw;
    } finally {
        closeConnection();
    }
}

private static SqlDataReader executeQuery(String query) {
    SqlCommand command = new SqlCommand(query, Connection);
    SqlDataReader reader;
    try {
        openConnection();
        reader = command.ExecuteReader();
        return reader;
    } catch {
        throw;
    } finally {
        //connection left open because the reader is worked on elsewhere.
    }
}

These methods are the core of the DAL class. Each other static method (create, retrieve, update, delete) uses one of these two methods and pretty much has the responsibility of building a query string and passing it on. In the case of SELECT queries, a SqlDataReader is returned to be worked on. I haven't felt the need to push this data into a datatable, since it's fairly easy to just move this data into a data object or List<> of data objects. It will require more work on the side of binding data to form controls, but I think I prefer having more control over what is going on anyway.

Thanks again for your quick response!

Mycroft Holmes30-Sep-10 11:53

30-Sep-10 11:53

Excellent start, however you need to be aware the datareader lock the connection and should be consumed and disposed of ASAP, passing a datareader is asking for problems. For this reason I use datatable.fill. It uses a datareader under the hood but leaves you with a disconnected datatable as a result.

Also I do not make my DAL class static, I often connect to multiple databases. I do have a static class that hold the main copy of the dal but I often create additional copies for specific connections.

I have a single generic method that moves a datatable into a List<> of the object. I also have a UI helper class that binds a List<> to a form, naming discipline is required.

If you send me an email I will send you a copy of the DAL class.

Never underestimate the power of human stupidity
RAH

underclocker30-Sep-10 6:00

underclocker

30-Sep-10 6:00

I tried nHibernate once. It was a bit of a pill to get used to but once I did, I found that it really helped me in the whole binding and persisting work. It doesn't fit with my current work of real time processing ( well it would for system configuration and settings ) but otherwise I would use it all the time.

new line

scottichrosaviakosmos25-Sep-10 23:00

scottichrosaviakosmos

25-Sep-10 23:00

select gender,amt,age,[address],class ,

Case when gender= '' then 'gender,'when gender is null then 'gender,' Else '' End +
Case when amt = '' then 'amt,'when amt is null then 'amt,' Else '' End +
Case when age = '' then 'age,'when age is null then 'age,' Else '' End +
case when [address] = ''then 'address'when [address] is null then 'address,' else '' End +
Case when class = '' then 'class,'when class is null then 'class,' Else '' End
As [Error Remarks] from mytable

The above query is used to check if any of this row is blank or null display the result in
a column name "error remarks" .
Now the result i m getting is :
gender, amt, age,address,class
all this result is displayed in a single line in a single cell.
now i want the result to be displayed in different line:
like this:
gender,
amt,
age,
[address],
class

all this is single cell with the above query.

i had tried char(13) and 10 . and the value is coming in new line but in sql server textmode and not in grid mode . i want data to be in grid mode.

Re: new line

Mycroft Holmes25-Sep-10 23:40

(INSERT EXEC statement cannot be nested.) and (Cannot use the ROLLBACK statement within an INSERT-EXEC statement.) how to solve this??!!

25-Sep-10 23:40

scottichrosaviakosmos wrote:
i want the result to be displayed in different line

So do this in the UI where display operations belong! Why are doing such a silly thing as formatting your data for the query results grid, you should be the only person looking at SQL Server results.

Never underestimate the power of human stupidity
RAH

Re: new line

Corporal Agarn28-Sep-10 6:44

Corporal Agarn

28-Sep-10 6:44

How did you use the CHAR(13) and CHAR(10)?

Re: new line

bigbadben30-Sep-10 2:55

bigbadben

30-Sep-10 2:55

With SQL Enterprise Manager Query Results in grid view you can't display new lines - I came across the same only this week. I think they get converted to spaces deliberately prior to being dumped into the grid so everything stays on the one line. Microsoft made the call - most developers, most of the time, want to see most of their data without stretching the row-height every time they run the query. But trust me, those 13-10s/CR-LFs are working. As someone else said - you are the only person who will see this grid. If you want to whack this query into a .NET winforms grid view, it is possible to get the multi-lines to show there by setting a couple of properties (I could look it up for you, but I won't). And of course, make that query a view, link to the view from Microsoft Access as a linked table and you will see multi-lines. I will now run for cover.

Yajjoj25-Sep-10 9:43

Yajjoj

25-Sep-10 9:43

hi all
i have a very important issue,

read this scenario please

i have three Stored Procedures Sp1,Sp2 and Sp3 .

the first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the Second one will execute the third one (Sp3) and save data into @tempTB2.

if I execute the Sp2 it will works and it will returned me all my data from the Sp3 ,but the problem is in the Sp1, when i execute it it will display this Error:

INSERT EXEC statement cannot be nested

I tried to change the place of execute Sp2 and it display me another error:

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

al hajjaj

Create Database in Oracle 9i

MohanMbnr25-Sep-10 3:01

MohanMbnr

25-Sep-10 3:01

Dear All,

How to create a database in oracle 9i through query.

Mohan Kundena Goud

Re: Create Database in Oracle 9i

Eddy Vluggen25-Sep-10 12:01

Eddy Vluggen

25-Sep-10 12:01

Hi,

Would the example[^] on the bottom of that page work?

I are Troll Suspicious | :suss:

Create database to share folder

ndkit24-Sep-10 15:50

ndkit

24-Sep-10 15:50

Hi all,

Currently, I used SQL Server 2008 to create database on share folder as below query:

DBCC TRACEON (1807,-1)

CREATE DATABASE 20100914 ON (NAME="20100914_dat",FILENAME="\\HOST1\SHARE\20100914_DATA.MDF") LOG ON (NAME="20100914_log",FILENAME="\\HOST1\SHARE\20100914_LOG.LDF")

with \\HOST1\SHARE has full permission for all person.

When I run above query, the error is shown:
System.Data.SqlClient.SqlException: Directory lookup for the file "\\HOST1\SHARE\20100914_DATA.MDF" failed with the operating system error 5(failed to retrieve text for this error. Reason: 15105).

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Pls help me to solve it if you know.

Thanks a lot.

Re: Create database to share folder

Eddy Vluggen25-Sep-10 0:56

Eddy Vluggen

25-Sep-10 0:56

Error 5 indicates that the access has been denied; your share probably needs a username/password combination. If you want to share your data, create a TCP/IP connection to the server from a remote machine, putting the database on a unc-path is asking for problems;

MSDN states:[^]
If access to a disk resource requires that a share be mapped, or if the disk resource appears as a remote server through a UNC path, (for example, \\Servername\Sharename), on the network, then by default, the disk storage system is not supported as a location for SQL Server databases.

I are Troll Suspicious | :suss:

Query Improvement Related to Lounge Post

programmervb.netc++24-Sep-10 11:07

24-Sep-10 11:07

Ideas of how to improve this query

SELECT `UniqueNumber-900` AS `ClientNumber`,
			 (SELECT `AdmitDate`
				FROM `cl_clientsvcs_dayhab`
				WHERE `ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
				ORDER BY `AdmitDate` ASC
				LIMIT 1)
				 AS `InitialAdmitDate`,
			 CONCAT(`cl_client_common`.`LastName`, ', ', `cl_client_common`.`FirstName`, ' ', `cl_client_common`.`MiddleName`)
				 AS `ClientName`,
			 `xx_component_master`.`ComponentCode`,
			 `MedicaidNumber`,
			 `MedicareNumber`,
			 CONCAT(`cl_client_common`.`Address1`, IF(`cl_client_common`.`Address2` = '', '', CONCAT(', ', `cl_client_common`.`Address2`)))
				 AS `Address1`,
			 CONCAT(`cl_client_common`.`City`, ', ', `client_state`.`StateAbbrev`, ' ', `cl_client_common`.`ZipCode`)
				 AS `CityStateZip`,
			 CONCAT(`cl_client_common`.`PhoneNumber`, IF(`cl_client_common`.`PhoneNumber2` <> '', CONCAT('; ', `cl_client_common`.`PhoneNumber2`), ''))
				 AS `ClientPhone`,
			 `cl_client_common`.`PhoneNumber2` AS `ClientPhone2`,
			 `cl_client_common`.`DateOfBirth`,
			 `Gender`,
			 `cl_client_common`.`SocialSecurityNumber`,
			 `DDSNumber`,
			 LEFT(`xx_marital_status`.`Description`, 1) AS `MaritalStatus`,
			 LEFT(`xx_race_master`.`Description`, 1) AS `Race`,
			 `xx_legal_status`.`Description` AS `LegalStatus`,
			 CONCAT(`xx_sc_master`.`LastName`, ', ', `xx_sc_master`.`FirstName`) AS `ServiceCoordinator`,
			 `cl_clientsvcs_dayhab`.`AdmitDate`,
			 `cl_client_guardians`.`SelfGuardian`,
			 `xx_county_master`.`CountyName`,
			 CASE `Guardian1`.`NameFormat`
				 WHEN 1 THEN
					 CONCAT(`Guardian1`.`FirstName1`, ' ', `Guardian1`.`LastName1`)
				 WHEN 2 THEN
					 CONCAT(`Guardian1`.`FirstName1`, ' & ', `Guardian1`.`FirstName2`, ' ', `Guardian1`.`LastName1`)
				 WHEN 3 THEN
					 CONCAT(`Guardian1`.`FirstName1`, ' ', `Guardian1`.`LastName1`, ' & ', `Guardian1`.`FirstName2`,
									' ', `Guardian1`.`LastName2`)
			 END
				 AS `Guardian1Name`,
			 CONCAT(`Guardian1`.`Address1`, ', ',
							IF(`Guardian1`.`Address2` = '', '', CONCAT(`Guardian1`.`Address2`, ', ')), `Guardian1`.`City`,
							', ', `guardian1_state`.`StateAbbrev`,
							' ', `Guardian1`.`ZipCode`)
				 AS `Guardian1Address`,
			 `Guardian1`.`HomePhone1` AS `Guardian1Phone`,
			 CASE `Guardian1`.`NameFormat`
				 WHEN 1 THEN
					 `Guardian1`.`WorkPhone1`
				 ELSE
					 CONCAT(IF(`Guardian1`.`WorkPhone1` <> '', CONCAT(`Guardian1`.`FirstName1`, ': ', `Guardian1`.`WorkPhone1`), ''),
									IF(`Guardian1`.`WorkPhone1` <> ''
AND 								 `Guardian1`.`WorkPhone2` <> '',
										 '; ',
										 ''),
									IF(`Guardian1`.`WorkPhone2` <> '', CONCAT(`Guardian1`.`FirstName2`, ': ', `Guardian1`.`WorkPhone2`), ''))
			 END
				 AS `Guardian1WorkPhone`,
			 CASE `Guardian1`.`NameFormat`
				 WHEN 1 THEN
					 `Guardian1`.`CellPhone1`
				 ELSE
					 CONCAT(IF(`Guardian1`.`CellPhone1` <> '', CONCAT(`Guardian1`.`FirstName1`, ': ', `Guardian1`.`CellPhone1`), ''),
									IF(`Guardian1`.`CellPhone1` <> ''
AND 								 `Guardian1`.`CellPhone2` <> '',
										 '; ',
										 ''),
									IF(`Guardian1`.`CellPhone2` <> '', CONCAT(`Guardian1`.`FirstName2`, ': ', `Guardian1`.`CellPhone2`), ''))
			 END
				 AS `Guardian1Cell`,
			 CASE `Guardian1`.`NameFormat`
				 WHEN 1 THEN
					 `Guardian1`.`WorkPlace1`
				 ELSE
					 CONCAT(IF(`Guardian1`.`WorkPlace1` <> '', CONCAT(`Guardian1`.`FirstName1`, ': ', `Guardian1`.`WorkPlace1`), ''),
									IF(`Guardian1`.`WorkPlace1` <> ''
AND 								 `Guardian1`.`WorkPlace2` <> '',
										 '; ',
										 ''),
									IF(`Guardian1`.`WorkPlace2` <> '', CONCAT(`Guardian1`.`FirstName2`, ': ', `Guardian1`.`WorkPlace2`), ''))
			 END
				 AS `WorkPlace1`,
			 CASE `Guardian1`.`NameFormat`
				 WHEN 1 THEN
					 `Guardian1`.`WorkHours1`
				 ELSE
					 CONCAT(IF(`Guardian1`.`WorkHours1` <> '', CONCAT(`Guardian1`.`FirstName1`, ': ', `Guardian1`.`WorkHours1`), ''),
									IF(`Guardian1`.`WorkHours1` <> ''
AND 								 `Guardian1`.`WorkHours2` <> '',
										 '; ',
										 ''),
									IF(`Guardian1`.`WorkHours2` <> '', CONCAT(`Guardian1`.`FirstName2`, ': ', `Guardian1`.`WorkHours2`), ''))
			 END
				 AS `WorkHours1`,
			 `Guardian1Relation`.`Description` AS `Guardian1Relation`,
			 CASE `Guardian2`.`NameFormat`
				 WHEN 1 THEN
					 CONCAT(`Guardian2`.`FirstName1`, ' ', `Guardian2`.`LastName1`)
				 WHEN 2 THEN
					 CONCAT(`Guardian2`.`FirstName1`, ' & ', `Guardian2`.`FirstName2`, ' ', `Guardian2`.`LastName1`)
				 WHEN 3 THEN
					 CONCAT(`Guardian2`.`FirstName1`, ' ', `Guardian2`.`LastName1`, ' & ', `Guardian2`.`FirstName2`,
									' ', `Guardian2`.`LastName2`)
			 END
				 AS `Guardian2Name`,
			 CONCAT(`Guardian2`.`Address1`, ', ',
							IF(`Guardian2`.`Address2` = '', '', CONCAT(`Guardian2`.`Address2`, ', ')), `Guardian2`.`City`,
							', ', `guardian2_state`.`StateAbbrev`,
							' ', `Guardian2`.`ZipCode`)
				 AS `Guardian2Address`,
			 `Guardian2`.`HomePhone1` AS `Guardian2Phone`,
			 CASE `Guardian2`.`NameFormat`
				 WHEN 1 THEN
					 `Guardian2`.`WorkPhone1`
				 ELSE
					 CONCAT(IF(`Guardian2`.`WorkPhone1` <> '', CONCAT(`Guardian2`.`FirstName1`, ': ', `Guardian2`.`WorkPhone1`), ''),
									IF(`Guardian2`.`WorkPhone1` <> ''
AND 								 `Guardian2`.`WorkPhone2` <> '',
										 '; ',
										 ''),
									IF(`Guardian2`.`WorkPhone2` <> '', CONCAT(`Guardian2`.`FirstName2`, ': ', `Guardian2`.`WorkPhone2`), ''))
			 END
				 AS `Guardian2WorkPhone`,
			 CASE `Guardian2`.`NameFormat`
				 WHEN 1 THEN
					 `Guardian2`.`CellPhone1`
				 ELSE
					 CONCAT(IF(`Guardian2`.`CellPhone1` <> '', CONCAT(`Guardian2`.`FirstName1`, ': ', `Guardian2`.`CellPhone1`), ''),
									IF(`Guardian2`.`CellPhone1` <> ''
AND 								 `Guardian2`.`CellPhone2` <> '',
										 '; ',
										 ''),
									IF(`Guardian2`.`CellPhone2` <> '', CONCAT(`Guardian2`.`FirstName2`, ': ', `Guardian2`.`CellPhone2`), ''))
			 END
				 AS `Guardian2Cell`,
			 CASE `Guardian2`.`NameFormat`
				 WHEN 1 THEN
					 `Guardian2`.`WorkPlace1`
				 ELSE
					 CONCAT(IF(`Guardian2`.`WorkPlace1` <> '', CONCAT(`Guardian2`.`FirstName1`, ': ', `Guardian2`.`WorkPlace1`), ''),
									IF(`Guardian2`.`WorkPlace1` <> ''
AND 								 `Guardian2`.`WorkPlace2` <> '',
										 '; ',
										 ''),
									IF(`Guardian2`.`WorkPlace2` <> '', CONCAT(`Guardian2`.`FirstName2`, ': ', `Guardian2`.`WorkPlace2`), ''))
			 END
				 AS `WorkPlace11`,
			 CASE `Guardian2`.`NameFormat`
				 WHEN 1 THEN
					 `Guardian2`.`WorkHours1`
				 ELSE
					 CONCAT(IF(`Guardian2`.`WorkHours1` <> '', CONCAT(`Guardian2`.`FirstName1`, ': ', `Guardian2`.`WorkHours1`), ''),
									IF(`Guardian2`.`WorkHours1` <> ''
AND 								 `Guardian2`.`WorkHours2` <> '',
										 '; ',
										 ''),
									IF(`Guardian2`.`WorkHours2` <> '', CONCAT(`Guardian2`.`FirstName2`, ': ', `Guardian2`.`WorkHours2`), ''))
			 END
				 AS `WorkHours2`,
			 `Guardian2Relation`.`Description` AS `Guardian2Relation`,
			 IF(`cl_client_guardians`.`SurrogateNum` = 0, 'None', CONCAT(`xx_surrogate_master`.`LastName`, ', ', `xx_surrogate_master`.`FirstName`))
				 AS `SurrogateName`,
			 CONCAT(`xx_surrogate_master`.`Address1`,
							', ',
							IF(`xx_surrogate_master`.`Address2` = '', '', CONCAT(`xx_surrogate_master`.`Address2`, ', ')),
							`xx_surrogate_master`.`City`,
							', ',
							`guardian1_state`.`StateAbbrev`,
							' ',
							`xx_surrogate_master`.`ZipCode`)
				 AS `SurrogateAddress`,
			 `xx_surrogate_master`.`Phone1` AS `SurrogatePhone`,
			 `PHC`.`Description` AS `PrimaryHandiCond`,
			 `SHC`.`Description` AS `SecondaryHandiCond`,
			 CASE `cl_client_medical`.`Seizures`
				 WHEN 'Y' THEN 'Yes'
				 WHEN 'N' THEN 'No'
				 WHEN 'R' THEN 'At Risk'
			 END
				 AS `Seizures`,
			 `xx_language_master`.`Description` AS `PrimaryLanguage`,
			 `cl_client_medical`.`Allergies`,
			 `cl_client_medical`.`Equipment`,
			 `cl_client_medical`.`Medications`,
			 CONCAT(CONVERT(`SpecialDiet`, CHAR),
							IF(CHAR_LENGTH(`SpecialDiet`) > 0, '; ', ''),
							IF(`TubeFed` = 0, '', 'Tube Fed'),
							IF(`TubeFed` <> 0, IF(`TFServedMeals` = 0, '-Not Served Meals; ', '-Served Meals; '), ''),
							IF(`SDBottleFeed` = 1, 'Bottle Feed; ', ''),
							IF(`SDPureed` = 1, 'Pureed; ', ''),
							IF(`SDChopped` = 1, 'Chopped; ', ''),
							IF(`SDGround` = 1, 'Ground; ', ''),
							IF(`SDPureedChopped` = 1, 'Half Pureed/Half Chopped; ', ''),
							IF(`SDHalfPureedGround` = 1, 'Half Pureed/Half Ground; ', ''),
							IF(`LiquidConsistancy` = '',
								 '',
								 CONCAT('Liquid: ',
												CASE `LiquidConsistancy`
													WHEN 'T' THEN 'Thin; '
													WHEN 'N' THEN 'Nectar; '
													WHEN 'H' THEN 'Honey; '
													WHEN 'P' THEN 'Pudding; '
													WHEN 'E' THEN 'Nectar Plus; '
												END)))
				 AS `SpecialDiet`,
			 IF(`ThickenerUsed` = 'N',
					'',
					CONCAT('Thickener Used: ',
								 CASE `ThickenerUsed`
									 WHEN 'S' THEN 'Simply Thick'
									 WHEN 'R' THEN 'Rice Cereal'
									 WHEN 'T' THEN 'Thick It'
									 WHEN 'O' THEN CONCAT('Other: ', `ThickenerUsedOther`)
								 END,
								 IF(`AmountMethod` = ''
OR									 `AmountMethod` IS NULL,
										'',
										CONCAT(' Amount/Method: ', `AmountMethod`))))
				 AS `ThickenerUsedInfo`,
			 `cl_client_dietary`.`Reason`,
			 `cl_client_dietary`.`DietLimitations`,
			 IF(`cl_client_medical`.`PrimaryPhysicianNum` = 0, 'None', CONCAT(`primaryphysician`.`LastName`, ', ', `primaryphysician`.`FirstName`))
				 AS `PrimaryPhysicianName`,
			 CONCAT(`primaryphysician`.`Address1`,
							', ',
							IF(`primaryphysician`.`Address2` = '', '', CONCAT(`primaryphysician`.`Address2`, ', ')),
							`primaryphysician`.`City`,
							', ',
							`primaryphysician_state`.`StateAbbrev`,
							' ',
							`primaryphysician`.`ZipCode`)
				 AS `PrimaryPhysicianAddress`,
			 `primaryphysician`.`Phone1` AS `PrimaryPhysicianPhone`,
			 IF(`cl_client_medical`.`SecondPhysicianNum` = 0, 'None', CONCAT(`secondphysician`.`LastName`, ', ', `secondphysician`.`FirstName`))
				 AS `secondphysicianName`,
			 CONCAT(`secondphysician`.`Address1`,
							', ',
							IF(`secondphysician`.`Address2` = '', '', CONCAT(`secondphysician`.`Address2`, ', ')),
							`secondphysician`.`City`,
							', ',
							`secondphysician_state`.`StateAbbrev`,
							' ',
							`secondphysician`.`ZipCode`)
				 AS `secondphysicianAddress`,
			 `secondphysician`.`Phone1` AS `SecondPhysicianPhone`,
			 IF(`cl_client_medical`.`HospitalNum` = 0, 'None', `xx_hospital_master`.`HospitalName`) AS `HospitalName`,
			 `xx_hospital_master`.`Phone1` AS `HospitalPhone`,
			 CASE `emergencycontact`.`NameFormat`
				 WHEN 1 THEN
					 CONCAT(`emergencycontact`.`FirstName1`, ' ', `emergencycontact`.`LastName1`)
				 WHEN 2 THEN
					 CONCAT(`emergencycontact`.`FirstName1`, ' & ', `emergencycontact`.`FirstName2`, ' ', `emergencycontact`.`LastName1`)
				 WHEN 3 THEN
					 CONCAT(`emergencycontact`.`FirstName1`, ' ', `emergencycontact`.`LastName1`, ' & ',
									`emergencycontact`.`FirstName2`, ' ', `emergencycontact`.`LastName2`)
			 END
				 AS `EmergencyName`,
			 CONCAT(`emergencycontact`.`Address1`,
							', ',
							IF(`emergencycontact`.`Address2` = '', '', CONCAT(`emergencycontact`.`Address2`, ', ')),
							`emergencycontact`.`City`,
							', ',
							`emergency_state`.`StateAbbrev`,
							' ',
							`emergencycontact`.`ZipCode`)
				 AS `EmergencyAddress`,
			 CONCAT(IF(`emergencycontact`.`HomePhone1` = '', '', CONCAT('Home:', `emergencycontact`.`HomePhone1`, ' ')), IF(`emergencycontact`.`WorkPhone1` = '', '', CONCAT('Work:', `emergencycontact`.`WorkPhone1`, ' ')), IF(`emergencycontact`.`CellPhone1` = '', '', CONCAT('Cell:', `emergencycontact`.`CellPhone1`)))
				 AS `EmergPhoneNumbers1`,
			 CONCAT(IF(`emergencycontact`.`HomePhone2` = '', '', CONCAT('Home:', `emergencycontact`.`HomePhone2`, ' ')), IF(`emergencycontact`.`WorkPhone2` = '', '', CONCAT('Work:', `emergencycontact`.`WorkPhone2`, ' ')), IF(`emergencycontact`.`CellPhone2` = '', '', CONCAT('Cell:', `emergencycontact`.`CellPhone2`)))
				 AS `EmergPhoneNumbers2`,
			 `emergencyrelation`.`Description` AS `EmergencyRelation`,
			 CASE `cl_clientsvcs_extcare`.`AttendsEC` WHEN 1 THEN 'Yes' ELSE 'No' END AS `ExtendedCare`,
			 CONCAT(`xx_component_master`.`Description`, IF(`xx_classroom_master`.`ClassroomName` IS NULL, '', CONCAT('/', `xx_classroom_master`.`ClassroomName`)))
				 AS `ComponentClassroomBreak`,
			 CAST(CONCAT(`cl_client_common`.`LastName`, `cl_client_common`.`FirstName`, `cl_client_common`.`MiddleName`, `cl_client_common`.`UniqueNumber-900`) AS CHAR)
				 AS `ClientNameBreak`,
			 `HabFunding`.`Description` AS `HabFunding`,
			 'BRIANR' AS `PrintedBy`
FROM`cl_client_common`
 LEFT JOIN `cl_clientsvcs_dayhab` AS `cl_clientsvcs_dayhab`
	 ON `cl_clientsvcs_dayhab`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
AND 	`cl_clientsvcs_dayhab`.`ServicesLocale` = 'HS'
AND 	`cl_clientsvcs_dayhab`.`CurrentRecord` = 1
AND 	`cl_clientsvcs_dayhab`.`AdmitDate` IS NOT NULL
AND 	(`cl_clientsvcs_dayhab`.`DischargeDate` IS NULL
OR			`cl_clientsvcs_dayhab`.`DischargeDate` > '2010-09-24')
 LEFT JOIN `xx_component_master`
	 ON `xx_component_master`.`UniqueNumber-003` = `cl_clientsvcs_dayhab`.`ComponentNum`
 LEFT JOIN `xx_classroom_master`
	 ON `xx_classroom_master`.`UniqueNumber-037` = `cl_clientsvcs_dayhab`.`ClassroomNum`
 LEFT JOIN `xx_state_master` AS `client_state`
	 ON `client_state`.`UniqueNumber-030` = `cl_client_common`.`StateNum`
 LEFT JOIN `xx_marital_status`
	 ON `xx_marital_status`.`UniqueNumber-015` = `cl_client_common`.`MaritalStatusNumber`
 LEFT JOIN `xx_legal_status`
	 ON `xx_legal_status`.`UniqueNumber-012` = `cl_client_common`.`LegalStatusNumber`
 LEFT JOIN `xx_sc_master`
	 ON `xx_sc_master`.`UniqueNumber-024` = `cl_client_common`.`SCNumber`
 LEFT JOIN `xx_race_master`
	 ON `xx_race_master`.`UniqueNumber-019` = `cl_client_common`.`RaceNumber`
 LEFT JOIN `cl_client_guardians`
	 ON `cl_client_guardians`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
 LEFT JOIN `xx_people_master` AS `Guardian1`
	 ON `Guardian1`.`UniqueNumber-036` = `cl_client_guardians`.`Guardian1Num`
 LEFT JOIN `xx_state_master` AS `guardian1_state`
	 ON `guardian1_state`.`UniqueNumber-030` = `Guardian1`.`StateNum`
 LEFT JOIN `xx_relation_master` AS `Guardian1Relation`
	 ON `Guardian1Relation`.`UniqueNumber-023` = `cl_client_guardians`.`Guardian1RelationNum`
 LEFT JOIN `xx_people_master` AS `Guardian2`
	 ON `Guardian2`.`UniqueNumber-036` = `cl_client_guardians`.`Guardian2Num`
 LEFT JOIN `xx_state_master` AS `guardian2_state`
	 ON `guardian2_state`.`UniqueNumber-030` = `Guardian2`.`StateNum`
 LEFT JOIN `xx_relation_master` AS `Guardian2Relation`
	 ON `Guardian2Relation`.`UniqueNumber-023` = `cl_client_guardians`.`Guardian2RelationNum`
 LEFT JOIN `xx_surrogate_master`
	 ON `xx_surrogate_master`.`UniqueNumber-031` = `cl_client_guardians`.`SurrogateNum`
 LEFT JOIN `xx_state_master` AS `surrogate_state`
	 ON `surrogate_state`.`UniqueNumber-030` = `xx_surrogate_master`.`StateNum`
 LEFT JOIN `cl_client_medical`
	 ON `cl_client_medical`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
 LEFT JOIN `xx_hcondition_master` AS `PHC`
	 ON `PHC`.`UniqueNumber-009` = `cl_client_medical`.`PrimaryHandiCondNum`
 LEFT JOIN `xx_hcondition_master` AS `SHC`
	 ON `SHC`.`UniqueNumber-009` = `cl_client_medical`.`SecondHandiCondNum`
 LEFT JOIN `xx_language_master`
	 ON `xx_language_master`.`UniqueNumber-011` = `cl_client_common`.`LanguageNumber`
 LEFT JOIN `cl_client_dietary`
	 ON `cl_client_dietary`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
 LEFT JOIN `xx_physician_master` AS `primaryphysician`
	 ON `primaryphysician`.`UniqueNumber-017` = `cl_client_medical`.`PrimaryPhysicianNum`
 LEFT JOIN `xx_state_master` AS `primaryphysician_state`
	 ON `primaryphysician_state`.`UniqueNumber-030` = `primaryphysician`.`StateNum`
 LEFT JOIN `xx_physician_master` AS `secondphysician`
	 ON `secondphysician`.`UniqueNumber-017` = `cl_client_medical`.`SecondPhysicianNum`
 LEFT JOIN `xx_state_master` AS `secondphysician_state`
	 ON `secondphysician_state`.`UniqueNumber-030` = `secondphysician`.`StateNum`
 LEFT JOIN `xx_hospital_master`
	 ON `xx_hospital_master`.`UniqueNumber-010` = `cl_client_medical`.`HospitalNum`
 LEFT JOIN `cl_client_contacts`
	 ON `cl_client_contacts`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
 LEFT JOIN `xx_people_master` AS `emergencycontact`
	 ON `emergencycontact`.`UniqueNumber-036` = `cl_client_contacts`.`ContactNum`
 LEFT JOIN `xx_relation_master` AS `emergencyrelation`
	 ON `emergencyrelation`.`UniquenUmber-023` = `cl_client_contacts`.`RelationNum`
 LEFT JOIN `xx_state_master` AS `emergency_state`
	 ON `emergency_state`.`UniqueNumber-030` = `emergencycontact`.`StateNum`
 LEFT JOIN `cl_clientsvcs_extcare`
	 ON `cl_clientsvcs_extcare`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
 LEFT JOIN `xx_fundingcode_master` AS `HabFunding`
	 ON `HabFunding`.`UniqueNumber-008` = `cl_clientsvcs_dayhab`.`FundingNum`
 LEFT JOIN `xx_county_master`
	 ON `xx_county_master`.`UniqueNumber-004` = `cl_client_common`.`CountyOfResidence`
WHERE `cl_clientsvcs_dayhab`.`ServicesLocale` = 'HS'
AND 	`cl_clientsvcs_dayhab`.`AdmitDate` IS NOT NULL
AND 	(`cl_clientsvcs_dayhab`.`DischargeDate` IS NULL
OR			`cl_clientsvcs_dayhab`.`DischargeDate` > '2010-09-24')
AND 	`cl_clientsvcs_dayhab`.`CurrentRecord` = 1
AND 	`cl_clientsvcs_dayhab`.`ComponentNum` IN (16)
AND 	(`cl_clientsvcs_dayhab`.`ClassroomNum` = 0
OR			`cl_clientsvcs_dayhab`.`ClassroomNum` IN
					(7, 69, 12, 13, 15, 17, 18, 70, 6, 71, 11, 72, 16, 5, 14, 2, 3, 4, 8, 9, 10, 61, 50))
GROUP BY `ClientName`, `cl_client_common`.`UniqueNumber-900`, `cl_client_contacts`.`ContactNum`
ORDER BY `ClientName`, `cl_client_contacts`.`EmergencyPriority`

Humble Programmer

Luc Pattyn24-Sep-10 11:29

Luc Pattyn

24-Sep-10 11:29

Do you really expect anyone to read and study all that, while you are not even providing a decent description of what it does, what it is about, why you think it should run faster, and what you tried to get it any faster?

Unsure | :~

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

programmervb.netc++24-Sep-10 11:35

24-Sep-10 11:35

Actually it runs fine.
There was a post in the lounge regarding this particular statement and rather than post it in the lounge I posted it here and linked it.
What is does is get information for a facesheet which is basically all information on a client.
Like I said performance is not a problem there was just a conversation about the code being incorrect if it was so many lines of code.

Humble Programmer

Mycroft Holmes24-Sep-10 15:32

24-Sep-10 15:32

Create a view so no other poor bugger has to look at this code again.

I would probably break it into a number of different procs servicing different parts of the profile. Supporting this query would be nasty.

What happens when the dietician want the name and diet details, does he get all of the face sheet.

Never underestimate the power of human stupidity
RAH

programmervb.netc++24-Sep-10 19:09

24-Sep-10 19:09

In the case of the dietician needs info we would generally do that on a different report with only the information needed by them.
I do think that breaking the SQL out to bring in only parts of the profile is a good idea then we could more easily reuse them.
However a concern of mine in doing that would be that if we have to tie all of these back together would that not be a pretty big performance hit?
I would think you would have to do that with multiple statements which is in turn more network traffic.
We try to do as much as we can with minimal hits to our DB because we have some users that are on very slow connections.
Thanks for the ideas and look forward to more.

Humble Programmer

programmervb.netc++27-Sep-10 13:50

27-Sep-10 13:50

I do have another question.
As you can see we try to do as much logic as possible in the SQL so that we do not have do manipulate the dataset once it is returned....
Because from my understanding the server will be able to optimize much more efficiently than you will most likely be able to do in your program.
For example rather than retrieve first and last name then concat as needed for the result just write it into the SQL so that your result set will already be correct.

Humble Programmer

Mycroft Holmes27-Sep-10 15:45

27-Sep-10 15:45

programmervb.netc++ wrote:
first and last name then concat as needed for the result just write it into the SQL

That is definitely reasonable, however you need to make a judgment call on how much formatting you do in the query. EG you may note the guy who wants to add CRLF to the data in the query, this is totally invalid whereas concat of the names is good.

It also depends on the volume of data you are delivering, if you are doing this profile for 1 patient then this query is almost valid (I would still split it out for support reasons) doing this for 1000s is not valid.

Never underestimate the power of human stupidity
RAH

programmervb.netc++29-Sep-10 18:35

29-Sep-10 18:35

Why would the number of clients really matter if your indexes are correct?
I understand that the record set has an affect on total time but I don't see how that really relates to a CONCAT for example.
If you are going to have to CONCAT last and first name on 1000 clients what benefit would you have from doing it locally instead of the server?
Also in either case the concatenation I think the concatenation would be happening in memory so in theory the server should have more...
and I would think that the CONCAT statement written in my case mySQL is probably more efficient than the equivalent code in .NET.
Thanks for your thoughts I do appreciate your input just want to have a full understanding of your reasoning.

Humble Programmer