SQL syntax

try this

SELECT     A.Nomor_PO, B.Tgl_PO, CASE WHEN YEAR(Tgl_PO) = 2009 THEN
                          (SELECT    top 1  Harga_Satuan
                            FROM          SCM_INV..TDPOBELI2009 AS Z
                            WHERE      Z.Nomor_PO = Nomor_PO AND Z.BahanCode = BahanCode) END AS Harga_Satuan_Beli
FROM         TDPOBELI2009 AS A LEFT OUTER JOIN
                      THPOBELI2009 AS B ON A.Nomor_PO = B.Nomor_PO
WHERE     (A.Nomor_PO = '3115 ')

Select allOpen in new window

SELECT   Harga_Satuan
                            FROM          SCM_INV..TDPOBELI2009 AS Z
                            WHERE      Z.Nomor_PO = Nomor_PO AND Z.BahanCode = BahanCode

This query Migth returning more than one row just make sure it will return only ione row like

SELECT    top 1  Harga_Satuan
                            FROM          SCM_INV..TDPOBELI2009 AS Z
                            WHERE      Z.Nomor_PO = Nomor_PO AND Z.BahanCode = BahanCode

you can try adding a GROUPBY clause

SELECT     A.Nomor_PO, B.Tgl_PO, CASE WHEN YEAR(Tgl_PO) = 2009 THEN
                          (SELECT     Harga_Satuan
                            FROM          SCM_INV..TDPOBELI2009 Z
                            WHERE      (Nomor_PO = Nomor_PO) AND (BahanCode = BahanCode)
                            GROUP BY Harga_Satuan) END AS Harga_Satuan_Beli
FROM         SCM_INV..TDPOBELI2009 A LEFT OUTER JOIN
                      dbo.THPOBELI2009 B ON A.Nomor_PO = B.Nomor_PO
GROUP BY A.Nomor_PO, B.Tgl_PO, CASE WHEN YEAR(Tgl_PO) = 2009 THEN
                          (SELECT     Harga_Satuan
                            FROM          SCM_INV..TDPOBELI2009 Z
                            WHERE      (Nomor_PO = Nomor_PO) AND (BahanCode = BahanCode)
                            GROUP BY Harga_Satuan) END
HAVING      (A.Nomor_PO = '3115 ')

Select allOpen in new window

Hi pratima_mcs,

Why it returns more than one row?
At the first query, it returns one row:

SELECT     A.Nomor_PO, B.Tgl_PO, A.BahanCode
FROM         TDPOBELI2009 AS A LEFT OUTER JOIN
                      THPOBELI2009 AS B ON A.Nomor_PO = B.Nomor_PO
WHERE     (A.Nomor_PO = '3115 ')

Thank you.

Hi phoenixfire425,

Please see my comment to pratima.

I am not suppose to use group, since it returns only one row at first query.

Thank you.

have you tried this top 1

SELECT    top 1  Harga_Satuan
                            FROM          SCM_INV..TDPOBELI2009 AS Z
                            WHERE      Z.Nomor_PO = Nomor_PO AND Z.BahanCode = BahanCode

is it working ?

Top 1 will make sure that it will retun only one row

Typically when you have something returning more than one value then there is some deviation in the data.  Select all of the rows and see if you can see the difference between the two rows.

Yes, Pratima, it works.

But why it shows the message when the actual row only one?

Thank you.

Hi phoenixfire425,

-Typically when you have something returning more than one value then there is some deviation in the data.  Select all of the rows and see if you can see the difference between the two rows.

I don't get it?

Thank you.

Not sure
might for some case it is returning multiple rows

Run this and then compare the two rows.  If you see a difference in the columns then thats why you are creating multiple rows.

SELECT     *, CASE WHEN YEAR(Tgl_PO) = 2009 THEN
                          (SELECT     Harga_Satuan
                            FROM          SCM_INV..TDPOBELI2009 AS Z
                            WHERE      Z.Nomor_PO = Nomor_PO AND Z.BahanCode = BahanCode) END AS Harga_Satuan_Beli
FROM         TDPOBELI2009 AS A LEFT OUTER JOIN
                      THPOBELI2009 AS B ON A.Nomor_PO = B.Nomor_PO
WHERE     (A.Nomor_PO = '3115 ')

Select allOpen in new window

Hi phoenixfire425,

SELECT     *, CASE WHEN YEAR(Tgl_PO) = 2009 THEN
                          (SELECT     Harga_Satuan
                            FROM          SCM_INV..TDPOBELI2009 AS Z
                            WHERE      Z.Nomor_PO = Nomor_PO AND Z.BahanCode = BahanCode) END AS Harga_Satuan_Beli
FROM         TDPOBELI2009 AS A LEFT OUTER JOIN
                      THPOBELI2009 AS B ON A.Nomor_PO = B.Nomor_PO
WHERE     (A.Nomor_PO = '3115 ')


Exception occurs.


SELECT     *
FROM         TDPOBELI2009 AS A LEFT OUTER JOIN
                      THPOBELI2009 AS B ON A.Nomor_PO = B.Nomor_PO
WHERE     (A.Nomor_PO = '3115 ')

Return just one row?

Thank you.

Hi Pratima,

SELECT     A.Nomor_PO, B.Tgl_PO, CASE WHEN YEAR(Tgl_PO) = 2009 THEN
                          (SELECT     COUNT(Harga_Satuan)
                            FROM          SCM_INV..TDPOBELI2009 AS Z
                            WHERE      Z.Nomor_PO = Nomor_PO AND Z.BahanCode = BahanCode) END AS Harga_Satuan_Beli
FROM         TDPOBELI2009 AS A LEFT OUTER JOIN
                      THPOBELI2009 AS B ON A.Nomor_PO = B.Nomor_PO
WHERE     (A.Nomor_PO = '3115 ')

Result :

Harga_Satuan_Beli = 310



SELECT     COUNT(*) AS Count
FROM         TDPOBELI2009
WHERE     (Nomor_PO = '3115                          ')

Result :

Count = 1  

Weird?

Thank you.

Hi phoenixfire425,

It has many rows.

Thank you.

your welcome

Hi phoenixfire425,

I try below and return one rows.

SELECT     Code, Nomor_PO, BahanCode, Qty_PO, Harga_Satuan, Pct_Disc, Harga_Net, Qty_DO, Qty_SJ, Qty_Fkt, Qty_Retur, Satuan, KdSts, CrtId, CrtDate, UpdId,
                      UpdDate
FROM         TDPOBELI2009 AS Z
WHERE     (Nomor_PO = Nomor_PO) AND (BahanCode = BahanCode) AND (Nomor_PO = '3115 ')

Thank you.

Hi All,

I know the problem now.

SELECT     A.Nomor_PO, B.Tgl_PO, CASE WHEN YEAR(Tgl_PO) = 2009 THEN
                          (SELECT     Harga_Satuan
                            FROM          SCM_INV..TDPOBELI2009 AS Z
                            WHERE      Z.Nomor_PO = A.Nomor_PO AND Z.BahanCode = A.BahanCode) END AS Harga_Satuan_Beli
FROM         TDPOBELI2009 AS A LEFT OUTER JOIN
                      THPOBELI2009 AS B ON A.Nomor_PO = B.Nomor_PO
WHERE     (A.Nomor_PO = '3115 ')


WHERE Z.Nomor_PO = Nomor_PO AND Z.BahanCode = BahanCode

Should be

WHERE  Z.Nomor_PO = A.Nomor_PO AND Z.BahanCode = A.BahanCode

Thank you.

Thanks to both of you.