How fix error in SQL query?

nickas123 21 Reputation points
2024-04-03T14:08:14.29+00:00

Hello,

I am working with database and having trouble with query. It generates error. Here is an query:

select
 	cast(pk.OperacijosId as int)		OPER_ID,
	o.OperacijosTipoId						as	OP_TIPO_KODAS,
 	pk.PardavimuKunoId 							REC_ID,
 	s.Kodas													SAND_KODAS,
 	replace(replace(p.Kodas,char(13),''),char(10),'') 	PREK_KODAS,
 	pk.Kiekis																		KIEKIS,
 	pk.vvKainaGalutineBePVM 										KAINA,
 	round(pk.ppKainaBePVM,2)										SUMA_BE_PVM,
 	round(pk.ppPVM,2)														PVM_SUMA,
 	round(pk.ppKainaSuPVM,2)										SUMA_IS_VISO,
 	isnull(SK.KiekisAtiduotinas,cast(0.0 as numeric(14,4)))							SAV_KIKEIS,
 	isnull(round(SK.Vnt_Kaina,4),cast(0.0 as numeric(14,4)))						SAV_KAINA,
 	isnull(round(SK.Savikaina,2),cast(0.0 as numeric(14,4)))						SAV_SUMA,
 	isnull(round(isnull(pk.ppKainaBePVM,cast(0.0 as numeric(14,4))) - isnull(SK.Savikaina,cast(0.0 as numeric(14,4))),2),cast(0.0 as numeric(14,4)))	PELNAS,
 	isnull(isnull(pk.Kiekis,cast(0.0 as numeric(14,4))) - isnull(SK.KiekisAtiduotinas,cast(0.0 as numeric(14,4))),cast(0.0 as numeric(14,4)))				KIEKIS_DIFF
	into es.PARDAVIMAI_L
 	from [85.206.13.2,33999].[SK_Energy_Green].[dbo].PardavimuKunai pk
 	left join [85.206.13.2,33999].[SK_Energy_Green].[dbo].Operacijos 		o	on (o.OperacijosId=pk.OperacijosId)
 	left join [85.206.13.2,33999].[SK_Energy_Green].[dbo].Sandeliai 		s on (s.SandeliaiId=pk.SandeliaiId)
 	left join [85.206.13.2,33999].[SK_Energy_Green].[dbo].Prekes 				p on (p.PrekesID=pk.PrekesID)
 	left join [85.206.13.2,33999].[SK_Energy_Green].[dbo].PrekiuGrupes	g on (g.PrekiuGrupesID=p.PrekiuGrupesId)
 	left join (select 
 						 pj.OperacijosKunoId,
 						 sum(pj.KiekisAtiduotinas)							KiekisAtiduotinas,
 						 iif(OperacijosTipoId=51,sum(pj.KiekisGautinas*pp.Savikaina), sum(pj.KiekisAtiduotinas*pp.Savikaina))	Savikaina,
 						 iif(sum(pj.KiekisAtiduotinas)=0,cast(0.0 as numeric(14,4)),sum(pj.KiekisAtiduotinas*pp.Savikaina)/sum(pj.KiekisAtiduotinas))	Vnt_Kaina
 						 from 			[85.206.13.2,33999].[SK_Energy_Green].[dbo].PartijuJudejimas pj
 						 left join	[85.206.13.2,33999].[SK_Energy_Green].[dbo].PrekiuPartijos pp on (pp.PrekiuPartijosId=pj.PrekiuPartijosId)
 						 group by pj.OperacijosKunoId
 						) SK on (SK.OperacijosKunoId=pk.PardavimuKunoId)
 	inner join es.PARDAVIMAI_H H on (pk.OperacijosId=H.OPER_ID);

After run query, I am getting this error:

Invalid column name 'OperacijosTipoId'. But I was selected before.

Error comes at this line:

iif(OperacijosTipoId=51,sum(pj.KiekisGautinas*pp.Savikaina), sum(pj.KiekisAtiduotinas*pp.Savikaina))	Savikaina,

I also tried used prefixes (alias) like pk, SK, but not working also. How I could resolve this?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,675 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 112.1K Reputation points
    2024-04-03T14:44:45.05+00:00

    Try to replace the left join (select ...) SK on ... with outer apply (select ...) SK without “on” and also add where pj.OperacijosKunoId = pk.PardavimuKunoId before group by.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Michael Taylor 47,806 Reputation points
    2024-04-03T14:39:05.9433333+00:00

    Use the table alias that you used in the select statement o.OperacijosTipoId. Better yet just copy that select line for the column and paste it over what you have now to ensure you spelled it correctly.


  2. Olaf Helper 40,736 Reputation points
    2024-04-04T04:57:49.2766667+00:00

    But I was selected before.

    Yes, but in the outer query; you can not use it in a sub query.


  3. CosmogHong-MSFT 22,626 Reputation points Microsoft Vendor
    2024-04-05T02:41:01.1833333+00:00

    Hi @nickas123

    It is generally meaningless to have a correlated subquery in the FROM clause because the table in the FROM clause is needed to evaluate the outer query, but the correlated subquery in the FROM clause can't be evaluated before the outer query is evaluated.

    As answered above, you need to use the keyword CROSS APPLY or OUTER APPLY instead of JOIN to achieve the effect.

    	outer apply(select 
     						 pj.OperacijosKunoId,
     						 sum(pj.KiekisAtiduotinas)							KiekisAtiduotinas,
     						 iif(o.OperacijosTipoId=51,sum(pj.KiekisGautinas*pp.Savikaina), sum(pj.KiekisAtiduotinas*pp.Savikaina))	Savikaina,
     						 iif(sum(pj.KiekisAtiduotinas)=0,cast(0.0 as numeric(14,4)),sum(pj.KiekisAtiduotinas*pp.Savikaina)/sum(pj.KiekisAtiduotinas))	Vnt_Kaina
     						 from 			[85.206.13.2,33999].[SK_Energy_Green].[dbo].PartijuJudejimas pj
     						 left join	[85.206.13.2,33999].[SK_Energy_Green].[dbo].PrekiuPartijos pp on (pp.PrekiuPartijosId=pj.PrekiuPartijosId)
     						 where (pj.OperacijosKunoId = pk.PardavimuKunoId)
    						 group by pj.OperacijosKunoId
     				) SK 
    
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments