5 Replies Latest reply: Sep 30, 2011 1:19 PM by Chris Cammers RSS

    If statement in SQL Select

      Hi all,

       

      I'm having several problems trying to work with an old legacy data set and the only one that I can't seem to overcome is a date issue.

       

      I need to create a YearMonth filed in my SQL Select statement so that I can then reference it in the where clause, but can not seem to get it right.

       

      This is a "trimmed" version of what I am using (minus many of the fields):

       

      [ClaimsHistory]:
      first 10000 LOAD
                [Year], 
                [YearMonth],
                [Adjuster Checker], 
                [Adj Checker Desc], 
                [Name 1], 
                [Name 1 Qualifier], 
                [Name 2];
      SQL Select 
                [Year], 
                [Month],
                IF CHAR([Month])= '1' THEN [Year]&'0'&[Month] ELSE [Year]&[Month] as [YearMonth],
                [Adjuster Checker], 
                [Adj Checker Desc], 
                [Name 1], 
                [Name 1 Qualifier], 
                [Name 2]
      FROM DataMart.Class."Open_Claims_History";
      //Where 'YearMonth' > '$(vLoadDate)';               <------ To be used once I can get the year month working.
      
      Exit Script;
      
      

       

      The main problem I face is that the month field contains 1,2,3,4......11,12. So I am trying to populate a '0' when the char count =1.

       

      I'd be greaful for any help.

       

      Many thanks,

      Ronnie

        • If statement in SQL Select
          Sunil Chauhan

            you need to take resident like below

          [ClaimsHistory]:
          first 10000 LOAD
                    [Year],
                    [YearMonth],
                    [Adjuster Checker],
                    [Adj Checker Desc],
                    [Name 1],
                    [Name 1 Qualifier],
                    [Name 2];
          SQL Select
                    [Year],
                    [Month],
                    IF CHAR([Month])= '1' THEN [Year]&'0'&[Month] ELSE [Year]&[Month] as [YearMonth],
                    [Adjuster Checker],
                    [Adj Checker Desc],
                    [Name 1],
                    [Name 1 Qualifier],
                    [Name 2]
          FROM DataMart.Class."Open_Claims_History";
          //Where 'YearMonth' > '$(vLoadDate)';               <------ To be used once I can get the year month working.

          [tablenamewhateveryouwant]:

          load

          *

          1 as junk

          resident [ClaimsHistory]
          Where 'YearMonth' > '$(vLoadDate)';

          drop table [ClaimsHistory]
          ;

           

           

            • If statement in SQL Select

              Thanks Sunil, but the problem is that I need to avoid loading * as there are many millions of rows per year.

               

              I need the YearMonth field to allow me to create an incremental load so that I load everything that is newer than my current qvd data set.

               

              There are 80 fields, many more calculated in this data extract and I am trying to make this as efficient as possible. By only pulling in a months worth of data at a time, I will be retreiving 500,000 rows for every hit, rather than 20 years worth of data each time.

                • If statement in SQL Select
                  Sunil Chauhan

                  ok inace of  * ,u can use ur fieldname

                    • If statement in SQL Select

                      The problem is that the if statement:

                                IF CHAR([Month])= '1' THEN [Year]&'0'&[Month] ELSE [Year]&[Month] as [YearMonth],

                       

                      will not work in the select statement. I get this:

                      ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near the keyword 'THEN'.

                       

                       

                      I was hoping that someone would know the correct way of writing it.

                       

                      If have tried writing it in the format of

                                IF (CHAR([Month])= '1', [Year] & '0' & [Month], [Year]&[Month]) as [YearMonth],

                       

                      But the error message appears

                      ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near ','  .