Do something similar to 'Select 0 from abc' used in sql

Posts   
 
    
rthakur
User
Posts: 10
Joined: 18-May-2017
# Posted on: 17-Oct-2017 10:46:33   

I have a requirement where in certain cases I have to select a blank field from the query.

Something like Select 0 used in sql. Is this possible in LLBLGen?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-Oct-2017 14:03:17   

You have to elaborate a bit more in what context you want to use this and why. E.g. in a linq query you can specify a constant in the projection without problems:

from c in metaData.Customer select new { Constant = 0, CustomerId = c.CustomerId};

Frans Bouma | Lead developer LLBLGen Pro
rthakur
User
Posts: 10
Joined: 18-May-2017
# Posted on: 17-Oct-2017 14:33:31   

Hi,

I have a scenario where I have to add and remove join from the query based on some conditions, the problem in this case is that the select statement which were dependent on those joins give an error as I can't write a select from a table that no longer exists in query.

To resolve this I need a way by which i can implement a blank select , something similar to

Select 0, othercolumns from someTable

This is possible in SQL, can I do the same in LLBLGen Pro?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 17-Oct-2017 22:19:14   

Why don't you filter the select list and modify the projection if any, with the same criteria or according to the same triggers that you use to filter the tables to join?

rthakur
User
Posts: 10
Joined: 18-May-2017
# Posted on: 18-Oct-2017 11:39:08   

This is what I am trying to do.

DECLARE @condition bit

set @condition=1 -- Scenario one.

SELECT top 10 cpd.column1,cpd.column2, CASE WHEN @condition=1 THEN crv.column1 ELSE '0' END

FROM Table1 cpd LEFT JOIN Table2 crv on cpd.column= crv.column

--Scenarion 2 DECLARE @condition bit SET @condition=0

SELECT top 10 cpd.column1,cpd.column2, CASE WHEN @condition=1 THEN crv.column1 ELSE '0' END

FROM Table1 cpd --LEFT JOIN Table2 crv !! Based on the condtion I have to remove the join --on cpd.column= crv.column

--Here I get the error crc.column1 that it cannot be bound.

I am trying to achieve this in LLBLGen Pro :

public void Test(bool condition) { IRelationPredicateBucket bucket = new RelationPredicateBucket(); if (condition) { bucket.Relations.Add(Entity1.Relations.Entity2UsingColumn); } var queryFactory = new QueryFactory(); var query = queryFactory.Table.From(bucket.Relations) .Select(() => new DataContract() { Field1= Entity1Fields.Field1.ToValue<string>(), //Fields2= Entity2.Fields1.ToValue<int>(), !! I get an error here the same as that of sql , a select that has no join in the query. //To solve this I am trying to change my select using the ternary operator, but as it tries to evaluate because of 'ToValue<int>()' I get an error that column specified in slect does not have a relation in the from clause. //As I have removed the join too Field2= condition ? Entity2.Field1.ToValue<int>() :0 }); ;

    }

Is there any way to achieve this?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 18-Oct-2017 19:52:41   

Check this out: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=22582

I believe it answers your question.

rthakur
User
Posts: 10
Joined: 18-May-2017
# Posted on: 24-Oct-2017 11:50:38   

Hi, The above mentioned solution gives me result in the form of array, however I want to store it in a contract. Is there any simpler way to do it, I mean storing the result in contract ?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 24-Oct-2017 18:32:46   

Did you check this specific message: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=22582&StartAtMessage=0&#127604

Using WithProjection should return a structured resultSet.