Re: SQL error

[Sam_L <lennon_s_j@xxxxxxxxxxx>]

You don't make it easy to read/understand you SQL.  Just for grins, I 
did some reformatting:

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

Now I'm *guessing* that the problem with "TTBACK CONCAT TTBDCK" is that 
you have not included these field in your select. If you included 
something like "TTBACK CONCAT TTBDCK as MyConcat" in the select then you 
could use MyConcat in the group by.

(May I gently suggest that in future projects/posts you consider some 
indentation, perhaps some correlation names and perhaps use common table 
expressions.)

On 5/6/2024 3:19 AM, Merlino, Basilio (NissanContractor) 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