Can someone help me transfer the logic into an stored procedure, the entire syntax, this is the query I am using in access, i want to transfer to sql server 2008
SELECT CountOfColorValue, ColorTable*, IIf([CountOfColorValue]<7,"Invalid Color",[ColorCode]) AS COLOR STATUS
FROM INNER JOIN ColorTable ON (ColorLocation=ColorLocation) AND (ColorPart=ColorPart) AND (ColorCode=ColorCode);
Loading
Jignesh TrivediPosted Mar 12, 2012, 12:14 AM
As per my knowledge IIF is only support in sql server 2012, so you must use CASE WHEN..
try...
SELECT CountOfColorValue, ColorTable,*, case when ([CountOfColorValue]<7),"Invalid Color"else [ColorCode] end) AS COLOR STATUS
FROM INNER JOIN ColorTable ON (ColorLocation=ColorLocation) AND (ColorPart=ColorPart) AND (ColorCode=ColorCode)
hope this help.
VulpesPosted Mar 12, 2012, 5:32 PM
David SmithPosted Mar 12, 2012, 9:50 AM
so I am going to give you the exact linq I am doing.
var ColorList_A = from s in ColorRawTable.AsEnumerable()
orderby s.Field
("COLOR_TEST_PHASE")
group s by new
{
ColorValue= s.Field
ColorCode = s.Field
Environment = s.Field
ColorIdentification = s.Field
ColorTestLocation = s.Field
}
into g
select new
{
Count = g.Select(s => s["ColorValue"]).Count(),
ColorValue= g.Key.ColorValue,
ColorCode = g.Key.ColorCode,
ColorTestPhase= g.Key.Environment,
ColorIdentification = g.Key.ColorIdentification ,
ColorTestLocation = g.Key.ColorTestLocation,
};
var ColorList_B = from s in ColorList_A.AsEnumerable()
group s by new
{
ColorCode = s.ColorCode,
Environment = s.EnvironmentTestPhase,
ColorIdentification = s.ColorIdentification ,
ColorTestLocation = s.ColorTestLocation,
}
into g
select new
{
CountOfColorValue = g.Select(s => s.ColorValue).Count(),
ColorCode = g.Key.ColorCode,
ColorTestPhase= g.Key.Environment,
ColorIdentification = g.Key.ColorIdentification ,
ColorTestLocation = g.Key.ColorTestLocation,
};
Up to this point I am good, below is where I am trying to incorporate the inner join and also the if condition "IIf([CountOfColorValue]<7,"Invalid Color","View Valid Color Report") AS COLOR STATUS" in the linq below I am getting errors in the join and then I am not for sure how to incorporate the if condition inside the linq. Review below
var FinalColorReportList = from productionListB in productionList_B.AsEnumerable()
join FinalColorDataTable in ColorRawTable.AsEnumerable()
on new
{
KEY1 = ColorListB.TestLocation,
KEY2 = ColorListB.UutIdentification,
KEY3 = ColorListB.ColorTestPhase,
KEY4 = ColorListB.Pcode,
}
equals new
{
KEY1 = FinalColorDataTable["COLOR_TEST_LOCATION"],
KEY2 = FinalColorDataTable["COLOR_IDENTIFICATION"],
KEY3 = FinalColorDataTable["COLOR_TEST_PHASE"],
KEY4 = FinalColorDataTable["ColorCode"],
}
select new
{
COLOR_TEST_DATA = ColorRawTable["COLOR_TEST_DATA"],
COLOR_WRITE_TIME = ColorRawTable["COLOR_WRITE_TIME"],
COLOR_IDENTIFICATION = ColorRawTable["COLOR_IDENTIFICATION"],
COLOR_REVISION = ColorRawTable["COLOR_REVISION"],
COLOR_SERIAL_NUMBER = ColorRawTable["COLOR_SERIAL_NUMBER"],
COLOR_TEST_STATUS = ColorRawTable["COLOR_STATUS"],
COLOR_START_TIME = ColorRawTable["COLOR_START_TIME"],
//VIEW_COLOR_STATUS = ColorListB.Count < 7 < : "Invalid Color" ? "View Color Report"
};
Jignesh TrivediPosted Mar 12, 2012, 7:41 AM
In LINQ consider below example.
I have two class test and test1 and defination are.
class test
{
public int id { get; set; }
public string name { get; set; }
}
class test1
{
public bool IsId { get; set; }
public string name { get; set; }
}
now i add some value in of "Test" class.
List
test t = new test { id = 0, name = "jignesh" };
test t1 = new test { id = 1, name = "jignesh1" };
test t2 = new test { id = 2, name = "jignesh2" };
test t3 = new test { id = 3, name = "jignesh3" };
test t4 = new test { id = 4, name = "jignesh4" };
tall.Add(t);
tall.Add(t1);
tall.Add(t2);
tall.Add(t3);
tall.Add(t4);
now i am use equivalent statement of IIF
var k = (from g in tall
select new test1 { IsId = (g.id > 1) ? true : false, name = g.name }
);
hope this help.
Jignesh TrivediPosted Mar 12, 2012, 7:30 AM
IIF is only support in SQL 2012.
please go through..
http://www.mssqltips.com/sqlservertip/2570/new-logical-functions-in-sql-server-2012-iif-and-choose/
David SmithPosted Mar 12, 2012, 7:28 AM
SELECT CountOfColorValue, ColorTable,*, case when ([CountOfColorValue]<7),"Invalid Color"else [ColorCode] end) AS COLOR STATUS
FROM INNER JOIN ColorTable ON (ColorLocation=ColorLocation) AND (ColorPart=ColorPart) AND (ColorCode=ColorCode)
David SmithPosted Mar 12, 2012, 7:15 AM
CountOfColorValue, ColorTable*, IIf([CountOfColorValue]<7,"Invalid Color",[ColorCode]) AS COLOR STATUS
I dont think it like the less than sign
David SmithPosted Mar 12, 2012, 7:06 AM
Can you also show me how to transef that exact syntax into linq, that way I dont have to go back and hit the database went I get to that point, I can do it all all in memory using linq to befor setting the datasource
SenthilkumarPosted Mar 12, 2012, 12:15 AM
There is no table in the from clause.
I have assumed and written the stored procedure.
I have given the MainTable in From clause.You need to change the correct table and use the appropriate table object in the select column list.
Hope this will help you. If you need any thing then post me. If it is helpful then mark it as accept answer.