RE: SQL error

I'd expect that || will cause problems, because the pipe may not be international (different hex codes in different language environments) while CONCAT is international

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Moran
Sent: Monday, 6 May 2024 10:25
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL error

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.

--
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.