Solved

Faking a field for a real field of a specific length

Posted on 2007-03-29
5
161 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
  • 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 34

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 34

Expert Comment

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

 James
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now