SQL Server Join Syntax

I am trying to join KUAF on dTree. The kuaf.id is a positive number (123456) and the dTree.ownerid is a negative number that mirrors the kuaf.id (-123456).

I have tried multiplying by -1 (kuaf.id*-1 = dtree.ownerid). I have tried putting the - in front of the call for the id (-kuaf.id=dtree.ownerid). Both return errors. I figure there must be a simple way to do this.

Any help is greatly appreceated (SQL Server only for now).

Find more posts tagged with

Comments

Giovanny Buitrago

Hi,

you may use absolute value ABS (FieldName).

See https://msdn.microsoft.com/en-us/library/ms189800.aspx

Greg Kellogg

That did it - thanks!

Brian_Walsh

Hi Greg,

Not sure what your goal is here as KUAF.ID is an ID for a user or group but DTree.OwnerID is not a user or group value. It is in fact a Volume. The negative value indicates a volume and if you encounter a positive value in OwnerID that would be an associate volume such as a project.

Cheers,

Brian G Walsh
Principal Technical Analyst

Content Server Team

OpenText Corporation
1-800-540-7292
bwalsh@opentext.com

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, October 24, 2016 11:56 AM
To: eLink Recipient
Subject: SQL Server Join Syntax

SQL Server Join Syntax

Posted by Kellogg, Greg On 10/24/2016 11:54 AM

I am trying to join KUAF on dTree. The kuaf.id is a positive number (123456) and the dTree.ownerid is a negative number that mirrors the kuaf.id (-123456).

Any help is greatly appreceated (SQL Server only for now).

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

Greg Kellogg

This is my final SQL after an assist from Giovanny.

select k.name, sum(dv.datasize)/1024 as "Total Size (KB)", count(dv.datasize) as "Number of Items"

from kuaf k, dversdata dv, dtree dt

where k.deleted=1

and dt.dataid=dv.docid

and k.id=ABS(dt.ownerid)

group by k.name

order by "Number of Items" desc

Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200

the iQ Business Group

intelligence. applied.

www.IQBGinc.com

From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>
Sent: Monday, October 24, 2016 10:56:27 AM
To: eLink Recipient
Subject: SQL Server Join Syntax

SQL Server Join Syntax

Posted by Kellogg, Greg On 10/24/2016 11:54 AM

I am trying to join KUAF on dTree. The kuaf.id is a positive number (123456) and the dTree.ownerid is a negative number that mirrors the kuaf.id (-123456).I have tried multiplying by -1 (kuaf.id*-1 = dtree.ownerid). I have tried putting the - in front of the call for the id (-kuaf.id=dtree.ownerid). Both return errors. I figure there must be a simple way to do this. Any help is greatly appreceated (SQL Server only for now).

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

****** CONFIDENTIALITY NOTICE ****** This message and any attachments are confidential and for the sole use of the intended recipient. If you are not the intended recipient, any copying, forwarding, disclosure, distribution or use of any part of this message or its attachments is strictly prohibited and may be unlawful. If you believe that you received this email in error, please do not read it or any attachments thereto, and notify the sender immediately by email and then delete this message from your system. Thank you.

Greg Kellogg

This is my final SQL after an assist from Giovanny.

select k.name, sum(dv.datasize)/1024 as "Total Size (KB)", count(dv.datasize) as "Number of Items"

from kuaf k, dversdata dv, dtree dt

where k.deleted=1

and dt.dataid=dv.docid

and k.id=ABS(dt.ownerid)

group by k.name

order by "Number of Items" desc

Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200

the iQ Business Group

intelligence. applied.

www.IQBGinc.com

SQL Server Join Syntax

Posted by Kellogg, Greg On 10/24/2016 11:54 AM

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

Greg Kellogg

Hey Brian - so using OwnerID would limit the query to only those items within the Personal Workspace - correct? And if I wanted everything I would user the dtree.userid field in my where clause?

Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200

the iQ Business Group

intelligence. applied.

www.IQBGinc.com

From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>
Sent: Monday, October 24, 2016 11:42:27 AM
To: eLink Recipient
Subject: RE SQL Server Join Syntax

RE SQL Server Join Syntax

Posted by Walsh, Brian On 10/24/2016 12:41 PM

Hi Greg, Not sure what your goal is here as KUAF.ID is an ID for a user or group but DTree.OwnerID is not a user or group value. It is in fact a Volume. The negative value indicates a volume and if you encounter a positive value in OwnerID that would be an associate volume such as a project. Cheers, Brian G Walsh
Principal Technical AnalystContent Server TeamOpenText Corporation
1-800-540-7292
bwalsh@opentext.com

SQL Server Join Syntax

Posted by Kellogg, Greg On 10/24/2016 11:54 AM

I am trying to join KUAF on dTree. The kuaf.id is a positive number (123456) and the dTree.ownerid is a negative number that mirrors the kuaf.id (-123456).

Any help is greatly appreceated (SQL Server only for now).

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	SQL Server Join Syntax
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

Greg Kellogg

Hey Brian - so using OwnerID would limit the query to only those items within the Personal Workspace - correct? And if I wanted everything I would user the dtree.userid field in my where clause?

Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200

the iQ Business Group

intelligence. applied.

www.IQBGinc.com

RE SQL Server Join Syntax

Posted by Walsh, Brian On 10/24/2016 12:41 PM

SQL Server Join Syntax

Posted by Kellogg, Greg On 10/24/2016 11:54 AM

I am trying to join KUAF on dTree. The kuaf.id is a positive number (123456) and the dTree.ownerid is a negative number that mirrors the kuaf.id (-123456).

Any help is greatly appreceated (SQL Server only for now).

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	SQL Server Join Syntax
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

Brian_Walsh

Using OwnerID would list everything in the Personal Workspace regardless of who actually owned the item. Other users could have items in another users Personal Workspace so just because they reside in a PWS does not necessarily mean that they are owned by that user.

Per the System Schema Reference – UserID - The user ID of the user who currently owns the node. It may be the user ID of the user who originally created the node, if ownership has not changed. The UserID is also a foreign key to ID in KUAF.

Brian G Walsh
Principal Technical Analyst

Content Server Team

OpenText Corporation
1-800-540-7292
bwalsh@opentext.com

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, October 24, 2016 1:13 PM
To: eLink Recipient
Subject: Re RE SQL Server Join Syntax 2

Re RE SQL Server Join Syntax 2

Posted by Kellogg, Greg On 10/24/2016 01:09 PM

Hey Brian - so using OwnerID would limit the query to only those items within the Personal Workspace - correct? And if I wanted everything I would user the dtree.userid field in my where clause?

Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200

the iQ Business Group

intelligence. applied.

www.IQBGinc.com

RE SQL Server Join Syntax

Posted by Walsh, Brian On 10/24/2016 12:41 PM

Hi Greg,

Cheers,

Brian G Walsh
Principal Technical Analyst

Content Server Team

OpenText Corporation
1-800-540-7292
bwalsh@opentext.com

SQL Server Join Syntax

Posted by Kellogg, Greg On 10/24/2016 11:54 AM

I am trying to join KUAF on dTree. The kuaf.id is a positive number (123456) and the dTree.ownerid is a negative number that mirrors the kuaf.id (-123456).

Any help is greatly appreceated (SQL Server only for now).

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	SQL Server Join Syntax
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	SQL Server Join Syntax
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16