SQL error

  

Hi,

I´m trying to do a query to get DISTINCT users from a DB, cause I´m not able to do that with an aggregate, at least that I know of. But my SQL is giving these errors, and I can´t understand them (although I´m a newbie in SQL)

Thanks in advance

Hi Rui,

There's no "FROM" in your query ;).

Hi Rui,

The problem with your query is that, their is no "From", keyword in your query. To use "Distinct" keyword in your query you can check the syntax of Distinct keyword from this link.

@Pravi: I already said that, and there's at glance nothing wrong with the DISTINCT?

just write between select and where 


from {InqueryBetweenUsers}


THERE IS nothing wrong with the select distinct! 

why do you have so many outputs?

Still trying to figure out how the SQL queries work in outsystems. Problem is, I want to use that SQL has a source record for a table. That table has checkboxes where you can select multiple elements from it and run an action on them. Problem is I can´t seem to find the way to add a Boolean to the SQL.

Hi Rui,

Having a "FROM" is pretty basic SQL, has little to do with OutSystems per se :). But can I ask why you need an SQL Statement at all? Your query seems easily doable in an Aggregate?

As for adding the Boolean, in your image I see you have already added it. In the SELECT list, add a "0" (for False) at the right location. Note that you must match the Output Entities / Structure with the SELECT list (in your image this is not the case).

You can do DISTINCT on a aggregate with Group By. You have to select all the fields you want to DISTINCT and click group by

set in the structure an attribute called BOOLEAN. Imagine the structure has these attributes:

boolean, id, name

In table records you bind the CHECK BOX in the variable with the 

SyntaxEditor Code Snippet

Name_OF_YOUR_TABLE_RECORDS.List.Current.NAME_OF_YOUR_STRUCTURE.Boolean


IN SQL the select should goes like

SELECT DISTINCT 0, Id, Name

FROM (...)


0 MEANS nothing will be ticked in the check box.. and the output should be the structure you have just created  with boolean, id, name . 

Gonna try explain my problem.

InqueryBetweenUsers, has two users involved, UserId and UserId2 (among other attributes). In this Table I want to show all the InqueryBetweenUsers where the User selected ISN´T a part of (this represents that there is no Inquery between those two users, and I have the possibility to create one).

I was doing this in an Aggregate where I was checking if:

InqueryBetweenUsers.UserId <> UserId and InqueryBetweenUsers.UserId2 <> UserId 


As you can see in the example, I´m testing with UserId=7. Without the filter there´s a relationship between UserId=6 and UserId2=7. If I insert the filter I don't see that relationship anymore, but I still see all other relationships involving UserId=6. These are the ones I want to hide.

Hi Rui,

I'm not sure I get it yet. There's a selected user, represented by UserId. There's an Entity, InqueryBetweenUsers (should that be Inquiry btw?) that indicates a relation between two users. But if you test with UserId = 7, why do you want a record with UserId = 6 to be filtered out?

Because UserId = 7 already has a relationship with UserId = 6, but since UserId = 6 also has InquiryBetweenUsers with other users it doesn´t get filtered out.

P.S: there´s probably a few typos, but once you write Inquiry a ton of times you don´t even notice it lol


Rui Almeida wrote:

Because UserId = 7 already has a relationship with UserId = 6, but since UserId = 6 also has InquiryBetweenUsers with other users it doesn´t get filtered out.

So you want to filter out not only relations between the selected user and other users, but also between those users and yet another users? So if I have a relation with you, and you with your mum, both should be left out?

In that case you need an SQL with some nasty subquery, or perhaps a WITH I'm afraid. Not something I would recommend in the slightest...

Exactly. I want to eliminate OUR relation and every OTHER relation that the other user is involved.

Yep, that what I was afraid of, and hence why I started looking into the query.

I'd be tempted to solve that programmatically, if the dataset isn't too big. Otherwise you're pretty screwed...

By programmatically you mean changing the core structure or by implementing the query?

I mean post-process the results by using List Actions. I'm not saying that's easy, but I think it beats trying to solve it in the database.

Well easy or not has to be done, especially cause I need to use these kind of filters on table records more than once. Any idea on how to start?

Thanks for the help anyway,
Much appreciated

If I understand correctly Rui, you want to find all people in an InqueryBetweenUsers, while excluding all inquiries where the @UserId is directly involved and all inquries for people who are also in a relation with @UserId. If that's a correct understanding, then I think you want something like this:

--------------------------------------

select distinct uid from (

SELECT {InqueryBetweenUsers}.[UserId] as uid

WHERE {InqueryBetweenUsers}.[UserId] <> @UserId and {InqueryBetweenUsers}.[UserId2] <> @UserId 

and {InqueryBetweenUsers}.[UserId] not in (select {InqueryBetweenUsers}.[UserId2] from {InqueryBetweenUsers} where {InqueryBetweenUsers}.[UserId] = @UserId) 

and {InqueryBetweenUsers}.[UserId2] not in (Select {InqueryBetweenUsers}.[UserId] from {InqueryBetweenUsers} where {InqueryBetweenUsers}.[UserId2] = @UserId)

UNION

SELECT {InqueryBetweenUsers}.[UserId2] as uid

WHERE {InqueryBetweenUsers}.[UserId] <> @UserId and {InqueryBetweenUsers}.[UserId2] <> @UserId 

and {InqueryBetweenUsers}.[UserId] not in (select {InqueryBetweenUsers}.[UserId2] from {InqueryBetweenUsers} where {InqueryBetweenUsers}.[UserId] = @UserId) 

and {InqueryBetweenUsers}.[UserId2] not in (Select {InqueryBetweenUsers}.[UserId] from {InqueryBetweenUsers} where {InqueryBetweenUsers}.[UserId2] = @UserId)

) a

-------------------------------------------

You'll need the UNION to get the list of users in UserId AND the list of users in UserId2.


PJ M wrote:

If I understand correctly Rui, you want to find all people in an InqueryBetweenUsers, while excluding all inquiries where the @UserId is directly involved and all inquries for people who are also in a relation with @UserId. If that's a correct understanding, then I think you want something like this:

--------------------------------------

select distinct uid from (

SELECT {InqueryBetweenUsers}.[UserId] as uid

WHERE {InqueryBetweenUsers}.[UserId] <> @UserId and {InqueryBetweenUsers}.[UserId2] <> @UserId 

and {InqueryBetweenUsers}.[UserId] not in (select {InqueryBetweenUsers}.[UserId2] from {InqueryBetweenUsers} where {InqueryBetweenUsers}.[UserId] = @UserId) 

and {InqueryBetweenUsers}.[UserId2] not in (Select {InqueryBetweenUsers}.[UserId] from {InqueryBetweenUsers} where {InqueryBetweenUsers}.[UserId2] = @UserId)

UNION

SELECT {InqueryBetweenUsers}.[UserId2] as uid

WHERE {InqueryBetweenUsers}.[UserId] <> @UserId and {InqueryBetweenUsers}.[UserId2] <> @UserId 

and {InqueryBetweenUsers}.[UserId] not in (select {InqueryBetweenUsers}.[UserId2] from {InqueryBetweenUsers} where {InqueryBetweenUsers}.[UserId] = @UserId) 

and {InqueryBetweenUsers}.[UserId2] not in (Select {InqueryBetweenUsers}.[UserId] from {InqueryBetweenUsers} where {InqueryBetweenUsers}.[UserId2] = @UserId)

) a

-------------------------------------------

You'll need the UNION to get the list of users in UserId AND the list of users in UserId2.


Wow that´s alot to take in. I´ll try to test it today, and see if it works.

Thanks!


I'm not sure whether PJ's solution will work (note the "distinct" is superfluous, as "union" (as opposed to "union all") already removes duplicates), but here's an example of another possible solution:

declare @u as integer = 1;

with x (a,b) as (
   select 1 as a, 2 as b
   union all
   select 2, 3
   union all
   select 3, 4
   union all
   select 1, 5
   union all
   select 5, 3
   union all
   select 6, 7
),
z (a) as (
   select a from x where b = @u
   union
   select b from x where a = @u
)
select *
from x
where x.a not in (select a from z)
and x.b not in (select a from z)

The first with is just to create some data, in your case you don't need it as you already have the InquiryBetweenUsers table. The second with is the important one: it creates a list of all users that have a relation with the selected user ("@u"). Then, the select selects all elements from InquireyBetweenUsers of which neither the first nor the second user are inside z.

I'm not sure whether this is very performant, but then again, I don't think anything will be very performant. So converting the above to your tables, it would something like this:


WITH BadUsers (UserId) AS (
   SELECT {InquiryBetweenUsers}.[UserId1]
   FROM {InquiryBetweenUsers}
   WHERE {InquiryBetweenUsers}.[UserId2] = @UserId

   UNION

   SELECT {InquiryBetweenUsers}.[UserId2]
   FROM {InquiryBetweenUsers}
   WHERE {InquiryBetweenUsers}.[UserId1] = @UserId
)
SELECT {InquiryBetweenUsers}.*
FROM {InquiryBetweenUsers}
WHERE {InquiryBetweenUsers}.[UserId1] NOT IN (SELECT UserId FROM BadUsers)
   AND {InquiryBetweenUsers}.[UserId2] NOT IN (SELECT UserId FROM BadUsers)

Hi Rui,

Did you see my reply and check whether it works?

Hi,

Sorry, I got caught up in solving other problems, and this one was postponed, but I'll look into it today. I´ll update you soon.

Thanks for all the help.