sql - Count results generated by case statement -


i'm working on query analyzes 2 records , checks whether 1 record matches second record. if record 1 column matches record 2 column there's no error. if record 1 column not match record 2 column there's error. want able count errors per ypid. life of me... can't figure out. help!

here's query:

select r1.ypid, r1.business_name base_listed_name, r1.street_address base_listed_address, r1.city base_listed_city, r1.state base_listed_state, r1.zip5 base_listed_zip, rmve1.value base_url, r2.business_name google_scanned_listed_name, r2.street_address google_scanned_listed_address, r2.city google_scanned_listed_city, r2.state google_scanned_listed_city, r2.zip5 google_scanned_listed_zip, rmve2.value google_scanned_url, ls.presence_score, ls.listing_score, case when r1.business_name = r2.business_name 'no_error' else 'error' end business_name_status, case when r1.latitude = r2.latitude  'no_error' when r1.longitude = r2.longitude  'no_error' when r1.latitude null or r1.latitude in ('0') (case when r1.street_address = r2.street_address 'no_error' else 'error' end) else 'error' end street_address_status, case when r1.city = r2.city 'no_error' else 'error' end city_status, case when r1.state = r2.state 'no_error' else 'error' end state_status, case when r1.zip5 = r2.zip5 'no_error' else 'error' end zip_status, case when lower(replace(replace(replace(replace(replace(replace(rmve1.value, 'http://www.', null), 'www.', null), 'https://www.', null), 'http://', null), 'https://', null), 'wwww.', null))  = lower(replace(replace(replace(replace(replace(replace(rmve2.value, 'http://www.', null), 'www.', null), 'https://www.', null), 'http://', null), 'https://', null), 'wwww.', null)) 'no_error' else 'error' end url_status mdm2.records r1 join mdm2.records r2 on r1.ypid = r2.ypid join mdm2.presence_listing_statuses ls on r1.ypid = ls.ypid  left outer join mdm2.record_mult_val_exts rmve1 on r1.id = rmve1.record_id , rmve1.extension_type = 'urls' , rmve1.value_type = 'primary' left outer join mdm2.record_mult_val_exts rmve2 on r2.id = rmve2.record_id , rmve2.extension_type = 'urls' , rmve2.value_type = 'primary' r1.ypid in ('5625222','13846403','21974776','22806234','30303664','453728041') , r1.source_code = 'ppa' , r2.source_code = 'goog';    results |ypid|base_listed_name|base_listed_address|base_listed_city|base_listed_state|base_listed_zip|base_url|google_scanned_listed_name|google_scanned_listed_address|google_scanned_listed_city|google_scanned_listed_city_1|google_scanned_listed_zip|google_scanned_url|presence_score|listing_score|business_name_status|street_address_status|city_status|state_status|zip_status|url_status| |5625222|affinity insurance agency|5702 s staples st suite g|corpus christi|tx|78413|http://affinityia.com|affinity insurance agency|5702 s staples st suite g|corpus christi|tx|78413|http://corpuschristiinsuranceprovider.com|90|97|no_error|no_error|no_error|no_error|no_error|error| |13846403|party bazaar|4435 lovers ln|dallas|tx|75225|www.partybazaardallas.com|party bazaar|4435 lovers ln|dallas|tx|75225|http://partybazaardallas.com|93|100|no_error|no_error|no_error|no_error|no_error|no_error| |21974776|alterations go|2100 arden way ste 150|sacramento|ca|95825|http://www.alterationstogosacramento.com|alterations go|2100 arden way # 150|sacramento|ca|95825|http://alterations-togo.com|91|99|no_error|no_error|no_error|no_error|no_error|error| |22806234|rv america|3640 chambers rd|aurora|co|80011|http://www.rvamericainc.com/pages/rv%20financing|rv america|3640 chambers rd|aurora|co|80011|http://rvamericainc.com|83|91|no_error|no_error|no_error|no_error|no_error|error| |30303664|sorelli hair studio & spa|400 saint andrews blvd|melbourne|fl|32940|http://sorellihairstudio.com|sorelli hair studio & spa|400 saint andrews blvd|melbourne|fl|32940|http://sorellihairstudio.com|91|99|no_error|no_error|no_error|no_error|no_error|no_error| |453728041|carolina driving school|534 walkertown guthrie rd|winston salem|nc|27101|http://www.carolinadriving.com|carolina driving school|534 walkertown guthrie rd|winston salem|nc|27101|http://carolinadrivingschool.com|87|97|no_error|no_error|no_error|no_error|no_error|error|

i guess need use lead , lag function .

check link http://oracle-base.com/articles/misc/lag-lead-analytic-functions.php

lag function used access data previous row lead function used return data next row.

by can compare 1st row 2nd row


Comments

Post a Comment

Popular posts from this blog

jquery - How do you format the date used in the popover widget title of FullCalendar? -

Image
in fullcalendar, how format date used in popover widget title when clicking '+n more'? code: <div class="fc-popover fc-more-popover"> <div class="fc-header fc-widget-header"> <span class="fc-close fc-icon fc-icon-x"></span> <span class="fc-title">tuesday, april 7</span> ... </div> ... </div> you use daypopoverformat that. determines date format of title of popover created eventlimitclick option. must date formatting string. default value "dddd, mmmm d" english , "ll" other languages.
Read more

asp.net mvc - SSO between MVCForum and Umbraco7 -

i need integrate sso between mvcforum (forum of company) , umbraco7 (manages cms company). however struggling shared authentication work. tried several ways suggested on other threads still not running expected. here did: 1. in mvcforum (forum.mywebsite.com) 1.1. machine key in web.xml: <machinekey validationkey="the same key umbraco7" decryptionkey="the same key umbraco7" validation="sha1" decryption="aes" /> 1.2. authenticate forms in web.xml <forms name="my-sso-auth" protection="all" path="/" timeout="43200" domain="forum.mywebsite.com" loginurl="/members/logon" enablecrossappredirects="true" /> 2. in umbraco7 (news.mywebsite.com) 2.1. machine key in web.xml: <machinekey validationkey="the same key mvcforum" decryptionkey="the same key mvcforum" validation="sha1" decryption="aes" /> 2.2. a...
Read more

Python Tkinter keyboard using bind -

i have problem using bind option can click button in keyboard, , call function once key pressed. tried taking other codes have similar purpose , saw i'm doing pretty same, though still have problem must've missed. thankful if me this. here's code: # -*- coding: utf-8 -*- #imports tkinter import * pil import imagetk, image import time import tkmessagebox #===========================================================================================================================================# #tkinter class class app(frame): def __init__(self, master=none): frame.__init__(self, master) self.pack() #===========================================================================================================================================# #game pieces classes class board: def __init__(self, rows = 7, columns = 6, picture_height = none, picture_width = none): self.rows = rows self.columns = columns self.picture ...
Read more