Solved

Faking a field for a real field of a specific length

Posted on 2007-03-29
5
168 Views
Last Modified: 2013-11-30
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.  



0
Comment
Question by:rortiz77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18819709
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
 

Author Comment

by:rortiz77
ID: 18819784
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
 
LVL 35

Accepted Solution

by:
James0628 earned 100 total points
ID: 18821065
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
 

Author Comment

by:rortiz77
ID: 18823003
Holy crap...you rock!  I knew it was some sort of a join issue...just didnt know where.  Thanks James0628!!!
0
 
LVL 35

Expert Comment

by:James0628
ID: 18826886
You're welcome.  Glad I could help.

 James
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question