This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(57.599999999999994, 32%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EN%3C/text%3E%3C/svg%3E)
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?
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 32%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMT%3C/text%3E%3C/svg%3E)
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.
I tried butt getting The multi-part identifier "o.OperacijosTipoId" could not be bound.
Oh, I see. You're using a subquery inside your join. Inside this subquery you don't have access to the outer queries tables because that hasn't run yet. Since you need access to that [85.206.13.2,33999].[SK_Energy_Green].[dbo].Operacijos table inside the subquery you need to join it in the subquery as well. Then you have access to it.
I don't understand your DB structure so I'm not going to try to figure out which tables/columns you need in both sets of queries. But I will say that if you have that table in your subquery then consider selecting the column(s) you need from that table in your subsquery. Then in the outer query you can eliminate the join against the o table as you already have the results from the subquery call.
Could you demonstrate it because I am not sure how implement it.
I cannot because I don't understand all your table relationships. They seem overly complex and normalized.
Ultimately you need to add a left join inside your subquery to the Operacijos table. If that table doesn't have any related columns to the other tables in your subquery then you'll need to add additional joins in the subquery to bring in additional tables until you can relate the Operacijos row you care about in your iif expression to the rows being processed in the subquery. For example, in your main query you used the PardavimuKunai table to get to the Operacijos rows. Do that same thing in your subquery but to get to the PartijuJudejimas rows you're querying.
But I was selected before.
Yes, but in the outer query; you can not use it in a sub query.
How make accessible it in subquery?
Use OUTER APPLY as described in Viorel's post.
But please understand that we have very little knowledge of your tables or columns, so it is very difficult for us to tell what the correct solution is.
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".