Faking a field for a real field of a specific length

SQL Sever Enterprize v 8

When looking at a table properties and it says Column name, data type and length....if the length is set for 50....does that mean it has 50 spaces in there?  

I'm trying to "fake" a field name by doing a:
        '                                                  ' AS Board_Name,  
The real one has a length set for 50.  What I'm hoping is that if I put 50 blank spaces its the same as saying its length is 50.  Does that make sense?

I'm also doing this because my 4th table that i've added is causing my to loose some rows.  



rortiz77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
well it depends on the data type ...

but if its Character or Varchar then it could hold 50 characters....

but if its character then you only need to specify a single space as it will be padded out to the maximum length..
if its varchar then it will only store a string as long as you pass...

you can use SPACE(50) to specify a 50 character sting...


>>m also doing this because my 4th table that i've added is causing my to loose some rows   ??


please post your sql statement...
0
rortiz77Author Commented:
Ok, its a varchar =50.  Below is my query.  The 4th row is called Med_Prof_Specialties and I'm using the Board_name field.  When adding that table and doing an inner join with Order_ it looks fine except some physicians are still missing.  Without doing a join with Order_ I get TONS of duplicate rows.  I only get an accurate number of physicians when I'm not using the Med_Prof_Specialties table.  

SELECT              Med_Prof.Last_name,
                              Med_Prof.First_name,
                              Med_Prof.Middle_initial,
                              RIGHT(Med_Prof.ID_number, 6) AS Admit_Num,
                              '               ' AS Expr1,
                              Med_Prof.Degree,
                              Med_Prof_Facilities.Expertise,
                              Med_Prof_Specialties.Board_name,
                    Med_Prof.Languages_spoken,
                    Med_Prof_Offices.Phone_number_1,
                    Med_Prof_Offices.Fax_number,
                    Med_Prof_Offices.Address_1,
                    Med_Prof_Offices.Address_2,
                    Med_Prof_Offices.City,
                    Med_Prof_Offices.State,
                    Med_Prof_Offices.Zip_Code,
                    'Physician - Non FH ' AS [Physician - Non FH]
FROM         Med_Prof INNER JOIN
                      Med_Prof_Offices ON Med_Prof.Med_Prof_Record_No =
                      Med_Prof_Offices.Med_Prof_Record_No INNER JOIN
                      Med_Prof_Facilities ON Med_Prof.Med_Prof_Record_No =
                      Med_Prof_Facilities.Med_Prof_Record_No INNER JOIN
                      Med_Prof_Specialties ON Med_Prof.Med_Prof_Record_No =
                      Med_Prof_Specialties.Med_Prof_Record_No AND
                      Med_Prof_Facilities.Order_ = Med_Prof_Specialties.Order_ AND
                      Med_Prof_Offices.Order_ = Med_Prof_Specialties.Order_
WHERE     (Med_Prof_Facilities.Current_status = 'fellow' OR
                      Med_Prof_Facilities.Current_status = 'active' OR
                      Med_Prof_Facilities.Current_status = 'consulting' OR
                      Med_Prof_Facilities.Current_status = 'courtesy' OR
                      Med_Prof_Facilities.Current_status = 'fellow/pa' OR
                      Med_Prof_Facilities.Current_status = 'provisional' OR
                      Med_Prof_Facilities.Current_status = 'prov-special active' OR
                      Med_Prof_Facilities.Current_status = 'provisional-active' OR
                      Med_Prof_Facilities.Current_status = 'provisional-courtesy' OR
                      Med_Prof_Facilities.Current_status = 'senior attending' OR
                      Med_Prof_Facilities.Current_status = 'special active' OR
                      Med_Prof_Facilities.Current_status = 'courtesy' OR
                      Med_Prof_Facilities.Current_status LIKE 'prov-cons%' OR
                      Med_Prof_Facilities.Current_status LIKE 'temporary' OR
                      Med_Prof_Facilities.Current_status LIKE 'acad%' OR
                      Med_Prof_Facilities.Current_status LIKE 'prov%' OR
                      Med_Prof_Facilities.Current_status LIKE 'spec%') AND
                      (Med_Prof_Facilities.FacCode = 'fhorland01') AND
                      (Med_Prof_Offices.Primary_office = 1) AND
                      (Med_Prof.Degree = 'md' OR
                      Med_Prof.Degree = 'DDS MD' OR
                      Med_Prof.Degree = 'dds, md' OR
                      Med_Prof.Degree = 'md, dds' OR
                      Med_Prof.Degree = 'do' OR
                      Med_Prof.Degree = 'phd' OR
                      Med_Prof.Degree = 'psyd' OR
                      Med_Prof.Degree = 'edd' OR
                      Med_Prof.Degree = 'dpm' OR
                      Med_Prof.Degree = 'dds' OR
                      Med_Prof.Degree = 'DDS, MD' OR
                      Med_Prof.Degree = 'dmd' OR
                      Med_Prof.Degree = 'ph,d,.md' OR
                      Med_Prof.Degree = 'ph.d')
ORDER BY Med_Prof.Last_name, Med_Prof.First_name
0
James0628Commented:
Should every entry in Med_Prof have an entry in Med_Prof_Specialties?  If not, you probably need to change the INNER JOIN to a LEFT OUTER JOIN.  Then if there is no corresponding entry in Med_Prof_Specialties, any fields from that table (eg. Board_name) will be null.

 What is Order_?  Does the value in Med_Prof_Specialties have to match the values in both Med_Prof_Facilities and Med_Prof_Offices?  Maybe the value in one of those is sometimes different and you should only be checking one or the other?


 This doesn't appear to be related to your problem, but, FWIW, you could simplify your WHERE considerably by replacing the tests for various specific values with IN's.  For example:

 Med_Prof_Facilities.Current_status = 'fellow' OR
 Med_Prof_Facilities.Current_status = 'active' OR
 Med_Prof_Facilities.Current_status = 'consulting' OR
  and so on

 becomes

 Med_Prof_Facilities.Current_status IN ( 'fellow', 'active', 'consulting', <and so on> )

 Same thing for the Med_Prof.Degree tests.

 You've also got some overlap in your LIKE tests.
 Since you're testing for Med_Prof_Facilities.Current_status LIKE 'prov%', you don't need to test for = 'provisional', 'prov-special active', etc., or for LIKE 'prov-cons%'.  Those would all be caught by LIKE 'prov%'.
 And the test for LIKE 'spec%' would catch = 'special active'.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rortiz77Author Commented:
Holy crap...you rock!  I knew it was some sort of a join issue...just didnt know where.  Thanks James0628!!!
0
James0628Commented:
You're welcome.  Glad I could help.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.