Is the following sql query possible with llblgen?

use icdev
SELECT [Name], 
(select top 1 a.value from dbo.attribute a inner join infrastructure.networkdeviceattribute na ON na.AttributeGuid = a.AttributeGuid where na.NetworkDeviceGuid = n.NetworkDeviceGuid and a.Name = 'Cpu Count') as CpuCount,
(select top 1 a.value from dbo.attribute a inner join infrastructure.networkdeviceattribute na ON na.AttributeGuid = a.AttributeGuid where na.NetworkDeviceGuid = n.NetworkDeviceGuid and a.Name = 'Page File Location') as PageFileLocation,
(select top 1 a.value from dbo.attribute a inner join infrastructure.networkdeviceattribute na ON na.AttributeGuid = a.AttributeGuid where na.NetworkDeviceGuid = n.NetworkDeviceGuid and a.Name = 'Page File Size') as PageFileSize
  FROM infrastructure.networkdevice n

Sure You should use **ScalarQueryExpression **. Please read LLBLGenPro Help - Using generated code - Field expressions and aggregates.

Hope helpful.

daelmo wrote:

Sure You should use **ScalarQueryExpression **. Please read LLBLGenPro Help - Using generated code - Field expressions and aggregates.

Hope helpful.

I think your on the right track however, I am not able to make it work. I thought the scalarqueryexpression was for fetching aggregate values. I am not trying to get a aggregate value but actual values from the attribute table. What complicates it more is that a networkdevice has networkdeviceattributes which is a subclass of attribute. So I am not sure how to tie it all together. This is what i have so far and it seems to query all the attributes and not query based on the predicate that I have added.

            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(NetworkDeviceFields.NetworkDeviceGuid, 0);
            fields.DefineField(NetworkDeviceFields.Name, 1);
            IRelationCollection _rcol = new RelationCollection(NetworkDeviceEntity.Relations.NetworkDeviceAttributeEntityUsingNetworkDeviceGuid);
            fields.DefineField(new EntityField2("Cpu Count",
                new ScalarQueryExpression(NetworkDeviceAttributeFields.Value,(NetworkDeviceFields.NetworkDeviceGuid == NetworkDeviceAttributeFields.NetworkDeviceGuid & NetworkDeviceAttributeFields.Name == "Cpu Count"),_rcol)),2);
            DataTable results = new DataTable();
            _adapter.FetchTypedList(fields, results, null);

I got it to work thanks. I didn't need to add a relationship to the scalar query. I needed to add the relationship between networkdevice and networkdeviceattribute in the FetchEntities statement. Thanks for your help.

ScalarQueryExpression subSelect = new ScalarQueryExpression(
    NetworkDeviceAttributeFields.Value,
    (NetworkDeviceAttributeFields.NetworkDeviceGuid == NetworkDeviceFields.NetworkDeviceGuid & 
    NetworkDeviceAttributeFields.Name == "Cpu Count")
);

ResultsetFields fields = new ResultsetFields(3);
fields[0] = NetworkDeviceFields.NetworkDeviceGuid;
fields[1] = NetworkDeviceFields.Name;
fields[2] = new EntityField2("Cpu Count", subSelect);

DataTable results = new DataTable();

_adapter.FetchTypedList(fields, results, null);

untested, but i think this will do it. if your ScalarQueryExpression requires a relationcollection pass NULL. I believe the code below would return the same results

ResultsetFields fields = new ResultsetFields(3);
fields[0] = NetworkDeviceFields.NetworkDeviceGuid;
fields[1] = NetworkDeviceFields.Name;
fields[2] = NetworkDeviceAttributeFields.Value;

IRelationPrediateBucket bucket = new RelationPrediateBucket(NetworkDeviceAttributeFields.Name == "Cpu Count");
bucket.Relations.Add(NetworkDeviceEntity.Relations.NetworkDeviceAttributeEntityUsingNetworkDeviceGuid);

DataTable results = new DataTable();

_adapter.FetchTypedList(fields, results, bucket);

jmeckley wrote:

ScalarQueryExpression subSelect = new ScalarQueryExpression(
    NetworkDeviceAttributeFields.Value,
    (NetworkDeviceAttributeFields.NetworkDeviceGuid == NetworkDeviceFields.NetworkDeviceGuid & 
    NetworkDeviceAttributeFields.Name == "Cpu Count")
);

ResultsetFields fields = new ResultsetFields(3);
fields[0] = NetworkDeviceFields.NetworkDeviceGuid;
fields[1] = NetworkDeviceFields.Name;
fields[2] = new EntityField2("Cpu Count", subSelect);

DataTable results = new DataTable();

_adapter.FetchTypedList(fields, results, null);

untested, but i think this will do it. if your ScalarQueryExpression requires a relationcollection pass NULL. I believe the code below would return the same results

ResultsetFields fields = new ResultsetFields(3);
fields[0] = NetworkDeviceFields.NetworkDeviceGuid;
fields[1] = NetworkDeviceFields.Name;
fields[2] = NetworkDeviceAttributeFields.Value;

IRelationPrediateBucket bucket = new RelationPrediateBucket(NetworkDeviceAttributeFields.Name == "Cpu Count");
bucket.Relations.Add(NetworkDeviceEntity.Relations.NetworkDeviceAttributeEntityUsingNetworkDeviceGuid);

DataTable results = new DataTable();

_adapter.FetchTypedList(fields, results, bucket);

I will try that out, Thanks.