0

I got table with 2 IDs. I'm using parametrs as filters.

eg.

| ID  | DIFF_ID |
| ----| ------  |
| 1   | null    |
| 2   | 3       |
| 3   | null    |
...

As you can see Diff_ID has the same int as ID and I need to use DIFF_ID to show other values in table by using parametrs eg.

case when $P{aba} is true then DIFF_ID (...) = ID(?) end

if the paramter 'aba' is true then use DIFF ID as base but show up as ID.

I'm expecting DIFF_ID appear in ID column if the parameter is true.

Artico
  • 17
  • 4
  • Sounds like you want an alias in your query: `select case when $P{aba} then id else diff_id end as "ID" from test;` The value you'll get from this query will always show up as `ID` regardless of whether it was taken from `id` or `diff_id` column. You can also skip `is true` if the `aba` parameter is a boolean already. Also, mind the [case-sensitivity behaviour](https://stackoverflow.com/a/21798517/5298879). – Zegarek Oct 26 '22 at 08:52
  • @Zegarek Not really. I need to use DIFF_ID as ID when parameter is true. There are multiple columns. I'm sorry I can't provide exact tables and information as it's for a company I work for. – Artico Oct 26 '22 at 08:57
  • You'll need to specify *how* you need to use it. A *base* for what? Do you need `diff_id` to populate the `id` column permanently, if `aba` is true, or do you need it to just show up instead of the `id`, only in a given query? For example, for insertion to another table, to construct some text using one id or the other, to be used in an arithmetic operation? – Zegarek Oct 26 '22 at 09:02
  • [Here's an example of how you can already *use* that `case` or alias.](https://dbfiddle.uk/k5RoKynL) – Zegarek Oct 26 '22 at 09:11
  • @Zegarek Thanks for your input. Let me try to explain. In table we have "base" ID and DIFF_ID which appears when ID gets an apple. So then there could be 30 IDs and one gets an apple then on that one ID a DIFF_ID appears and IDs are now 31 but that one that has apple now has DIFF_ID while other IDs have null value in DIFF_ID. Now I need to use DIFF_IDs when parameter ABA is true. This parameter aba is can not be used in select only in where clause. – Artico Oct 26 '22 at 09:16
  • "Now I need to use DIFF_IDs when parameter ABA is true." I need to use DIFF_ID in ID. It's very hard to explain. – Artico Oct 26 '22 at 09:24
  • Please try to edit the question to reflect the new proposed input structure and what you just clarified. You can use the `case` expression in a `where` clause just as well `select * from test where case when aba then id1>2 else diff_id1>2 end;` You're just specifying your `where` filters according to your cases. [Example](https://dbfiddle.uk/m4gMH9Nj) – Zegarek Oct 26 '22 at 09:38
  • Using [`coalesce(diff_id,id)`](https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL) you can also use `id` only if `diff_id` is null for a given row. If `diff_id` won't be null in that row, it'll be taken instead of `id`. [Example](https://dbfiddle.uk/4yvI5YXz). – Zegarek Oct 26 '22 at 09:45
  • @Zegarek Thanks but that's not really it. When ABA parameter is set as true I need to only see DIFF_ID in ID column. It's already there but it's assigned on a different ID. Example there are two IDs - ID 1 and ID 2. ID 2 gets an apple then it gets DIFF_ID that's 3. So now there are 3 IDs - ID1, ID2 and ID3 but ID2 has DIFF_ID as well that's DIFF_ID 3. – Artico Oct 26 '22 at 09:57
  • I encourage you to edit and update the question, adding as much clarity as possible - everything necessary to reproduce and solve your problem should be there. If you make a comment that clarifies, expandes or changes the question, you need to reflect it in the question. Prepare a censored, safe-to-share version of your input table. Prepare an example output of the query you're trying to build. – Zegarek Oct 26 '22 at 10:23
  • **1.** "See DIFF_ID in ID column" in results of a `select` statement? **2.** How is DIFF_ID "assigned on a different ID" - another column, another row, or both? How can you tell what's assigned to what in your structure? **3.** "Example there are two IDs - ID 1 and ID 2. ID 2" - two rows, one with 1 in column ID, one with 2 in the same column? Or are these 1 and 2 in columns ID1 and ID2 of the same row? **4.** How can you tell ID2 "got an apple"? Is "getting an apple" just getting some data in DIFF_ID2 in the same row? Is ID3 the same thing as ID2's DIFF_ID or is it a new thing? – Zegarek Oct 26 '22 at 10:28

0 Answers0