Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Faking a field for a real field of a specific length

Posted on 2007-03-29
5
Medium Priority
?
169 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 400 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

715 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