Re: SQL error -- RPG400-L

Out of curiosity, does the result change if you use || instead of concat?
(It shouldn't, but I wonder if the concat itself is causing an issue)

Another good test may be to try it in ACS. You'll have to define it a
little differently, but may help you find a way to make it work.

Respectfully,

Daniel Moran

On Mon, May 6, 2024 at 4:19 PM Merlino, Basilio (NissanContractor) <
b.merlino-extern@xxxxxxxxx> wrote:

Dear All,

I' m asking your opinion about the following behavior.

First SQL
The follow sql does not work. It's return SQL0122 error "Column *N or
expression in select is not valid"

Select 'K' AS TIPO , 1 AS PERIODO , TTBACK CONCAT TTBDCK AS ELEMENTO1
, TTBPCK AS ELEMENTO2 , count(*) AS QUANTITA ,
IFNULL((Select COUNT(*) FROM SNVEI0F INNER JOIN IBM_DAT/SNCON0F
ON B5CSCK = BNCSCK INNER JOIN IBM_DAT/SNSLL0F ON B5BACK = IMBACK
and IMKDCK = 'DV1' INNER JOIN IBM_DAT/SNSLR0F ON
IMKDCK = N2KDCK and IMCCKK = N2CCKK WHERE B5BACK CONCAT
B5BDCK = TTBACK CONCAT TTBDCK AND B5BPCK = TTBPCK AND
B5CFDT <= 1230131 AND B5CWDT > 1230131 AND B5CFDT <> 0 AND B5BWST
IN ('D') AND B5JDST in (' ' , 'H') AND B5BACK IN ('A01')
) , 0) AS QUANTITAORDINI ,
0 AS QUANTITALIBERA FROM IBM_DAT/SNCID0F INNER JOIN
IBM_DAT/SNCON0F ON TTCSCK = BNCSCK INNER JOIN IBM_DAT/SNSLL0F
ON TTBACK = IMBACK and IMKDCK = 'DV1' INNER JOIN IBM_DAT/SNSLR0F
ON IMKDCK = N2KDCK and IMCCKK = N2CCKK WHERE TTH2DS >= 20230101
AND TTH2DS <= 20230131 AND TTBACK IN ('A01')
GROUP BY 'K', 1 , TTBACK CONCAT TTBDCK , TTBPCK

Second SQL
The follow sql work fine. It's the same of the previous without che
concat expression in the last group by clause

Select 'K' AS TIPO , 1 AS PERIODO , TTBACK CONCAT TTBDCK AS ELEMENTO1
, TTBPCK AS ELEMENTO2 , count(*) AS QUANTITA ,
IFNULL((Select COUNT(*) FROM SNVEI0F INNER JOIN IBM_DAT/SNCON0F
ON B5CSCK = BNCSCK INNER JOIN IBM_DAT/SNSLL0F ON B5BACK = IMBACK
and IMKDCK = 'DV1' INNER JOIN IBM_DAT/SNSLR0F ON
IMKDCK = N2KDCK and IMCCKK = N2CCKK WHERE B5BACK CONCAT
B5BDCK = TTBACK CONCAT TTBDCK AND B5BPCK = TTBPCK AND
B5CFDT <= 1230131 AND B5CWDT > 1230131 AND B5CFDT <> 0 AND B5BWST
IN ('D') AND B5JDST in (' ' , 'H') AND B5BACK IN ('A01')
) , 0) AS QUANTITAORDINI ,
0 AS QUANTITALIBERA FROM IBM_DAT/SNCID0F INNER JOIN
IBM_DAT/SNCON0F ON TTCSCK = BNCSCK INNER JOIN IBM_DAT/SNSLL0F
ON TTBACK = IMBACK and IMKDCK = 'DV1' INNER JOIN IBM_DAT/SNSLR0F
ON IMKDCK = N2KDCK and IMCCKK = N2CCKK WHERE TTH2DS >= 20230101
AND TTH2DS <= 20230131 AND TTBACK IN ('A01')
GROUP BY 'K', 1 , TTBACK , TTBDCK , TTBPCK

Third SQL
The follow sql work fine. It's the same of the first without SUBQUERY in
the select sentence with the CONCAT expression in the group by

Select 'K' AS TIPO , 1 AS PERIODO ,
TTBACK concat TTBDCK as
ELEMENTO1
, TTBPCK AS ELEMENTO2 , count(*) AS QUANTITA ,
0 AS QUANTITALIBERA FROM IBM_DAT/SNCID0F INNER JOIN
IBM_DAT/SNCON0F ON TTCSCK = BNCSCK INNER JOIN IBM_DAT/SNSLL0F
ON TTBACK = IMBACK and IMKDCK = 'DV1' INNER JOIN IBM_DAT/SNSLR0F
ON IMKDCK = N2KDCK and IMCCKK = N2CCKK WHERE TTH2DS >= 20230101
AND TTH2DS <= 20230131 AND TTBACK IN ('A01')
GROUP BY 'K', 1 , TTBACK concat TTBDCK , TTBPCK

Same problem if i use a substr expression

So it's seem that when i place a subquery in a select the group by clause
can't contains any complex expression
Honestly i don't find nothing in google related this topic

Thanks

Basilio

***********************************************************************************************************
CONFIDENTIALITY NOTICE

This message is for the named person's use only. It may contain
confidential, proprietary or legally privileged information.

If you receive this message in error, please immediately delete it and all
copies of it from your system, destroy any hard copies of it and notify us
by email to email.security@xxxxxxxxxxxxxxxxx with a copy of this message.
You must not, directly or indirectly, use, disclose, distribute, print or
copy any part of this message if you are not the intended recipient. NISSAN
EUROPE and any of its subsidiaries each reserves the right to monitor all
e-mail communications through its networks.

NISSAN EUROPE is neither liable for the proper, complete transmission of
the information contained in this communication nor any delay in its
receipt. This email was scanned for the presence of computer viruses. In
the unfortunate event of infection NISSAN EUROPE does not accept liability.

Any views expressed in this message are those of the individual sender,
except where the message states otherwise and the sender is authorised to
state them.

***********************************************************************************************************
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.