Original Message:
Sent: Thu August 01, 2024 09:59 PM
From: 矢作 英幸
Subject: Omitting certain fields from a file using sql query
> What does It mean here?
If the purpose of excluding certain columns is "not to show sensitive values such as salary," RCAC would be suitable.
As mentioned previously, there are several ways to exclude certain columns with "SELECT *" as discussed in "Exclude a column using SELECT * [except columnA] FROM tableA?". For example,
a) Create temporary table : Realistic for small DB. Run "ADDRPYLE SEQNBR(99) MSGID(CPA32B2) RPY(I)" in advance.
SELECT * FROM QIWS.QCUSTCDT;
CREATE OR REPLACE TABLE QTEMP.QCUSTCDT AS (SELECT * FROM QIWS.QCUSTCDT) WITH DATA ON REPLACE DELETE ROWS;
CL:CHGJOB INQMSGRPY(*SYSRPYL);
ALTER TABLE QTEMP.QCUSTCDT DROP COLUMN BALDUE;
SELECT * FROM QTEMP.QCUSTCDT;
b) Create view : Efficient for continuous use. May be inefficient if you want to exclude columns dynamically or have a large number of exclusion patterns.
For example, a list of columns for a particular table can be obtained from the SYSCOLUMNS table.
SELECT COLUMN_NAME || ',' AS COLUMNS FROM QSYS2.SYSCOLUMNS WHERE TABLE_NAME = 'QCUSTCDT' AND TABLE_SCHEMA = 'QIWS'
Create a View by deleting unnecessary columns from the list of columns.
----
To get the desired answer, please explain the purpose of excluding certain columns.
If the table is to be processed by an embedded SQL program, it does not matter if some columns are not used, as they can simply be ignored by the logic. If you have a table that you want to make available to the public through a query, SQL script, ACS download, etc., and this table contains columns that you want to keep private, you can implement security with RCAC.
------------------------------
矢作 英幸
Original Message:
Sent: Thu August 01, 2024 06:02 AM
From: jerry ven
Subject: Omitting certain fields from a file using sql query
Hi,
Thanks for the response here but when I tried below SQL query just for example:-
" SELECT * EXCEPT(FIELD1,FIELD2)
FROM mylib/myfile "
Then I got this error:" Keyword EXCEPT not expected. Valid tokens: FROM INTO.
And creating a view here will also involve selecting all those desired columns one by one so it looks it will be adding extra step here.
· And what does 'Use RCAC if you simply do not want to show the value of a particular column.' What does It mean here? could someone please clarify it in detail?
"An alternative is to define those columns that you do NOT want to see as IMPLICITLY HIDDEN. There are some caveats behind doing that on inserting rows in to the table; default value etc., but if your SQL statement is SELECT * FROM SCHEMA.TABLE hidden columns are not returned. To view the column you need to EXPLICITLY specify it on the SELECT statement. "
Any example with SQL query for this 'IMPLICITLY HIDDEN' SQL Query for above point please?
Thanks much….
Original Message:
Sent: Tue July 30, 2024 03:51 AM
From: Keith Pryke
Subject: Omitting certain fields from a file using sql query
An alternative is to define those columns that you do NOT want to see as IMPLICITLY HIDDEN. There are some caveats behind doing that on inserting rows in to the table; default value etc., but if your SQL statement is SELECT * FROM SCHEMA.TABLE hidden columns are not returned. To view the column you need to EXPLICITLY specify it on the SELECT statement.
------------------------------
Keith Pryke
Original Message:
Sent: Mon July 29, 2024 08:10 AM
From: 矢作 英幸
Subject: Omitting certain fields from a file using sql query
Hello,
As far as I know, Db2 for i (and most other DBMSs) does not have such function. You would need to achieve equivalent functionality in one of the following ways.
Hope this helps.
------------------------------
Hideyuki Yahagi
Original Message:
Sent: Sun July 28, 2024 05:05 AM
From: jerry ven
Subject: Omitting certain fields from a file using sql query
Hi,
Could someone please respond to my query here, if anyone knows it's answer?
Thanks much...
Original Message:
Sent: Fri July 26, 2024 10:30 AM
From: jerry ven
Subject: Omitting certain fields from a file using sql query
Hi,
Rather than selecting select field1,field2,... field99 from a file if i want to omit only certain fields and let the other fields be selected as it is so there any way for the same using select /omit criteria in sql query ?
Thanks