In C# + Access I got it working well, but I can't use Access querie result in others queries in code.
I will show now how it looksm like working.
So I got Access querie in AccessI got 1x query name: "kw_Attributes_x3"
SELECT tbAttributes.*, "MIN" AS Range, (Attribute_position * 10) AS Attribute_Sorting FROM tbAttributes
UNION
SELECT tbAttributes.*, "TAR" AS Range, (Attribute_position * 10 + 1) AS Attribute_Sorting FROM tbAttributes
UNION SELECT tbAttributes.*, "MAX" AS Range, (Attribute_position * 10 + 2) AS Attribute_Sorting FROM tbAttributes;
And a function where are queries which are using this Access querie:
private string GetGrdSpecSql()
{
long SpecId;
long[] AneksId;
int i = 0;
AneksId = new long[i + 1];
SpecId = (Int32)DsSpecHeader.Tables[0].Rows[0]["Spec_ID"];
foreach (DataRow dr in DsAneksHeader.Tables[0].Rows)
{
Array.Resize(ref AneksId, i + 1);
AneksId[i] = (Int32)dr["Aneksy_ID"];
i++;
}
string AneksSql = "(select SpecValue from tbSpec_data S where kw_Attributes_x3.Attribute_ColName = S.Attribute_ColName and UCASE(kw_Attributes_x3.Range) = UCASE(S.Range) and S.Spec_ID = " + SpecId + ") AS Specyfikacja";
string AneksWhere = "";
i = 1;
foreach (long Aneks in AneksId)
{
if ((Int32)Procs.RetLng(Aneks) > 0)
{
AneksSql = AneksSql + ", (select SpecValue from tbAneksy_data " + Strings.Chr(System.Convert.ToInt32(65 + i)) + " where kw_Attributes_x3.Attribute_ColName = " + Strings.Chr(System.Convert.ToInt32(65 + i)) + ".Attribute_ColName and UCASE(kw_Attributes_x3.Range) = UCASE(" + Strings.Chr(System.Convert.ToInt32(65 + i)) + ".Range) and " + Strings.Chr(System.Convert.ToInt32(65 + i)) + ".Aneksy_ID = " + Aneks + ") AS Aneks" + i;
}
if ((Int32)Procs.RetLng(Aneks) > 0)
{
AneksWhere = AneksWhere + " or Attrib.Aneks" + i + " > 0 ";
}
i++;
}
return "SELECT * FROM [SELECT kw_Attributes_x3.Attribute_position * 10 + IIf(UCase(kw_Attributes_x3.Range)=\'TAR\',1,IIf(UCase(kw_Attributes_x3.Range)=\'MAX\',2,0)) As Attribute_position, kw_Attributes_x3.Attribute_ColName As Id, kw_Attributes_x3.Attribute_Name As Nazwa, kw_Attributes_x3.Range As Zakres, " + AneksSql + " FROM kw_Attributes_x3 ]. As Attrib WHERE (NOT IsNull(Attrib.Specyfikacja)) " + AneksWhere;
}
The poroblem is now I have all in SQL Server. I know there exist something like CTE. Could someone help do it?