This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 43%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Hi,
We get data from vendor and import into table like this
TxID
BCode
Ecode
SType
SCode
Desc
12345
SR
SR
MF
Abc
This is one type
12346
SD
SD
MF
Bcd
One more type
12347
CC
SD
OF
Efg
New type
12348
SR
SR
MF
Xyz
I get multiple rows of data like this. I need to send data to front end application as follows.
--Setup table variable to hold Datamart results
DECLARE @Types TABLE
(
TxID smallint not null,
BCode varchar(5),
ECode varchar(5),
SType varchar(5),
SCode Varchar(10),
[Desc] varchar(50) null
)
INSERT INTO @Types (TXID, BCode, ECode, SType, SCode, [Desc])
SELECT 12345, 'SR', 'SR', 'MF', 'Abc', 'This is one type'
Union
SELECT 12345, 'SD', 'SD', 'MF', 'Bcd', 'This is one more type'
union
SELECT 12345, 'CC', 'SD', 'OF', 'Efg', 'New Type'
union
SELECT 12345, 'SR', 'SR', 'MS', 'xyz', 'This is one different type'
SELECT
CASE
WHEN BCode IN ('SR', 'SD') and SType = 'MF' THEN 'Fee'
WHEN BCode = 'CC' AND ECode = 'SD' AND SType = 'OF' THEN 'Fee'
ELSE BCode
END AS Type
From @Types
This result data gets showed up on front end page. On that page, user has type dropdown with different types from that application which has ‘Fee’ as value.
When user selects ‘Fee’ from dropdown, grid should refresh ‘Fee’ related rows. But we do not have ‘Fee’ type in back end. Above table has those.
How do I make filter work when Fee is sent as type to back end stored procedure which gets data from above table.
I have to implements similar logic for other types.
When user selects ‘Fee’ from dropdown, grid should refresh ‘Fee’ related rows. But we do not have ‘Fee’ type in back end. Above table has those.
What related rows? I only see one table?
I get the feeling that I am only seeing part of the problem. You may know your tables, but we don't. Can you clarify?
And please fix your pots, it's difficult to read.
Hi, I understand. it's not letting me insert table. not sure how else I can include it. That is why I gave tmp table and data into it and sql query.
What is not letting you "insert table"? In any case, without further clarification from your side, I don't think we are getting any further.
Hi erland, yes one table. This has multiple row of types. When I send data to front end page, I dynamically come up with value like below:
SELECT
CASE WHEN BCode IN ('SR', 'SD') and SType = 'MF' THEN 'Fee'
WHEN BCode = 'CC' AND ECode = 'SD' AND SType = 'OF' THEN 'Fee'
ELSE BCode
END AS Type
You see 'Fee'. On front end web page, it shows up as 'Fee'. Same page has dropdown with 'Fee'.
When Fee as type value is sent to back end stored procedure, it should retrieve above
WHEN BCode IN ('SR', 'SD') and SType = 'MF' transactions.
Maybe this will work too:
select *, t.[Type]
from MyTable
cross apply (values (
case
when BCode IN ('SR', 'SD') and SType = 'MF' then 'Fee'
when BCode = 'CC' and ECode = 'SD' and SType = 'OF' then 'Fee'
else BCode end)) t([Type])
where @type is null or t.[Type] = @type
where @type is null (to return all rows) or the value of dropdown.
You need to repeat the logic:
WHERE (BCode IN ('SR', 'SD') and SType = 'MF' AND @dropdown = 'Fee') OR
(Other conditions AND @dropdown = 'somethingelse')
A slicker solution is to create a table that is keyed by these BCode, SType and the other columns with Dropdown as the result value. But there may be some effort in maintaining this table. (I don't know anything about your business.)