Conditional query on ASPNET page

Dear Friends,

I am working on search customer information page.

I have 5 search options,

Name,

Email,

Order Number,

Product Name,

Order Date

I am using check boxes, I need to allow admin to enter above information and click on search,

How will I make my query and sends to DB server to pull up records which satisfies where clause:

For example,

Select * from orders where Email = #Email#

This is simple, but I can not hardcode all queries, I don’t know in advance what different search option ADMIN may choose.

Any suggestion for logic or query make up,

Thanks,

Fahim.

So here are a couple suggestions that might help you with your task:

1. Look into calling system stored procedure sp_columns (http://msdn2.microsoft.com/en-us/library/ms176077.aspx) on the table to get the column metadata information.

Using this information you can then dynamically generate a query based on the check boxes selected by the user.

2, If you schema is simple and you already know the column information, then all you need to do is have a string that concatenates differect WHERE clauses based on the options selected and then submit the query when the user hits the search button.

HTH,

Concatenating Multiple rows to end of results set

I am trying to create a fixed position output file with customer information and the first three accounts they have with us per the following query in DB2/UDB Version 7.2. But I am getting the error:

[IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "fetch" was found following "e.cst_id - b.cst_id". Expected tokens may include ")". SQLSTATE=42601

Any ideas would be appreciated.

Thanks,
Dave

select cast(ltrim(a.cst_id) as char(10)) ||
case when a.cst_typ_id = '01'
then cast('P' as char(1))
else cast('C' as char(1))
end ||
cast(ltrim(a.tax_id_nbr) as char(9)) ||
cast(ltrim(a.ip_nm_line_1) as char(40)) ||
cast(ltrim(a.ip_nm_line_2) as char(40)) ||
cast(ltrim(a.addr_line_1) as char(40)) ||
cast(ltrim(a.addr_line_2) as char(40)) ||
cast(ltrim(a.addr_city) as char(29)) ||
cast(ltrim(a.addr_st) as char(2)) ||
cast(left(ltrim(a.addr_zip_cd),5) as char(5)) ||
case when length(ltrim(rtrim(a.wk_area_tele_cd))) = 0
then cast(' ' as char(3))
else cast(ltrim(a.wk_area_tele_cd) as char(3))
end ||
case when length(ltrim(rtrim(a.wk_tele_nbr))) = 0
then cast(' ' as char(7))
else cast(ltrim(a.wk_tele_nbr) as char(7))
end ||
case when length(ltrim(rtrim(a.wk_tele_extn))) = 0 or
ltrim(rtrim(a.wk_tele_extn)) = '0000'
then cast (' ' as char(4))
else cast(ltrim(a.wk_tele_extn) as char(4))
end ||
case when length(ltrim(rtrim(a.hm_area_tele_cd))) = 0
then cast(' ' as char(3))
else cast(ltrim(a.hm_area_tele_cd) as char(3))
end ||
case when length(ltrim(rtrim(a.hm_tele_nbr))) = 0
then cast(' ' as char(7))
else cast(ltrim(a.hm_tele_nbr) as char(7))
end ||
(select cast(ltrim(c.acct_nbr) as char(35))
from whse.ar c
where b.ar_id = c.ar_id and
b.as_of_dt = c.as_of_dt and
c.acct_nbr in (select acct_nbr
from whse.ar d,
whse.cst_ar e
where d.ar_id = e.ar_id and
d.as_of_dt = e.as_of_dt and
e.cst_id = b.cst_id fetch first 1 rows only)) ||
(select cast(ltrim(c.acct_nbr) as char(35))
from whse.ar c
where b.ar_id = c.ar_id and
b.as_of_dt = c.as_of_dt and
c.acct_nbr in (select acct_nbr
from whse.ar d,
whse.cst_ar e
where d.ar_id = e.ar_id and
d.as_of_dt = e.as_of_dt and
e.cst_id = b.cst_id and
d.acct_nbr not in (select acct_nbr
from whse.ar d,
whse.cst_ar e
where d.ar_id = e.ar_id and
d.as_of_dt = e.as_of_dt and
e.cst_id = b.cst_id fetch first 1 rows only) fetch first 1 rows only)) ||
(select cast(ltrim(c.acct_nbr) as char(35))
from whse.ar c
where b.ar_id = c.ar_id and
b.as_of_dt = c.as_of_dt and
c.acct_nbr in (select acct_nbr
from whse.ar d,
whse.cst_ar e
where d.ar_id = e.ar_id and
d.as_of_dt = e.as_of_dt and
e.cst_id = b.cst_id and
d.acct_nbr not in (select acct_nbr
from whse.ar d,
whse.cst_ar e
where d.ar_id = e.ar_id and
d.as_of_dt = e.as_of_dt and
e.cst_id = b.cst_id fetch first 1 rows only) fetch first 1 rows only))
from whse.cst a,
whse.cst_ar b
where a.cst_id = b.cst_id and
a.as_of_dt = b.as_of_dt and
ltrim(rtrim(cst_from_rel_cd)) || ltrim(rtrim(cst_to_rel_cd)) in ('SECJOR','PRIJOR','SECJNT','PRIJNT','TSTTRS','BUS
INC','PRIOWN','SECJAN','PRIJAN','SECPPR','PRIPPR',
'PRIBUS','PRITR','TRS','DBA','PRINPR','TST','BUS',
'PRIPTN','PRITRS','DBAINC','PRIDBA','LLCINC','OWN'
,'SECOWN','SECDBA')May I suggest that you look into the ROWNUMBER() function which may simplify your query. Use the result set from the ROWNUMBER() as a in-line query in a FROM. Below is a template to get you started.

SELECT ..... FROM
(SELECT cst_id, acct_nbr, ROWNUMBER() OVER (PARTITION BY cst_id, acct_nbr ORDER BY as_of_dt) as seq) x
WHERE x.cst_id = (join to your customer address table)
AND x.seq <= 3

Then you can use a CASE statement to grab the first account (seq = 1) as a column, the seq = 2 as 2nd account, and so forth.

Originally posted by davew@.pcbancorp
I am trying to create a fixed position output file with customer information and the first three accounts they have with us per the following query in DB2/UDB Version 7.2. But I am getting the error:

[IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "fetch" was found following "e.cst_id - b.cst_id". Expected tokens may include ")". SQLSTATE=42601

Any ideas would be appreciated.

Thanks,
Dave

select cast(ltrim(a.cst_id) as char(10)) ||
case when a.cst_typ_id = '01'
then cast('P' as char(1))
else cast('C' as char(1))
end ||
cast(ltrim(a.tax_id_nbr) as char(9)) ||
cast(ltrim(a.ip_nm_line_1) as char(40)) ||
cast(ltrim(a.ip_nm_line_2) as char(40)) ||
cast(ltrim(a.addr_line_1) as char(40)) ||
cast(ltrim(a.addr_line_2) as char(40)) ||
cast(ltrim(a.addr_city) as char(29)) ||
cast(ltrim(a.addr_st) as char(2)) ||
cast(left(ltrim(a.addr_zip_cd),5) as char(5)) ||
case when length(ltrim(rtrim(a.wk_area_tele_cd))) = 0
then cast(' ' as char(3))
else cast(ltrim(a.wk_area_tele_cd) as char(3))
end ||
case when length(ltrim(rtrim(a.wk_tele_nbr))) = 0
then cast(' ' as char(7))
else cast(ltrim(a.wk_tele_nbr) as char(7))
end ||
case when length(ltrim(rtrim(a.wk_tele_extn))) = 0 or
ltrim(rtrim(a.wk_tele_extn)) = '0000'
then cast (' ' as char(4))
else cast(ltrim(a.wk_tele_extn) as char(4))
end ||
case when length(ltrim(rtrim(a.hm_area_tele_cd))) = 0
then cast(' ' as char(3))
else cast(ltrim(a.hm_area_tele_cd) as char(3))
end ||
case when length(ltrim(rtrim(a.hm_tele_nbr))) = 0
then cast(' ' as char(7))
else cast(ltrim(a.hm_tele_nbr) as char(7))
end ||
(select cast(ltrim(c.acct_nbr) as char(35))
from whse.ar c
where b.ar_id = c.ar_id and
b.as_of_dt = c.as_of_dt and
c.acct_nbr in (select acct_nbr
from whse.ar d,
whse.cst_ar e
where d.ar_id = e.ar_id and
d.as_of_dt = e.as_of_dt and
e.cst_id = b.cst_id fetch first 1 rows only)) ||
(select cast(ltrim(c.acct_nbr) as char(35))
from whse.ar c
where b.ar_id = c.ar_id and
b.as_of_dt = c.as_of_dt and
c.acct_nbr in (select acct_nbr
from whse.ar d,
whse.cst_ar e
where d.ar_id = e.ar_id and
d.as_of_dt = e.as_of_dt and
e.cst_id = b.cst_id and
d.acct_nbr not in (select acct_nbr
from whse.ar d,
whse.cst_ar e
where d.ar_id = e.ar_id and
d.as_of_dt = e.as_of_dt and
e.cst_id = b.cst_id fetch first 1 rows only) fetch first 1 rows only)) ||
(select cast(ltrim(c.acct_nbr) as char(35))
from whse.ar c
where b.ar_id = c.ar_id and
b.as_of_dt = c.as_of_dt and
c.acct_nbr in (select acct_nbr
from whse.ar d,
whse.cst_ar e
where d.ar_id = e.ar_id and
d.as_of_dt = e.as_of_dt and
e.cst_id = b.cst_id and
d.acct_nbr not in (select acct_nbr
from whse.ar d,
whse.cst_ar e
where d.ar_id = e.ar_id and
d.as_of_dt = e.as_of_dt and
e.cst_id = b.cst_id fetch first 1 rows only) fetch first 1 rows only))
from whse.cst a,
whse.cst_ar b
where a.cst_id = b.cst_id and
a.as_of_dt = b.as_of_dt and
ltrim(rtrim(cst_from_rel_cd)) || ltrim(rtrim(cst_to_rel_cd)) in ('SECJOR','PRIJOR','SECJNT','PRIJNT','TSTTRS','BUS
INC','PRIOWN','SECJAN','PRIJAN','SECPPR','PRIPPR',
'PRIBUS','PRITR','TRS','DBA','PRINPR','TST','BUS',
'PRIPTN','PRITRS','DBAINC','PRIDBA','LLCINC','OWN'
,'SECOWN','SECDBA')