Solved

Faking a field for a real field of a specific length

Posted on 2007-03-29
5
166 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

710 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