[ad_1]
現在 SSRS に変換中の Crystal Report があります。 このレポートには、MTD (月から今日までの値) の 3 列と YTD (年から今日までの値) の 3 列があります。 これら 3 つの列を個別に取得することはできますが、データの精度を高めるために、1 つの SQL ステートメントに結合して 1 行に出力する必要があります。 SQL の合体関数を使用して、それにほぼ近づけることができました。 問題は、group by コマンドを使用できず、エラーがスローされることです。 以下の Crystal Report に示されているように、SQL クエリの何を編集すればよいですか?
エラー :
Msg 512, Level 16, State 1, Line 106 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
編集: サンプル dbfiddle を作成しました: DB フィドル – SQL データベース プレイグラウンド[^]
コード2では、ユニオンといくつかを使用しようとしましたが、次のようなエラーが発生します。
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
私が試したこと:
コード1
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT coalesce((Select ((SUM( "IS_vwSalesbyProductLineComp"."sls_amt")-SUM( "IS_vwSalesbyProductLineComp"."cost_amt"))/SUM( "IS_vwSalesbyProductLineComp"."sls_amt"))*100 ),0) AS G1MTD, "IS_vwSalesbyProductLineComp"."Mainslspsn_name", "IS_vwSalesbyProductLineComp"."PCSummarized", coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."qty_to_ship"-"IS_vwSalesbyProductLineComp"."qty_return_to_stk")FROM "100"."dbo"."IS_vwSalesbyProductLineComp" where Year = 2021 and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS Qty_Sold, coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."sls_amt")FROM "100"."dbo"."IS_vwSalesbyProductLineComp" where Year = 2021 and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS YTD_Sales, coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."cost_amt")FROM "100"."dbo"."IS_vwSalesbyProductLineComp" where Year = 2021 and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS YTD_COGS, coalesce((Select ((SUM( "IS_vwSalesbyProductLineComp"."sls_amt")-SUM( "IS_vwSalesbyProductLineComp"."cost_amt"))/SUM( "IS_vwSalesbyProductLineComp"."sls_amt"))*100 FROM "100"."dbo"."IS_vwSalesbyProductLineComp" where Year = 2021 and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS G2YTD, SUM("IS_vwSalesbyProductLineComp"."qty_to_ship"-"IS_vwSalesbyProductLineComp"."qty_return_to_stk") AS Qty_Sold, SUM("IS_vwSalesbyProductLineComp"."sls_amt") AS MTD_Sales, SUM("IS_vwSalesbyProductLineComp"."cost_amt") AS MTD_COGS FROM "100"."dbo"."IS_vwSalesbyProductLineComp" where Year = 2021 and Month = 12 Group by Mainslspsn_name,PCSummarized order by PCSummarized
コード2
SET ARITHABORT OFF SET ANSI_WARNINGS OFF WITH T1 AS ( SELECT ( SELECT((SUM(IS_vwSalesbyProductLineComp.sls_amt) - SUM(IS_vwSalesbyProductLineComp.cost_amt)) / SUM(IS_vwSalesbyProductLineComp.sls_amt)) * 100) AS G1MTD, IS_vwSalesbyProductLineComp.Mainslspsn_name, IS_vwSalesbyProductLineComp.PCSummarized, ( SELECT SUM(IS_vwSalesbyProductLineComp.qty_to_ship - IS_vwSalesbyProductLineComp.qty_return_to_stk) FROM "100".dbo.IS_vwSalesbyProductLineComp WHERE Year = 2021 AND Month BETWEEN 1 AND 12 GROUP BY Mainslspsn_name, PCSummarized) AS Qty_Sold, ( SELECT SUM(IS_vwSalesbyProductLineComp.sls_amt) FROM "100".dbo.IS_vwSalesbyProductLineComp WHERE Year = 2021 AND Month BETWEEN 1 AND 12 GROUP BY Mainslspsn_name, PCSummarized) AS YTD_Sales, ( SELECT SUM(IS_vwSalesbyProductLineComp.cost_amt) FROM "100".dbo.IS_vwSalesbyProductLineComp WHERE Year = 2021 AND Month BETWEEN 1 AND 12 GROUP BY Mainslspsn_name, PCSummarized) AS YTD_COGS, ( SELECT((SUM(IS_vwSalesbyProductLineComp.sls_amt) - SUM(IS_vwSalesbyProductLineComp.cost_amt)) / SUM(IS_vwSalesbyProductLineComp.sls_amt)) * 100 FROM "100".dbo.IS_vwSalesbyProductLineComp WHERE Year = 2021 AND Month BETWEEN 1 AND 12 GROUP BY Mainslspsn_name, PCSummarized) AS G2YTD, SUM(IS_vwSalesbyProductLineComp.qty_to_ship - IS_vwSalesbyProductLineComp.qty_return_to_stk) AS Qty_Sold2, --> renamed because ambigous SUM(IS_vwSalesbyProductLineComp.sls_amt) AS MTD_Sales, SUM(IS_vwSalesbyProductLineComp.cost_amt) AS MTD_COGS FROM "100".dbo.IS_vwSalesbyProductLineComp WHERE Year = 2021 AND Month = 12 GROUP BY Mainslspsn_name, PCSummarized ), T2 AS ( SELECT 0 AS C1, '' AS C2, '' AS C3, 0 AS C4, 0 AS C5, 0 AS C6, 0 AS C7, 0 AS C8, 0 AS C9, 0 AS C10 ), T3 AS ( SELECT * FROM T1 UNION SELECT * FROM T2 ) SELECT SUM(G1MTD) AS G1MTD, (SELECT [text()] = Mainslspsn_name FROM "100".dbo.IS_vwSalesbyProductLineComp FOR XML PATH(''), TYPE ).value ( './text()[1]', 'nvarchar(max)' ), (SELECT [text()] = PCSummarized FROM "100".dbo.IS_vwSalesbyProductLineComp FOR XML PATH(''), TYPE ).value ( './text()[1]', 'nvarchar(max)' ), SUM(Qty_Sold) AS Qty_Sold, SUM(YTD_Sales) AS YTD_Sales, SUM(YTD_COGS) AS YTD_COGS, SUM(G2YTD) AS G2YTD, SUM(Qty_Sold2) AS Qty_Sold2, SUM(MTD_Sales) AS MTD_Sales, SUM(MTD_COGS) AS MTD_COGS FROM T3 GROUP BY Mainslspsn_name, PCSummarized ORDER BY PCSummarized;
解決策 1
サンプル データがない場合は、まず次の点に対処します。
— 100.dbo.IS_vwSalesbyProductLineComp は有効な SQL 識別子ではありません。 SQL 識別子は文字またはアンダースコアで始まる必要があります
— 1 つのテーブルからのみ選択する場合は、列でテーブル名を修飾する必要はありません。
— 列を修飾する場合は、完全なテーブル名の代わりにエイリアスを使用して、スクリプトを読みやすくします。
select IS_vwSalesbyProductLineComp.sls_amt from IS_vwSalesbyProductLineComp
使用
select splc.sls_amt from IS_vwSalesbyProductLineComp splc
— 列名とテーブル名の区切り文字を削除します。区切り文字は必要なく、クエリを読み取れなくなるだけです。
— 区切り文字を使用する場合は、次を使用します。 [ ] つまり、代わりに "IS_vwSalesbyProductLineComp"
使用 [IS_vwSalesbyProductLineComp]
– 繰り返しになりますが、より読みやすくなりました
— あなたの発言
引用:ただし、データの精度を高めるために、それを 1 つの SQL ステートメントに結合する必要があります。
有効じゃない。 使用される SQL ステートメントの数は、データの精度にはまったく関係ありません。 すべてを 1 つのステートメントにまとめることにこだわると、明瞭さ、正確さ (データではなくスクリプトの)、パフォーマンスに悪影響を及ぼす可能性があります。
— これらすべてのサブクエリはまったく必要ありません。さらに、ご覧のとおり、複数の行が返されます。
必要なすべてのデータを個別の行で取得します。単一の SQL ステートメントであることを主張する場合は、共通テーブル式 (CTE – WITH common_table_expression (Transact-SQL) – SQL Server | Microsoft ドキュメント[^] )。
それから ピボット これらの結果から必要なテーブルを取得します – を参照してください。 SQL Server PIVOT 演算子を実際の例でわかりやすく説明[^]
解決策 2
一見すると…あなたのクエリは本当に悪いです:
最も重要な間違いは次のとおりです。 SELECTが多すぎる!
メインが 1 つあります SELECT
そして4つのサブSELECT
!
より少ない数を使用できるようにクエリを書き直してみてください。 SELECT
。
すべてのサブクエリを確認してください。 いずれかのクエリが複数のレコードを返す場合は、そのクエリを改善する必要があります。
それまでの間、これを読んでください。 SQL Server のメッセージ 512「サブクエリが複数の値を返しました」を修正[^]
解決策 5
https://www.google.com/search?q=standale+to+故郷+水+火+復興
しかし、素晴らしい投稿です。この件についてもう少し詳しく書いていただけないでしょうか?
もう少し詳しく教えていただけると大変ありがたいです。
乾杯!
[ad_2]
コメント