| how to return a row based on count using multiple tables [message #633869] |
Thu, 26 February 2015 10:31  |
 |
suji6281
Messages: 151
Registered: September 2014
|
Senior Member |
|
|
Hello All,
Please help us to write a query to return 'X' with the following criteria.
1. count(*) from PS_VENDOR_COST_TBL table should be less than LINE_NBR_LIMIT from TBL1
2. count(*) from PS_VENDOR_COST_TBL1 table should be greater than LINE_NBR_LIMIT from TBL2
EXAMPLE:
SELECT 'X' FROM PS_VENDOR_COST_TBL, PS_VENDOR_COST_TBL1 WHERE COUNT(*) < TBL1.LINE_NBR_LIMIT AND COUNT(*) > TBL2.LINE_NBR_LIMIT
Create a table.
SQL> CREATE TABLE TBL1 (STRING_ID VARCHAR2(11) NOT NULL,
2 LINE_NBR_LIMIT INTEGER );
Table created
SQL> CREATE TABLE TBL2 (STRING_ID VARCHAR2(11) NOT NULL,
2 LINE_NBR_LIMIT INTEGER );
Table created
SQL> CREATE TABLE PS_VENDOR_COST_TBL (EMPLID VARCHAR2(11) NOT NULL,
2 LINE_NBR INTEGER , STATUS VARCHAR2(1) NOT NULL, AMOUNT_COL INTEGER );
Table created
SQL> CREATE TABLE PS_VENDOR_COST_TBL1 (EMPLID VARCHAR2(11) NOT NULL,
2 LINE_NBR INTEGER , STATUS VARCHAR2(1) NOT NULL, AMOUNT_COL INTEGER );
Table created
Insert rows into table.
SQL> INSERT INTO TBL1 VALUES ('LN_LIMIT', 1);
1 row created.
SQL> INSERT INTO TBL1 VALUES ('SH_LIMIT', 4);
1 row created.
SQL> INSERT INTO PS_VENDOR_COST_TBL VALUES ('12345', 1, 'A', 100);
1 row created.
SQL> INSERT INTO PS_VENDOR_COST_TBL VALUES ('12345', 2, 'A', 200);
1 row created.
SQL> INSERT INTO PS_VENDOR_COST_TBL1 VALUES ('12345', 1, 'A', 100);
1 row created.
SQL> INSERT INTO PS_VENDOR_COST_TBL1 VALUES ('12345', 2, 'A', 200);
1 row created.
SQL> INSERT INTO PS_VENDOR_COST_TBL1 VALUES ('12345', 3, 'A', 300);
1 row created.
Thank You!
|
|
|
|
| Re: how to return a row based on count using multiple tables [message #633870 is a reply to message #633869] |
Thu, 26 February 2015 10:35  |
 |
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Create a test case, test the test case but post only the test case code NOT its execution.
We cannot copy and paste the execution to test our solution.
For your understanding:
SQL> SQL> CREATE TABLE TBL1 (STRING_ID VARCHAR2(11) NOT NULL,
SP2-0734: unknown command beginning "SQL> CREAT..." - rest of line ignored.
SQL> 2 LINE_NBR_LIMIT INTEGER );
SQL>
SQL> Table created
SP2-0734: unknown command beginning "Table crea..." - rest of line ignored.
SQL>
SQL> SQL> CREATE TABLE TBL2 (STRING_ID VARCHAR2(11) NOT NULL,
SP2-0734: unknown command beginning "SQL> CREAT..." - rest of line ignored.
SQL> 2 LINE_NBR_LIMIT INTEGER );
SQL>
SQL> Table created
SP2-0734: unknown command beginning "Table crea..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>
SQL> SQL> CREATE TABLE PS_VENDOR_COST_TBL (EMPLID VARCHAR2(11) NOT NULL,
SP2-0734: unknown command beginning "SQL> CREAT..." - rest of line ignored.
SQL> 2 LINE_NBR INTEGER , STATUS VARCHAR2(1) NOT NULL, AMOUNT_COL INTEGER );
SQL>
SQL> Table created
SP2-0734: unknown command beginning "Table crea..." - rest of line ignored.
SQL>
SQL> SQL> CREATE TABLE PS_VENDOR_COST_TBL1 (EMPLID VARCHAR2(11) NOT NULL,
SP2-0734: unknown command beginning "SQL> CREAT..." - rest of line ignored.
SQL> 2 LINE_NBR INTEGER , STATUS VARCHAR2(1) NOT NULL, AMOUNT_COL INTEGER );
SQL>
SQL> Table created
SP2-0734: unknown command beginning "Table crea..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>
[Updated on: Thu, 26 February 2015 10:37] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
