Showing posts with label queryselect. Show all posts
Showing posts with label queryselect. Show all posts

Tuesday, March 20, 2012

CONDITIONAL 'WHERE'

THIS IS MY QUERY:
SELECT *
FROM users a
WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5%'
and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
AND STATUS IN (MMCOLPARAM7)
ORDER BY d.Attorney
THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
HOW CAN THIS BE DONE ?
THANKS FOR THE HELP GUYS.
AHi
Try something like:
SELECT *
FROM users a
WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5%'
AND ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
AND ( STATUS IN (MMCOLPARAM7)
OR MMCOLPARAM7 IS NULL )
John
"Aleks" wrote:

> THIS IS MY QUERY:
>
> SELECT *
> FROM users a
> WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5
%'
> and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
> AND STATUS IN (MMCOLPARAM7)
> ORDER BY d.Attorney
> --
> THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
> CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
> SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
> HOW CAN THIS BE DONE ?
> THANKS FOR THE HELP GUYS.
> A
>
>|||With regards to

> AND STATUS IN (MMCOLPARAM7)
See if this works for status filtering
AND STATUS =coalesce(MMCOLPARAM7,STATUS)
HTH..
--
http://zulfiqar.typepad.com
BSEE, MCP
"Aleks" wrote:

> THIS IS MY QUERY:
>
> SELECT *
> FROM users a
> WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5
%'
> and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
> AND STATUS IN (MMCOLPARAM7)
> ORDER BY d.Attorney
> --
> THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
> CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
> SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
> HOW CAN THIS BE DONE ?
> THANKS FOR THE HELP GUYS.
> A
>
>

Friday, February 10, 2012

Concatenating multiple rows into one field

Hi everyone, I have the following query:
SELECT DISTINCT engine_model
FROM qs_engine_list,
si_plants
WHERE qs_engine_list.engine_family = si_plants.engine_family
AND si_plants.doc_num = p_doc_num
ORDER BY engine_model;

Given a document number of 3810477 it might return 3 rows that
have engine x, y, and z. But what I would really like it to return is
one row that looks like "x, y, z".

I know how to do this programatically but I need it to happen as part of the query.

Does anyone know how to do this, or is it even possible?You would be better off by writing a common function which returns you the output string after passing the sql query as an input string.

Here is a query to get the results but if you are expecting a large number of rows, might get longer. This one works for 5 rows.

select max(decode(rnbr, 1, emp_no, null)) || ',' ||
max(decode(rnbr, 2, emp_no, null)) || ',' ||
max(decode(rnbr, 3, emp_no, null)) || ',' ||
max(decode(rnbr, 4, emp_no, null)) || ',' ||
max(decode(rnbr, 5, emp_no, null)) emp_string
from ( select dept_no, emp_no, row_number() over (partition by dept_no order by dept_no) as rnbr
from emp )
group by dept_no
/|||Yeah, I ultimately figured out use a function inside of the query and it
worked great. Thanks for the help anyway, though.