|
View previous topic :: View next topic
|
| Author |
Message |
geeta.mishra
New User
Joined: 27 Dec 2005
Posts: 21
Location: Bangalore
|
|
|
|
Hi,
I have a column in the table which has the data type as SMALLINT.
This column is used in the WHERE clause of the query. I want to select :
1.the matching rows for the given value of this column
2.All the rows from the table (irrespective of the value of this column).
As this is not a character column I can not use the LIKE clause to do the same.
Is there any way I can serve both of the above purposes altogether in a single query?
e.g.
SELECT column1,column2
FROM table1
WHERE
column3 = 1
SELECT column1,column2
FROM table1
WHERE
column3 LIKE '%%%'
The syntax used in the second query is not correct. can I have a solution for this?
Thanks,
Geeta |
|
| Back to top |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| geeta.mishra wrote: |
e.g.
SELECT column1,column2
FROM table1
WHERE
column3 = 1
SELECT column1,column2
FROM table1
WHERE
column3 LIKE '%%%'
The syntax used in the second query is not correct. can I have a solution for this?
Thanks,
Geeta |
How about
| Code: |
| SELECT COLUMN1, COLUMN2 FROM TABLE1 |
|
|
| Back to top |
|
|
geeta.mishra
New User
Joined: 27 Dec 2005
Posts: 21
Location: Bangalore
|
|
|
|
Hi,
I dint understand the reply.
Could you please explain?
Regards,
Geeta |
|
| Back to top |
|
|
vasanthkumarhb
Active User
Joined: 06 Sep 2007
Posts: 275
Location: Bang,iflex
|
|
|
|
Hi,
Hi,
| Quote: |
I have a column in the table which has the data type as SMALLINT.
This column is used in the WHERE clause of the query. I want to select :
1.the matching rows for the given value of this column
2.All the rows from the table (irrespective of the value of this column).
As this is not a character column I can not use the LIKE clause to do the same.
Is there any way I can serve both of the above purposes altogether in a single query? |
According to your requirementas stated above.
| Code: |
| SELECT COLUMN1, COLUMN2 FROM TABLE1 |
The above query is correct, why you are using column3 = 1 in the first query and wild charecter in second query "%" this is my concern pls give more info. |
|
| Back to top |
|
|
geeta.mishra
New User
Joined: 27 Dec 2005
Posts: 21
Location: Bangalore
|
|
|
|
I think I cudnt put my requirement adequately.
There is a column 'column3' in a table 'table1' which has data type as
Numeric.My requirement is I have to select the rows from the table based on the following conditions:
1.I want to select records from table1 based on the value of column3.
2.I want to select all the records from table 1 regardless of the value of column3 i.e. all the rows from the table will be selected.
If you see 2 queries can be written to serve the above purpose as I have mentioned.
query 1 will select from the table 'table1' and use 'column3' in the Where clause
query2 will select from the table 'table' without using the where clause.
But my requrement is to use a single query which can serve both of the purposes.
Let me know if still you did not get me. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
the column content and type is irrelevant for Your request...
since the result of a query rerieving all the rows is from a logical point of view
always a superset of a query with a "WHERE" clause ....
why would You want to complicate Your life with a useless additional constraint ??? |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| geeta.mishra wrote: |
I think I cudnt put my requirement adequately.
There is a column 'column3' in a table 'table1' which has data type as
Numeric.My requirement is I have to select the rows from the table based on the following conditions:
1.I want to select records from table1 based on the value of column3.
2.I want to select all the records from table 1 regardless of the value of column3 i.e. all the rows from the table will be selected.
If you see 2 queries can be written to serve the above purpose as I have mentioned.
query 1 will select from the table 'table1' and use 'column3' in the Where clause
query2 will select from the table 'table' without using the where clause.
But my requrement is to use a single query which can serve both of the purposes.
Let me know if still you did not get me. |
If you are saying that sometimes you want to use the where and sometimes you do not then try this:
| Code: |
01 ws-min pic s9(4) comp-5.
01 ws-max pic s9(4) comp-5.
SELECT COLUMN1, COLUMN2 FROM TABLE1
WHERE COLUMN3 BETWEEN :WS-MIN AND :WS-MAX
If you have a value for column3 move it to ws-min and ws-max, else MOVE -32768 TO WS-MIN.
MOVE 32767 TI WS-MAX. |
|
|
| Back to top |
|
|
|
|
