Max functions not working on sql 

Max functions not working on sql 

  

When i am trying to update the registration table by 

Update {WorkOrder}

set {WorkOrder}.[StatusId] = 7,

{WorkOrder}.[RegistraionID] = (select MAX([RegistraionID]) from {Registration} where [RegistraionID] > @p_RegistraionID)

where {WorkOrder}.[Id] = @p_CurrentWO


the max function does  not get defined in sql function  it throws a syntax error 

Hello Tarun.

I'm not sure, but my guess is that you are using just the [Attribute] instead of {Entity}.[Attribute].

For instance:

Update {WorkOrder}

set {WorkOrder}.[StatusId] = 7,

{WorkOrder}.[RegistraionID] = (select MAX({Registration}.[RegistraionID]) from {Registration} where {Registration}.[RegistraionID] > @p_RegistraionID)

where {WorkOrder}.[Id] = @p_CurrentWO


Can you test it?


Best regards,

Ricardo

thanx ricardo but the max function is not working in sql


Hi Tarun,


Can you provide more context?

- where are you getting that error? (from database? in service studio?)

- can you paste here the full error message? (incorrect syntax near X)

- in which database are you trying to accomplish that? (mySQL, SQL Server, Oracle, ...)


I'll wait for your feedback in order to help you.

Thanks.

Hi Nuno,

yes i am getting that error in the service studio only , 

i am not using any external database ,

i am doing that in SQL widget which is in outsystems platform only .

i have attached the following error message in image below

Hello Tarun,

In your screenshot, we cannot see the full SQL you're using but it seems that is not the same as in your first post. Can you please paste the exact SQL sentence that is generating that error?

Hi Tarun,


It seems the tables you are using are on different Databases. Did you use an Extension for this?

no i am not using any Extensions

Rui Covelo wrote:

Hello Tarun,

In your screenshot, we cannot see the full SQL you're using but it seems that is not the same as in your first post. Can you please paste the exact SQL sentence that is generating that error?

I am trying to use simple max function right now 


Hi Tarun,

The "FROM" clause does not allow columns.

Try the following:

SELECT MAX({Product}.[Price]) from {Product}

Let us know if it helped

Nuno Maurício wrote:

Hi Tarun,

The "FROM" clause does not allow columns.

Try the following:

SELECT MAX({Product}.[Price]) from {Product}

Let us know if it helped


i tried using that 

Tarun,

Your output Entity/Structure have more than 1 Decimal attribute.

To achieve that, you need:

1. create a structure with 1 attribute and set its Data Type as Decimal.

2. remove the Product from "Output structure"

3. add this new structure as Output structure

And that should work.

Let us know.

Nuno Maurício wrote:

Tarun,

Your output Entity/Structure have more than 1 Decimal attribute.

To achieve that, you need:

1. create a structure with 1 attribute and set its Data Type as Decimal.

2. remove the Product from "Output structure"

3. add this new structure as Output structure

And that should work.

Let us know.

Thanx it worked

You're welcome, Tarun

Best regards

now i wanted to implement using this query

Build a logic so that all the titles whose status is "Metadata completed" to be assigned to different team members with state changed to "Assigned for QC".


so i am using a max query like this  (select MAX({Registration}.[RegistraionID]) from {Registration} where {Registration}.[RegistraionID] > @p_RegistraionID) where current user id must be assign the status 

to next user id   

I'm sorry Tarun, I couldn't understand what do you want to do. 

Can you provide more context and detailed information?
In your post, I cannot find any reference to "RandomTM" SQL and what do you expect that query to return.

SyntaxEditor Code Snippet

Select Distinct  {Registration}.* from {Registration}
LEFT JOIN {WorkOrder}
on {Registration}.[Id] = {WorkOrder}.[RegistraionID]
where ({WorkOrder}.[RegistraionID] > @p_UserId  or {WorkOrder}.[RegistraionID] IS NULL)
AND {Registration}.[UserRolesID] = 2

What I am trying to do is Fetch the Next Record of registration id ,

Apart from current login user it should get the next user id i.e the next registration id of an user

basically i want to implement round robin in outsystems

1.   i have 10 team members.

2 . i am Assign 10 titles to each members.

3. Build a logic so that all the titles whose status is "Metadata completed" to be assigned to different team members with state changed to "Assigned for QC". by using round robin , so can i use max query in 

Select Distinct  {Registration}.* from {Registration}
LEFT JOIN {WorkOrder}
on {Registration}.[Id] = {WorkOrder}.[RegistraionID]
where ({WorkOrder}.[RegistraionID] >  @p_UserId  or {WorkOrder}.[RegistraionID] IS NULL)
AND {Registration}.[UserRolesID] = 2

So i wanted to ask you that whether i can write subqueries to fetch i next record by the max query

like this   (select MAX({Registration}.[ID]) from {Registration} where{Registration}.[ID] >  @p_UserId)


Tarun, 

answering your question: 

Yes, you can have subqueries like those you want to build.

Here's a sample one:

update {Entity1}
set {Entity1}.[Attribute1] = (select MAX({Entity2}.[Attribute1]) from {Entity2})

Let me know if you still have errors.

Best regards