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

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

ただいまの
回答率

90.03%

OUTER JOINの際のエラー

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 408

Ry22434

score 5

前提・実現したいこと

会員マスタテーブルとトランザクションテーブルを結合し、年月別・会員のうち、どのくらいの数の方が購買を行っているか知りたいです。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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

+1

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

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

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

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


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

追記

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

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

select
    substring(CARD_ISSUE_MONTH, 1, 4) As YEAR, substring(CARD_ISSUE_MONTH, 6, 2) AS MMONTH
  , CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
  , COUNT(PMP_MEMBER_ID) AS MEMBER_COUNT
  , SUM(TRANSACT) AS TRANSACT_COUNT
  , SUM(TRANSACT_003) AS TRANSACT_003_COUNT
  , SUM(ACTIVATED) AS ACTIVATED_COUNT
from (
    SELECT
        TBL_MEMBER.*
      , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 7) AS CARD_ISSUE_MONTH
      , 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
      , CASE WHEN TBL_MEMBER.[PMP_LAST_TRX_DATE] IS NOT NULL THEN 1 ELSE 0 END AS TRANSACT
      , CASE WHEN TBL_TRX.[DTP_MEMBER_ID] IS NOT NULL THEN 1 ELSE 0 END AS TRANSACT_003
      , CASE WHEN TBL_MEMBER.[PMP_MOBILE_PHONE] IS NOT NULL THEN 1 ELSE 0 END AS ACTIVATED
    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_MEMBER_ID] = TBL_TRX.[DTP_MEMBER_ID] 
            AND TBL_MEMBER.[PMP_ISSUER_CODE]=TBL_TRX.[DTP_COMPANY]
    WHERE
          TBL_MEMBER.[PMP_ISSUER_CODE] = '003' 
      AND TBL_MEMBER.[PMP_CARD_ISSUE_DATE] <> '0000-00-00' 
  ) as base
GROUP BY
    CARD_ISSUE_MONTH, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
ORDER BY
    CARD_ISSUE_MONTH

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/03 14:05

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

    キャンセル

0

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

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

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


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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

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'

OUTER JOIN  

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

INNER JOIN  

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

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/03 14:05

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

    キャンセル

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

  • ただいまの回答率 90.03%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる