Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Which Sub Query of Mine Return More than One value. Its Hard to Find for me

select 
        T1.ItemCode, 
        T1.ItemName, 
        T1.U_PKMD,
        (select 
                sum( T2.OnHand) AS 'QTY' 
            from 
                OITW T2 
                    join OITM T3 
                        on T2.ItemCode = T3.ItemCode  
            where 
                    T3.[ItemCode] = T1.[ItemCode]   
                and WhsCode in ('KHI-04','KHI-12','RWP-01','LHR-01')) 
        - ( Select 
                    Quantity 
                from 
                    drf1  drf 
                        INNER JOIN OITM itm 
                            ON drf.[ItemCode] = itm.[ItemCode]
                where 
                    drf.[ItemCode] = T1.[ItemCode] )
    from 
        OITW T0  
            left JOIN OITM T1 
                ON T0.[ItemCode] = T1.[ItemCode] 
    where 
            WhsCode IN ('KHI-04', 'KHI12', 'LHR-01', 'RWP-O1') 
        and T1.OnHand IS NOT NULL 
        and ItmsGrpCod in ('101', '116')
        and ItemName not like '%Tool%' 
        and T1.OnHand != 0 
    order by 
        T1.ItemCode

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

>Solution :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

After reformatting, looking at the individual queries, it is in your SUBTRACTION query. Notice it is MINUS the select of all QUANTITY records from it. THAT is your culprit.

Should that only return 1 record, or the SUM( Quantity )

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading