Link to home
Create AccountLog in
Avatar of emi_sastra
emi_sastra

asked on

SQL syntax

Hi All,

I have below queries:

1. Result one row.
2. Exception. Sub query return more than 1 value ?

What's the problem?

Thank you.


1 . First Query
 
SELECT     A.Nomor_PO, B.Tgl_PO 
FROM         TDPOBELI2009 AS A LEFT OUTER JOIN
                      THPOBELI2009 AS B ON A.Nomor_PO = B.Nomor_PO
WHERE     (A.Nomor_PO = '3115 ')
 
2. Second Query
 
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 = 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 ')

Open in new window

Avatar of Pratima
Pratima
Flag of India image

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 ')

Open 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 ')

Open in new window

Avatar of emi_sastra
emi_sastra

ASKER

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 ')

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Pratima
Pratima
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.