This is my SQL query:
SELECT
GXFINENTRYLINES.GXID, GXFINENTRY.GXID FINENTRYGXID,
GXTRADER.GXNAME, GXFINENTRYLINES.GXLINENUM,
GXFINENTRYLINES.GXTRNVALUE
FROM
GXFINENTRYLINES
LEFT JOIN
GXTRADEENTRY ON GXFINENTRYLINES.GXTENTID = GXTRADEENTRY.GXID
LEFT JOIN
GXFINENTRY ON GXFINENTRYLINES.GXFENTID = GXFINENTRY.GXID
INNER JOIN
GXFINENTRYTYPE ON GXFINENTRY.GXFETPID = GXFINENTRYTYPE.GXID
LEFT JOIN
GXTRADER ON GXFINENTRYLINES.GXTRDRID = GXTRADER.GXIDyour`
And the result is like this :
| GXFINENTRYLINES.GXID | FINENTRYGXID | GXNAME | LINENUM | GXTRNVAL |
|---|---|---|---|---|
| BA4A846A-2D3E-7974-DC0B-018A02C26931 | D0CFE900-8A25-BF6E-1125-018A02C226F0 | NAME_A | -1 | 3800.00 |
| 5BBB6000-34FE-43B7-C173-018A02C27527 | D0CFE900-8A25-BF6E-1125-018A02C226F0 | NULL | 1 | 3300.00 |
| C8529015-DC40-EA1E-36A1-018A02E3F088 | D0CFE900-8A25-BF6E-1125-018A02C226F0 | NULL | 2 | 500.00 |
| BE224ED1-A770-62C3-DB71-0185DDB1B62C | 2E1CE68A-5D4A-BBD8-021F-0185DDB0CB13 | NAME_B | -1 | 2500.00 |
| 95F1F307-143F-8864-A987-0185DDB1F457 | 2E1CE68A-5D4A-BBD8-021F-0185DDB0CB13 | NULL | 1 | 2500.00 |
But what I try to find and I want as a result is to have the name in GXNAME and not NULL where LINENUM <> -1 and where the id in column B (FINENTRYGXID) is the same.
The result should look like this:
| GXFINENTRYLINES.GXID | FINENTRYGXID | GXNAME | LINENUM | GXTRNVAL |
|---|---|---|---|---|
| BA4A846A-2D3E-7974-DC0B-018A02C26931 | D0CFE900-8A25-BF6E-1125-018A02C226F0 | NAME_A | -1 | 3800.00 |
| 5BBB6000-34FE-43B7-C173-018A02C27527 | D0CFE900-8A25-BF6E-1125-018A02C226F0 | NAME_A | 1 | 3300.00 |
| C8529015-DC40-EA1E-36A1-018A02E3F088 | D0CFE900-8A25-BF6E-1125-018A02C226F0 | NAME_A | 2 | 500.00 |
| BE224ED1-A770-62C3-DB71-0185DDB1B62C | 2E1CE68A-5D4A-BBD8-021F-0185DDB0CB13 | NAME_B | -1 | 2500.00 |
| 95F1F307-143F-8864-A987-0185DDB1F457 | 2E1CE68A-5D4A-BBD8-021F-0185DDB0CB13 | NAME_B | 1 | 2500.00 |
I would be grateful for your help !
Without seeing some sample data it's not possible to be be concise, however I suspect you could use a windowed aggregate for the column here:
..., max(GXTRADER.GXNAME) over(partition by FINENTRYGXID) as GXNAME,...