Toad World® Forums

Add a column to the query which is not a part of 'Where Condition'


#1

Hi ,

I have a query which works fine, But i need to have a column (FZZZ_RECNAME) in the o/p and it is not a part of the where condition at all. How to achieve this?

This is my query

SELECT DISTINCT D.OPRID
, O.oprdefndesc
, RU.ROLENAME ROLE
, D.OPRCLASS PERMISSION_LIST
, E.TREE_NAME
, E.ACCESS_GROUP
, E.ACCESSIBLE
FROM SYSADM.PSOPRCLS D
, sysadm.ps_OPRID_VW2 O
, sysadm.psroleuser RU
, SYSADM.PS_SCRTY_ACC_GRP E
, sysadm.psroleclass RC
WHERE O.oprid = RU.roleuser
AND D.OPRID = RU.roleuser
AND D.oprclass = RC.classid
AND RC.ROLENAME = RU.rolename
AND RU.rolename LIKE ‘FAS%’
AND E.classid = D.OPRCLASS;

Now i have to add FZZZ_RECNAME from FZZZ_PSQ_REC table


#2

So…add the table to the FROM clause, determine how to join it in the WHERE clause (some knowledge of the data is necessary to do that), and add the column to
the SELECT.

From: winona [mailto:bounce-winona@toadworld.com]

Sent: Thursday, March 06, 2014 11:15 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Add a column to the query which is not a part of ‘Where Condition’

Add a column to the query which is not a part of 'Where Condition’

Thread created by winona

Hi ,

I have a query which works fine, But i need to have a column (FZZZ_RECNAME) in the o/p and it is not a part of the where condition at all. How to achieve this?

This is my query

SELECT DISTINCT D.OPRID

, O.oprdefndesc

, RU.ROLENAME ROLE

, D.OPRCLASS PERMISSION_LIST

, E.TREE_NAME

, E.ACCESS_GROUP

, E.ACCESSIBLE

FROM SYSADM.PSOPRCLS D

, sysadm.ps_OPRID_VW2 O

, sysadm.psroleuser RU

, SYSADM.PS_SCRTY_ACC_GRP E

, sysadm.psroleclass RC

WHERE O.oprid = RU.roleuser

AND D.OPRID = RU.roleuser

AND D.oprclass = RC.classid

AND RC.ROLENAME = RU.rolename

AND RU.rolename LIKE ‘FAS%’

AND E.classid = D.OPRCLASS;

Now i have to add FZZZ_RECNAME from FZZZ_PSQ_REC table

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#3

The problem is i cannot join this table in the where clause at all, it would have been easier if i could do that.

And that above query is a SQL of a View and the view has the field FZZZ_RECNAME


#4

Help us understand. Why can’t you join the table? Really, the question comes down to: which
row from the table FZZZ_PSQ_REC table do you want the FZZZ_RECNAME value from? If you can answer the question of which row you want, that should tell you how to join the table into the query.

Nate Schroeder

US Row Crops IT Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WB - Saint Louis, MO - 63167

314-694-2592

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled

to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and

all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its

subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.

Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying

this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially

including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of

Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all

applicable U.S. export laws and regulations.


#5

view has the field FZZZ_RECNAME

then alter the view and add column in select part (visible outside the view) and then you could be able to add in your where part when calling that view.

Sounds easy.