Re: Suggestion fo SQL

Hi Gio,

you mean, you want an "empty" line, if there is no corresponding line for the same type, year and month?

So there is no record for type OR for 2028/07 - so there should be an empty line?

You should create a CTE with all the months in You table, and another with all the types from your table.

Next cross join this CTEs to get every combination - and then join that against the data from your table. 

Right now I'm on the road - but later that evening I could try to do it. 

HTH
Daniel



> Am 21.08.2025 um 18:40 schrieb gio.cot via RPG400-L <[email protected]>:
> 
> Hi All
> 
> 
> 
> I have a table like this:
> 
> 
> 
> Type   Year   Month           Code            value
> 
> FT         2028    07                      000001             656,22
> 
> OR       2028    06                      000001             894,01
> 
> FT         2025    08                      000001             123,45
> 
> OR       2025    08                      000001             444,45
> 
> 
> 
> 
> 
> I would need to get this result with SQL
> 
> 
> 
> Type   Year   Month           Code            value            Type2 Year2
> Month 2         Code2          Value2          
> 
> FT         2028    07                      000001             656,22
> -            -            -                         -
> -
> 
> -            -                         -                         -
> -                         OR       2028    06                      000001
> 894,01
> 
> FT         2025    08                      000001             123,45
> OR       2025    08                      000001             444,45
> 
> 
> 
> How could do that ?  (If is possible ??)
> 
> 
> 
> Thanks in advance
> 
> Gio
> 
> 
> 
> 
> 
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
> 
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.