If statement in SQL Select

[#1]

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

[SunilChauhan]

  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]
;

[#3]

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.

[SunilChauhan]

ok inace of  * ,u can use ur fieldname

[#5]

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 ','  .

[chriscammers]

You can do one of Three things

Change your If... Then ... Else or IF() syntax to a "Case" Statement in the SQL Select portion of the code

This is an approximation of the "Case" syntax, please use SQL Server help or google it

Here is a link I did a quick search for... http://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation...

Case

   When Char([Month]) = '1'

  Then [Year] & '0' & [Month]

   Else [Year] & [Month]

End

OR you can move your IF() syntax to the Load portion of the Qlikview Code.

OR In the Load part of your statement you can use this expression

[Year] & Num([Month],'00')