locked
Using left function in sql statement RRS feed

  • Question

  • Hi

    I have written a sql query, that display the Gender field as "Male" or "Female".
    However, I need just the 1st letter.
    I have tried using the left function, but it didn't work.
    How do I do this? Below is my sql:

    SELECT CONVERT(varchar, getdate(), 101) AS 'File Date', CON.new_MembershipNumber AS 'Member ID', CON.AccountIdName AS 'Company', CON.FirstName AS 'First Name', CON.MiddleName AS 'Middle Name', CON.LastName AS 'Last Name', CONVERT(varchar,CON.BirthDate,101) AS 'DOB', (select value from stringmap where attributename = 'gendercode' and attributevalue = CON.GenderCode and objecttypecode = 2 and langid = 1033) AS 'Gender'

    FROM dbo.Contact CON
    CON.StatusCode = 1

    Tuesday, July 9, 2013 3:02 PM

Answers

  • Hi, 

    You could change the column 'select value' in your subselect to 'select LEFT(value,1)' directly in the subselect. Don't wrap the complete subselect.

    This should give you the first character of "Male" or "Female" if the attribute is set. Otherwise you get NULL.

    But you can also write the query completely without subselect :)

    I find it much easier to read and maintain and because you use conditions in your subquery it could be faster:

    DECLARE @WC_Fee FLOAT(6)
    SET     @WC_Fee = '0.00'
    
             SELECT CONVERT(varchar, getdate(), 101)         AS 'File Date'
                  , CON.new_MembershipNumber                 AS 'Member ID'
                  , CON.AccountIdName                        AS 'Company'
                  , CON.FirstName                            AS 'First Name'
                  , CON.MiddleName                           AS 'Middle Name'
                  , CON.LastName	                         AS 'Last Name'
                  , CONVERT(varchar,CON.BirthDate,101)       AS 'DOB'
                  , LEFT(STG.Value,1)                        AS 'Gender'
                  , CON.new_VIPUserType                      AS 'OLD User Type'
                  , CON.EMailAddress1                        AS 'E-mail'
                  , CON.FullName                             AS 'Member Name'
                  , @WC_Fee                                  AS 'WC Fee'
                  , STC.Value                                AS 'Plan Type'
                  , CON.new_VIPProductName                   AS 'OLD Product Name'
                  , CONVERT(varchar,CON.new_MemberSince,101) AS 'Effective Date'
                  , STA.Value                                AS 'Location'
      
               FROM dbo.Contact CON
    
    LEFT OUTER JOIN StringMap STG
                 ON           STG.attributename  = 'gendercode' 
                AND           STG.attributevalue = CON.GenderCode 
                AND           STG.objecttypecode = 2 
                AND           STG.langid = 1033
    
    LEFT OUTER JOIN StringMap STC
                 ON           STC.attributename  = 'new_coveragetype' 
                AND           STC.attributevalue = CON.new_CoverageType
                AND           STC.objecttypecode = 2 
                AND           STC.langid = 1033
    
    LEFT OUTER JOIN StringMap STA
                 ON           STA.attributename  = 'new_address1_province' 
                AND           STA.attributevalue = CON.new_address1_province
                AND           STA.objecttypecode = 10007 
                AND           STA.langid = 1033
    
              WHERE CON.StatusCode = 1

    Best regards

    Steve


    Steve Sämmang, Vienna, Austria
    Blog: xrm.io Website: simplic.at

    • Marked as answer by katiemackie Tuesday, July 9, 2013 9:09 PM
    Tuesday, July 9, 2013 8:26 PM
  • Awesome Steve

    Thanks so much.

    • Marked as answer by katiemackie Tuesday, July 9, 2013 8:49 PM
    Tuesday, July 9, 2013 8:49 PM

All replies

  • Hello,

    Try the following statement. It works in my environment. Also there is no need for using a subselect.

    SELECT CONVERT(varchar, getdate(), 101)	 AS 'File Date',
    CON.new_MembershipNumber			           AS 'Member ID',
    CON.AccountIdName				                 AS 'Company',
    CON.FirstName					                   AS 'First Name',
    CON.MiddleName					                 AS 'Middle Name',
    CON.LastName					                   AS 'Last Name',
    CONVERT(varchar,CON.BirthDate,101)		   AS 'DOB',
    LEFT(STM.Value,1)                        AS 'Gender'
           
    FROM dbo.Contact CON
    
    LEFT OUTER JOIN StringMap STM
    ON STM.attributename  = 'gendercode' 
    AND STM.attributevalue = CON.GenderCode 
    AND STM.objecttypecode = 2 
    AND STM.langid = 1033
    
    WHERE CON.StatusCode = 1

    Best regards

    Steve


    Steve Sämmang, Vienna, Austria
    Blog: xrm.io Website: simplic.at

    Tuesday, July 9, 2013 3:12 PM
  • Hi

    Thanks for answering...
    I should have given you the entire sql statement..it's not working for me because of that ....

    DECLARE @WC_Fee FLOAT(6)
    SET     @WC_Fee = '0.00'
    
    SELECT 
    CONVERT(varchar, getdate(), 101)	     	AS 'File Date',
    CON.new_MembershipNumber			AS 'Member ID',
    CON.AccountIdName				AS 'Company',
    CON.FirstName					AS 'First Name',
    CON.MiddleName				 	AS 'Middle Name',
    CON.LastName				 	AS 'Last Name',
    CONVERT(varchar,CON.BirthDate,101)		AS 'DOB',
       
    (select value from stringmap
     where attributename  = 'gendercode' and
           attributevalue = CON.GenderCode and
           objecttypecode = 2 and
           langid = 1033)                    AS 'Gender',
    
    CON.new_VIPUserType			 AS 'OLD User Type',
    CON.EMailAddress1			 AS 'E-mail',
    CON.FullName				 AS 'Member Name',
    @WC_Fee                                  AS 'WC Fee',
    
    (select value from stringmap
     where attributename = 'new_coveragetype' and
           attributevalue = CON.new_CoverageType and
           objecttypecode = 2 and
           langid = 1033)                    AS 'Plan Type',
    
    CON.new_VIPProductName			 AS 'OLD Product Name',
    CONVERT(varchar,CON.new_MemberSince,101) AS 'Effective Date',
    
    (select value from stringmap
     where attributename = 'new_address1_province' and
           attributevalue = CON.new_address1_province and
           objecttypecode = 10007 and
           langid = 1033)         AS 'Location'
      
    FROM	 dbo.Contact CON
    WHERE    CON.StatusCode	 = 1


    • Edited by katiemackie Tuesday, July 9, 2013 6:05 PM typing error
    Tuesday, July 9, 2013 6:03 PM
  • Hi, 

    You could change the column 'select value' in your subselect to 'select LEFT(value,1)' directly in the subselect. Don't wrap the complete subselect.

    This should give you the first character of "Male" or "Female" if the attribute is set. Otherwise you get NULL.

    But you can also write the query completely without subselect :)

    I find it much easier to read and maintain and because you use conditions in your subquery it could be faster:

    DECLARE @WC_Fee FLOAT(6)
    SET     @WC_Fee = '0.00'
    
             SELECT CONVERT(varchar, getdate(), 101)         AS 'File Date'
                  , CON.new_MembershipNumber                 AS 'Member ID'
                  , CON.AccountIdName                        AS 'Company'
                  , CON.FirstName                            AS 'First Name'
                  , CON.MiddleName                           AS 'Middle Name'
                  , CON.LastName	                         AS 'Last Name'
                  , CONVERT(varchar,CON.BirthDate,101)       AS 'DOB'
                  , LEFT(STG.Value,1)                        AS 'Gender'
                  , CON.new_VIPUserType                      AS 'OLD User Type'
                  , CON.EMailAddress1                        AS 'E-mail'
                  , CON.FullName                             AS 'Member Name'
                  , @WC_Fee                                  AS 'WC Fee'
                  , STC.Value                                AS 'Plan Type'
                  , CON.new_VIPProductName                   AS 'OLD Product Name'
                  , CONVERT(varchar,CON.new_MemberSince,101) AS 'Effective Date'
                  , STA.Value                                AS 'Location'
      
               FROM dbo.Contact CON
    
    LEFT OUTER JOIN StringMap STG
                 ON           STG.attributename  = 'gendercode' 
                AND           STG.attributevalue = CON.GenderCode 
                AND           STG.objecttypecode = 2 
                AND           STG.langid = 1033
    
    LEFT OUTER JOIN StringMap STC
                 ON           STC.attributename  = 'new_coveragetype' 
                AND           STC.attributevalue = CON.new_CoverageType
                AND           STC.objecttypecode = 2 
                AND           STC.langid = 1033
    
    LEFT OUTER JOIN StringMap STA
                 ON           STA.attributename  = 'new_address1_province' 
                AND           STA.attributevalue = CON.new_address1_province
                AND           STA.objecttypecode = 10007 
                AND           STA.langid = 1033
    
              WHERE CON.StatusCode = 1

    Best regards

    Steve


    Steve Sämmang, Vienna, Austria
    Blog: xrm.io Website: simplic.at

    • Marked as answer by katiemackie Tuesday, July 9, 2013 9:09 PM
    Tuesday, July 9, 2013 8:26 PM
  • Awesome Steve

    Thanks so much.

    • Marked as answer by katiemackie Tuesday, July 9, 2013 8:49 PM
    Tuesday, July 9, 2013 8:49 PM
  • No Problem. You're welcome. PS: You've marked your answer not mine ;)

    Steve Sämmang, Vienna, Austria
    Blog: xrm.io Website: simplic.at

    Tuesday, July 9, 2013 8:53 PM