Home » Developer & Programmer » Forms » Query using Percentage %%
Query using Percentage %% [message #661648] Sun, 26 March 2017 13:39 Go to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
I HAVE TWO TABLE

TABLE_ENTITY
ENTITY_CODE     ENTITY_NAME

SR	        SR INFO SOLUTION
DP	        DIGITAL PLANET
TABLE_USER
NAME           ENTITY_CODE

KESHRI	        SR
EHTE	        DP
REZA	        SR DP
Want query like, if TABLE_USER.ENTITY_CODE = TABLE_ENTITY.ENTITY_CODE then show the value into :LIST_ITEM
When i query for KESHRI it's showing SR INFO SOLUTION and for EHTE it's showing DIGITAL PLANET
But when query for REZA it's showing nothing, i want SR INFO SOLUTION & DIGITAL PLANET into :LIST_ITEM

HOW TO DO THIS???
Re: Query using Percentage %% [message #661649 is a reply to message #661648] Sun, 26 March 2017 16:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Best Practice is to store single value in each column.
Worst Practice stores more than 1 value in single column.
Re: Query using Percentage %% [message #661653 is a reply to message #661649] Mon, 27 March 2017 01:33 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In other words, TABLE_USER should look like
KESHRI   SR
EHTE     DP
REZA     SR
REZA     DP
Re: Query using Percentage %% [message #661661 is a reply to message #661648] Mon, 27 March 2017 02:58 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Actually TABLE_USER having more than 1000 records and 50 column and TABLE_ENTITY is more than 20 and 10 column
columns having not null values and child records etc...
result, i have to create numbers of row for that

its better if i enter TABLE_USER.ENTITY_CODE like 'DP SR XX YY ZZ ...'

so that's why i need this type of validation...
Re: Query using Percentage %% [message #661664 is a reply to message #661661] Mon, 27 March 2017 03:22 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
sr8464

its better if i enter TABLE_USER.ENTITY_CODE like 'DP SR XX YY ZZ ...'
No, it is not better. If TABLE_USER contains many columns (how are we supposed to know that? We know only what you tell us), then remove ENTITY_CODE from current TABLE_USER table and create a new table, for example TABLE_USER_ENTITY which would look as I described in my previous message.
Re: Query using Percentage %% [message #661670 is a reply to message #661664] Mon, 27 March 2017 08:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
Re: Query using Percentage %% [message #661699 is a reply to message #661670] Tue, 28 March 2017 11:52 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
created 2 views
CREATE OR REPLACE VIEW VIEW_CALL AS
SELECT DISTINCT
       ENTITY_MAST.ENTITY_CODE,
       USER_MAST.USER_CODE,
       USER_MAST.PASSWORD,
       ENTITY_MAST.ENTITY_NAME,
       USER_MAST.ENTITY,
       REPLACE(USER_MAST.ENTITY, ' ') AS "NO_SPACE"
  FROM USER_MAST,ENTITY_MAST
CREATE OR REPLACE VIEW VIEW_CALL1 AS
SELECT DISTINCT
       VIEW_CALL.ENTITY_CODE,
       VIEW_CALL.USER_CODE,
       VIEW_CALL.PASSWORD,
       VIEW_CALL.ENTITY_NAME,
       VIEW_CALL.ENTITY,
       VIEW_CALL.NO_SPACE,
       SUBSTR(VIEW_CALL.NO_SPACE,0,2) AS AAA,
       SUBSTR(VIEW_CALL.NO_SPACE,3,2) AS BBB,
       SUBSTR(VIEW_CALL.NO_SPACE,5,2) AS CCC
  FROM VIEW_CALL
and then
DECLARE
		CURSOR GRP_SR IS
		SELECT DISTINCT ROWNUM RN_SR, VIEW_CALL1.ENTITY_CODE||' - '||VIEW_CALL1.ENTITY_NAME AS ENTITY
		FROM VIEW_CALL1
		WHERE :LOGIN_USER_CODE = VIEW_CALL1.USER_CODE
		AND :LOGIN_PASSWORD = VIEW_CALL1.PASSWORD
		AND VIEW_CALL1.AAA = VIEW_CALL1.ENTITY_CODE;
		
		CURSOR GRP_DP IS
		SELECT DISTINCT ROWNUM RN_DP, VIEW_CALL1.ENTITY_CODE||' - '||VIEW_CALL1.ENTITY_NAME AS ENTITY
		FROM VIEW_CALL1
		WHERE :LOGIN_USER_CODE = VIEW_CALL1.USER_CODE
		AND :LOGIN_PASSWORD = VIEW_CALL1.PASSWORD
		AND VIEW_CALL1.BBB = VIEW_CALL1.ENTITY_CODE;
		
		CURSOR GRP_XX IS
		SELECT DISTINCT ROWNUM RN_XX, VIEW_CALL1.ENTITY_CODE||' - '||VIEW_CALL1.ENTITY_NAME AS ENTITY
		FROM VIEW_CALL1
		WHERE :LOGIN_USER_CODE = VIEW_CALL1.USER_CODE
		AND :LOGIN_PASSWORD = VIEW_CALL1.PASSWORD
		AND VIEW_CALL1.CCC = VIEW_CALL1.ENTITY_CODE;
	BEGIN
		CLEAR_LIST ('LOGIN_ENTITY_LIST');
		FOR I_SR IN GRP_SR LOOP
			FOR I_DP IN GRP_DP LOOP
				FOR I_XX IN GRP_XX LOOP
					ADD_LIST_ELEMENT ('LOGIN_ENTITY_LIST', I_DP.RN_DP, I_DP.ENTITY, I_DP.ENTITY);
					ADD_LIST_ELEMENT ('LOGIN_ENTITY_LIST', I_SR.RN_SR, I_SR.ENTITY, I_SR.ENTITY);
					ADD_LIST_ELEMENT ('LOGIN_ENTITY_LIST', I_XX.RN_XX, I_XX.ENTITY, I_XX.ENTITY);
				END LOOP;
			END LOOP;
		END LOOP;
	END;
1. how to merge both views into 1
2. how to merge ADD_LIST_ELEMENT into 1 so that i use ORDER BY CLAUSE
3. how to set one default value in list item using WHERE CLAUSE OR OTHER example...

code's are well executing but i don't know it's in proper channel or not

TEST CASE
CREATE TABLE USER_MAST(
USER_CODE VARCHAR(8) NOT NULL,
PASSWORD VARCHAR(20) NOT NULL,
ENTITY VARCHAR(50));

CREATE TABLE ENTITY_MAST(
ENTITY_CODE VARCHAR(2) NOT NULL,
ENTITY_NAME VARCHAR(50));

INSERT INTO USER_MAST VALUES ('MSRDBA','12345','SR DP XP');
INSERT INTO USER_MAST VALUES ('ACC-RN','123','SR');
INSERT INTO USER_MAST VALUES ('DP-KT','XYZ','DP XP');

INSERT INTO ENTITY_MAST VALUES ('SR','SR INFO SOLUTION');
INSERT INTO ENTITY_MAST VALUES ('DP','DIGITAL PLANET');
INSERT INTO ENTITY_MAST VALUES ('XP','XEROX POINT');
SELECT * FROM USER_MAST;
SELECT * FROM ENTITY_MAST;

[Updated on: Tue, 28 March 2017 12:12]

Report message to a moderator

Re: Query using Percentage %% [message #661701 is a reply to message #661699] Tue, 28 March 2017 12:47 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
sr8464 wrote on Tue, 28 March 2017 12:52

1. how to merge both views into 1
I don't see the purpose of VIEW_CALL1 as VIEW_CALL seems to be VIEW_CALL with additional columns. If you really must combine them, make a third one and union them together, setting the 3 additional columns to NULL.

And if you insist on two or three views, then having distinct in more than one of them is unnecessary.

[Updated on: Tue, 28 March 2017 12:51]

Report message to a moderator

Re: Query using Percentage %% [message #661708 is a reply to message #661648] Wed, 29 March 2017 00:07 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
in form i am using VIEW_CALL1 to get the result, VIEW_CALl used for REPLACE spaces
Re: Query using Percentage %% [message #661716 is a reply to message #661708] Wed, 29 March 2017 03:37 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't work out how to merge 2 views when one of them only queries the other?
Replace the from of VIEW_CALL1 with the whole select of VIEW_CALL - i.e. nest it so you don't have to do the replace 4 times.

As for merging the cursors - OR is a wonderful thing.

And I don't know what you think you're doing with rownum there but what you've got will give rownum of 1 for each query if you use the login MSRDBA.
Re: Query using Percentage %% [message #661781 is a reply to message #661716] Sat, 01 April 2017 11:11 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
can some one suggest some link to solve my issue.....!
Re: Query using Percentage %% [message #661785 is a reply to message #661781] Sat, 01 April 2017 13:52 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You should really read what other people tell you. Cookiemonster gave the instructions; how come you didn't follow them?
Re: Query using Percentage %% [message #661805 is a reply to message #661785] Mon, 03 April 2017 06:51 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
(SELECT DISTINCT (SUBSTR(X, 3, 2))
       FROM (SELECT DISTINCT (REPLACE(ENTITY, ' ')) AS X
       FROM USER_MAST
       WHERE USER_CODE IN ('MSRDBA'))
       WHERE USER_CODE IN ('MSRDBA')) AS XXX

MERGED VIEW INTO 1

HOW I SELECT DEFAULT/ INITIAL VALUE IN LIST_ITEM
BUT NOT USING ITEM_PROPERTY INITIAL VALUE
BECAUSE INITIAL VALUE IS DIFFERENT FOR EACH USER
Re: Query using Percentage %% [message #661808 is a reply to message #661805] Mon, 03 April 2017 07:10 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't need to specify user_code twice, and if that's the view I'm not sure why you're specifying it at all - it won't work for other users.

You probably don't want to set initial value programatically - you'd have to do it in when-new-record-instance and doing so would immediately change the record status to insert - meaning forms thinks there's changes that need saving and will prompt the users if they try to leave the record in any way. If you default it in when-validate-record or pre-insert (so as part of the save process), you'll avoid that problem.
Re: Query using Percentage %% [message #661952 is a reply to message #661808] Fri, 07 April 2017 05:12 Go to previous messageGo to next message
sr8464
Messages: 82
Registered: February 2017
Location: India
Member
Thank you i solved my problem
SELECT DISTINCT ROWNUM RN_SR, ENTITY_CODE ||' - '|| ENTITY_NAME AS ENTITY_SHOW
FROM ENTITY_MAST, USER_MAST
WHERE USER_CODE = :LOGIN_USER_CODE
AND :LOGIN_PASSWORD = PASSWORD
AND ENTITY_CODE = (SELECT DISTINCT (SUBSTR(SR, 0, 2))
FROM (SELECT DISTINCT (REPLACE(ENTITY, ' ')) AS SR
FROM USER_MAST
WHERE USER_CODE = :LOGIN_USER_CODE));
Re: Query using Percentage %% [message #661953 is a reply to message #661952] Fri, 07 April 2017 05:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Pretty sure it can simply be this:
SELECT DISTINCT ROWNUM RN_SR, ENTITY_CODE ||' - '|| ENTITY_NAME AS ENTITY_SHOW
FROM ENTITY_MAST, USER_MAST
WHERE USER_CODE = :LOGIN_USER_CODE
AND :LOGIN_PASSWORD = PASSWORD
AND ENTITY_CODE = SUBSTR(REPLACE(ENTITY, ' '), 0, 2);

The main query is already selecting from user_mast, so having a sub-query to get the same user_mast record is pointless.
The above only does the equivalent of GRP_SR, what about the other 2?
Re: Query using Percentage %% [message #661954 is a reply to message #661953] Fri, 07 April 2017 05:21 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That first distinct is pointless as well, When you select rownum every row ends up being distinct because of it.
Previous Topic: How to change base html page for Oracle Forms
Next Topic: Generate/Load Text Files
Goto Forum:
  


Current Time: Thu Mar 28 06:33:43 CDT 2024