質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

3回答

4263閲覧

OUTER JOINの際のエラー

Ry22434

総合スコア15

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2019/04/30 10:25

前提・実現したいこと

会員マスタテーブルとトランザクションテーブルを結合し、年月別・会員のうち、どのくらいの数の方が購買を行っているか知りたいです。SQL歴2週間のため、問題になっている個所以外にも課題があるかもしれませんが、ご教示いただけますと幸いです。

問題

  1. OUTER JOINでテーブル結合をする前に、会員マスタテーブルのみでの集計を行いましたが、以下のエラーは発生しませんでした。下記コードのエラー内容を教えていただきたいです。

conversion failed when converting date and/or time from character string

  1. 会員マスターテーブルの中から年月別の(A)会員数、(B)会員のうち(情報登録数)の集計、(C)その会員のうち何人が購買をしているかを集計しており、トランザクションテーブルから(D)特定店舗(TBL_TRX.[DTP_COMPANY] = '003')での購買異数の集計を行いたいのですが、以下のコードにエラーがあると思っており、(A) = (D)となってしまいます。

SUM(CASE WHEN TBL_TRX.[DTP_MEMBER_ID] IS NOT NULL THEN 1 ELSE 0 END) AS '003_TRANSACT',

イメージ説明

発生している問題・エラーメッセージ

conversion failed when converting date and/or time from character string

該当のソースコード

SELECT year(CONVERT(date, TBL_MEMBER.[PMP_CARD_ISSUE_DATE])) AS 'YEAR', month(CONVERT(date, TBL_MEMBER.[PMP_CARD_ISSUE_DATE])) AS 'MONTH', COUNT(TBL_MEMBER.[PMP_MEMBER_ID]) AS 'MEMBER_COUNT', SUM(CASE WHEN TBL_MEMBER.[PMP_LAST_TRX_DATE] IS NOT NULL THEN 1 ELSE 0 END) AS 'TRANSACT', SUM(CASE WHEN TBL_TRX.[DTP_MEMBER_ID] IS NOT NULL THEN 1 ELSE 0 END) AS '003_TRANSACT', SUM(CASE WHEN TBL_MEMBER.[PMP_MOBILE_PHONE] IS NOT NULL THEN 1 ELSE 0 END) AS 'ACTIVATED', CASE WHEN TBL_MEMBER.[PMP_CARD_TYPE] IS NULL THEN 0 WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 1 THEN 1 WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 0 THEN 0 ELSE 0 END AS 'CARD_TYPE', TBL_MEMBER.[PMP_ISSUER_CODE], TBL_MEMBER.[PMP_ISSUER_NAME] FROM [DWH_PONTA].[dbo].[PLMS_MEMBER_PROFILE] AS TBL_MEMBER LEFT OUTER JOIN [DWH_PONTA].[dbo].[DAILY_TRX_PLMS] AS TBL_TRX ON TBL_MEMBER.[PMP_ISSUER_CODE] = '003' AND TBL_MEMBER.[PMP_CARD_ISSUE_DATE] <> '0000-00-00' AND TBL_MEMBER.[PMP_MEMBER_ID] = TBL_TRX.[DTP_MEMBER_ID] AND TBL_TRX.[DTP_COMPANY] = '003' GROUP BY year(CONVERT(date, TBL_MEMBER.[PMP_CARD_ISSUE_DATE])), month(CONVERT(date, TBL_MEMBER.[PMP_CARD_ISSUE_DATE])), CASE WHEN TBL_MEMBER.[PMP_CARD_TYPE] IS NULL THEN 0 WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 1 THEN 1 WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 0 THEN 0 ELSE 0 END, TBL_MEMBER.[PMP_ISSUER_CODE], TBL_MEMBER.[PMP_ISSUER_NAME] ORDER BY YEAR, MONTH

試したこと

補足情報(FW/ツールのバージョンなど)

Microsoft SQL

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

ベストアンサー

conversion failed when converting date and/or time from character string
のエラーについては、PMP_CARD_ISSUE_DATEが日付型に変換できない内容であるからだと推測します。

DAILY_TRX_PLMSと結合されるデータについては問題ないのでしょうが、外部結合にする事によってそれらのデータが対象になりエラーになっているのだと思います。

どのような書式なのか分かりませんが、0000-00-00の様な書式だとして

SQL

1 substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 4) as YERA_PART 2, substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 6, 2) as MONTH_PART

のように文字列の部分的な抽出とすれば良いかと。

追記

問題になっている個所以外にも課題があるかもしれませんが

SQLの記述的には、抽出条件と結合条件が明確になっていませんね。
※記述変更するとインデックスの状況によっては、レスポンスが変化する可能性があります。
後はネストさせると可読性は上がると思います。
上記を踏まえ色々と無駄を省くと、以下のようなSQLになります。

SQL

1select 2 substring(CARD_ISSUE_MONTH, 1, 4) As YEAR, substring(CARD_ISSUE_MONTH, 6, 2) AS MMONTH 3 , CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME 4 , COUNT(PMP_MEMBER_ID) AS MEMBER_COUNT 5 , SUM(TRANSACT) AS TRANSACT_COUNT 6 , SUM(TRANSACT_003) AS TRANSACT_003_COUNT 7 , SUM(ACTIVATED) AS ACTIVATED_COUNT 8from ( 9 SELECT 10 TBL_MEMBER.* 11 , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 7) AS CARD_ISSUE_MONTH 12 , CASE 13 WHEN TBL_MEMBER.[PMP_CARD_TYPE] IS NULL THEN 0 14 WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 1 THEN 1 15 WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 0 THEN 0 16 ELSE 0 17 END AS CARD_TYPE 18 , CASE WHEN TBL_MEMBER.[PMP_LAST_TRX_DATE] IS NOT NULL THEN 1 ELSE 0 END AS TRANSACT 19 , CASE WHEN TBL_TRX.[DTP_MEMBER_ID] IS NOT NULL THEN 1 ELSE 0 END AS TRANSACT_003 20 , CASE WHEN TBL_MEMBER.[PMP_MOBILE_PHONE] IS NOT NULL THEN 1 ELSE 0 END AS ACTIVATED 21 FROM 22 [DWH_PONTA].[dbo].[PLMS_MEMBER_PROFILE] AS TBL_MEMBER 23 LEFT OUTER JOIN [DWH_PONTA].[dbo].[DAILY_TRX_PLMS] AS TBL_TRX 24 ON TBL_MEMBER.[PMP_MEMBER_ID] = TBL_TRX.[DTP_MEMBER_ID] 25 AND TBL_MEMBER.[PMP_ISSUER_CODE]=TBL_TRX.[DTP_COMPANY] 26 WHERE 27 TBL_MEMBER.[PMP_ISSUER_CODE] = '003' 28 AND TBL_MEMBER.[PMP_CARD_ISSUE_DATE] <> '0000-00-00' 29 ) as base 30GROUP BY 31 CARD_ISSUE_MONTH, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME 32ORDER BY 33 CARD_ISSUE_MONTH

投稿2019/05/01 03:42

編集2019/05/01 07:56
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Ry22434

2019/05/03 05:05

ありがとうございます。INNER JOINとOUTER JOINの違いやWHERE句・ON句の使い方を理解できていませんでした。おっしゃられた通り、結局サブクエリを使わざるを得ず、いただいた回答例の通りとなりました。ありがとうございます。
guest

0

結合条件は以下の通りですが選択がうまく働かないと思います。

SQL

1ON TBL_MEMBER.[PMP_ISSUER_CODE] = '003' AND TBL_MEMBER.[PMP_CARD_ISSUE_DATE] <> '0000-00-00' AND 2TBL_MEMBER.[PMP_MEMBER_ID] = TBL_TRX.[DTP_MEMBER_ID] AND 3TBL_TRX.[DTP_COMPANY] = '003'

OUTER JOIN

OUTER JOIN ではTBL_MEMBER.[PMP_MEMBER_ID] = TBL_TRX.[DTP_MEMBER_ID]しか有効にならないと思います。TBL_MEMBER が全件選択されるはず。GROUP BY なしの外部結合だけ実行してその他の条件が適用されるか確認してください。

INNER JOIN

INNER JOIN の場合はすべての条件が適用されるはず。これも確認してください。

解決策:選択したものを結合

やりかたは二通りですが、選択を先にする方が結合のコストが減ります。

  • 結合する前にレコードを選択する(副問い合わせか、WITHを使う)
  • 結合後にレコードを選択する

投稿2019/04/30 23:00

xebme

総合スコア1081

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Ry22434

2019/05/03 05:05

ありがとうございます。INNER JOINとOUTER JOINの違いやWHERE句・ON句の使い方を理解できていませんでした。OUTER JOINを使い、WHERE句・ON句の指定条件を正しくしたうえで、抽出ができました。ありがとうございました。
guest

0

conversion failed when converting date and/or time from character string

こちらについては、メッセージの通りだと思います。
「文字列型」から「日付型 もしくは 時刻型」への変換に失敗しましたとのことですので

SQL

1TBL_MEMBER.[PMP_CARD_ISSUE_DATE] <> '0000-00-00'

で「'0000-00-00'」を利用しているのが問題なのではないでしょうか?
※当方MSSQLが無いので、確実かどうかわかりませんが。

投稿2019/04/30 15:32

kitaji0306

総合スコア176

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問