| Author |
Topic |
|
smccreadie
Aged Yak Warrior
505 Posts |
 Posted - 2002-08-21 : 13:46:31
|
In a SPROC, I assign several variables as such: DECLARE @pkg_size1 varchar(10)SELECT @pkg_size1 = pkg_size from TBL_DRUGS where drug_code = @initialdrugcode Occasionally, there will be more than one row in TBL_DRUGS that meets the criteria. The difference between the rows are some other values - pkg_size will always be the same. I seem to be getting errors in some of the processing whenever more than one row exists.How do I specify to get the value from only one row? Basically I want to do something like:SELECT @pkg_size1 = TOP 1 pkg_size from TBL_DRUGS where drug_code = @initialdrugcode but that throws a syntax error. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-08-21 : 14:00:01
|
| I think this should still work, your variable wil just be set to the value in the last row.However, you should be able to accomplish what you want by using:SELECT @pkg_size1 = (select TOP 1 pkg_size from TBL_DRUGS where drug_code = @initialdrugcode)orSELECT @pkg_size1 = (select distinct pkg_size from TBL_DRUGS where drug_code = @initialdrugcode) -Chad |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-08-21 : 15:05:05
|
| Scott,I know you know this, so I'm not trying to lecture, but for the casual reader... This problem suggests an underlying problem such as the database is not completely normalized (getting multiple rows with identical values). Especially if the pkg_size value is ALWAYS the same for every drug_code. Depending on the needs, it could be argued this is best for them, but just note that it wavers from the "ideal".Another option is that the query needs additional criteria (an AND statement) in the WHERE clause to actually return only one row instead of multiple rows.Edited by - ajarnmark on 08/21/2002 15:06:17 |
|
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-08-22 : 07:02:46
|
quote:
Scott,I know you know this, so I'm not trying to lecture, but for the casual reader... This problem suggests an underlying problem such as the database is not completely normalized (getting multiple rows with identical values). Especially if the pkg_size value is ALWAYS the same for every drug_code. Depending on the needs, it could be argued this is best for them, but just note that it wavers from the "ideal".Another option is that the query needs additional criteria (an AND statement) in the WHERE clause to actually return only one row instead of multiple rows.Edited by - ajarnmark on 08/21/2002 15:06:17
Actually the database is normalized. It's just that multiple active products are available for each master record (i.e. 2 manufacturers). The application doesn't really care about the differences in manufacturers because the pkg_size will always be the same for each code. Adding an AND to the where clause would increase the complexity of the app as people would have to pick the drug and manufacturer (which the users wouldn't know or care).I agree with you generally - keeping the database normalized is important. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-08-22 : 10:19:07
|
it's not normalized IMHO ... well from what you told us ... the items are the same just different manufactures ... well then you should have one item and another table that relates items to manufacutres ... that would be normalized in my opinion in this case from the information provided  |
|
|
|
Teroman
Posting Yak Master
115 Posts |
Posted - 2002-08-22 : 11:52:22
|
| regardless of normalisation, i thnk the syntax you want isSELECT top 1 @pkg_size1 = pkg_size from TBL_DRUGS where drug_code = @initialdrugcodeon the normalisation issue, i always think a good approach is to have one giant table, with say 4 columns indicating the record type, and the 50 char(100) ones to hold the data. Good thing is that now if you have a new entity you dont have to mess about designing a new table, just think up some new record type indicators, and bingo, you database structure is fine.Another good reason is that you dont have to fool about with nasty complex conceptual data models with lines crossing all over the shop. They are confusing to look at, and a nice simple page with one big entity in the middle is much more astetically pleasing.col |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-08-22 : 16:30:49
|
quote:
on the normalisation issue, i always think a good approach is to have one giant table, with say 4 columns indicating the record type, and the 50 char(100) ones to hold the data.
Teroman the Troublemaker... For about 1/2 second I thought you were being serious... The "I'm getting a migraine" groan was starting to form in my lungs. |
|
|
|
Teroman
Posting Yak Master
115 Posts |
Posted - 2002-08-23 : 04:36:47
|
Always happy to help  |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-08-23 : 05:38:07
|
quote:
on the normalisation issue, i always think a good approach is to have one giant table, with say 4 columns indicating the record type, and the 50 char(100) ones to hold the data. Good thing is that now if you have a new entity you dont have to mess about designing a new table, just think up some new record type indicators, and bingo, you database structure is fine.
I can see the Ad..."EXCEL... for the truly evolving data model"DavidM"SQL-3 is an abomination.." |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-08-23 : 08:05:23
|
| I would NEVER support such an approach ... the bastard DBA that did that to me here with an application. I have since removed that type of lookup table. Most of my lookup tables is just an IDENTITY column and a text value that I can change on the fly and have the application grab the new name without doing a huge update on millions of records. You could do this with a lookup table... its just sloppy and lazy designing on the DBA's part. IMHO that is anyway; from past experiences. |
|
|
|
|
Assigning variables in a SPROC