Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Store procedure help

Posted on 2011-05-10
14
Medium Priority
?
200 Views
Last Modified: 2012-05-11
Hello experts:
I need help with a stored procedure (see attachment).

The procedure selects a bunch of elements including one called RiskRating which
has a value of High, Medium  or Low.

The report shows all of the values fine except when there's no matching account number
in the Customer Profile table. It shows blanks but the client would like to see
a value of Low when this happens.

Any help would be greatly appreciated.

Thanks
Lance sp-AMLertsCustomerSummary-Report.txt
0
Comment
Question by:lancerxe
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35730568
Change this line:

, b.riskrating

To

, isnull(b.riskrating, 'Low') as riskrating

This assumes that it is a null value being shown.

Lee
0
 
LVL 4

Expert Comment

by:jimtpowers
ID: 35731314
Interesting that you are seeing anything for customers without an account number in the Customer Profile table since you have an INNER join. The INNER join should prevent customers without an account from even showing on the report. If the join were a LEFT OUTER, @Isavidge's solution would be correct.

You could also take the ISNULL and use in a CASE statement to catch where the field is blank instead of NULL:

CASE ISNULL(b.riskrating, 'Low')
     WHEN '' THEN 'Low'
     WHEN 'Low' THEN 'Low'
     ELSE b.riskrating
END

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35731423
If it is NULL or blank, you can get "Low" with this code.

isnull(nullif(b.riskrating,''), 'Low') as riskrating
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:lancerxe
ID: 35731825
I've tried all 3 versions and I still get some rows on the report that have a blank riskrating.

thanks
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35732053
Do you have any carriage returns or new line characters in the data?
0
 

Author Comment

by:lancerxe
ID: 35732172
I'm looking at doing something like this:
What do you think?

         , (CASE
           when (select distinct accountnumber  
                             from   CustomerProfile b
                             where  a.accountnumber = b.accountnumber) = 0
                             then 'Low'
                             else b.RiskRating
                                                                                                   
           END) as riskrating
0
 
LVL 4

Expert Comment

by:jimtpowers
ID: 35732191
What about:

CASE ISNULL(b.riskrating, 'Low')  
     WHEN '' THEN 'Low'  
     WHEN 0 THEN 'Low'
     WHEN 'Low' THEN 'Low'  
     ELSE b.riskrating  
END

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35732203
To comment further, I need to see the result of your query. Post some sample result of your original SP and the expected result.
0
 
LVL 4

Expert Comment

by:jimtpowers
ID: 35732240
Scratch that (not thinking clearly there). How about this:

CASE b.accountnumber
     WHEN 0 THEN 'Low'
     ELSE CASE ISNULL(b.riskrating, 'Low')    
               WHEN '' THEN 'Low'    
               WHEN 'Low' THEN 'Low'    
               ELSE b.riskrating
          END
END

Open in new window

0
 

Author Comment

by:lancerxe
ID: 35732287
Tried the code above. Still does not work
Tried this also . still no luck  
  , (CASE
           when (select count(*)
                             from   CustomerProfile b
                             where  a.accountnumber = b.accountnumber) = 0
                             then 'Low'
                             else b.RiskRating
                                                                                                   
           END) as riskrating
0
 

Author Comment

by:lancerxe
ID: 35732408
Do you think replace might work like:

 and ( replace(b.riskrating, '', 'Low'))  

thanks
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35732455
I think you need LEFT JOIN. check this.
select a.branchid
         , a.accountnumber 
         , a.taxidorssn 
         , a.shortname 
         , a.taxid as accounttype 
         , a.dateaccountopened 
         , a.transactioncode 
         , a.exposureamount 
         , a.currentledgerbalance 
         , a.averagebalance 
         , a.transactionamount 
         , a.bsahighriskaccountindicator 
         , a.hittype 
         , a.status 
         , isnull(nullif(b.riskrating,''), 'Low') as riskrating
  from   history a
         left join CustomerProfile b
         on (a.accountnumber = b.accountnumber)   
         where  convert(datetime, a.postingdate) between convert(datetime, @startDate) and convert (datetime, @endDate) 
         and a.transactiontype <> 'LOAN' 
         and ( a.highaggregatecashinindicator = '1' 
                or a.highaggregatecashoutindicator = '1' 
                or a.highaggregateincwiresindicator = '1' 
                or a.highaggregateoutwiresindicator = '1' 
                or a.highaggregateloanpaymentsindicator = '1' 
                or a.highaggregatemonetaryinstrumentindicator = '1' ) 
      and ( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(a.hittype, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '') in (select replace(hittype, '%', '') 
                                                                                                                                                                                                                                   from   hittypes 
                                                                                                                                                                                                                                   where  hittype_section in ( 'A', 'B' ) 
                                                                                                                                                                                                                                          and active = 1) )  
         and ( isnull(region, '') = ( case 
                                        when ( @region = 'No Region' ) then ( '' ) 
                                        when ( @region = 'All Regions' ) then ( isnull(region, '') ) 
                                        else ( @region ) 
                                      end ) ) 
         and ( a.branchid = ( case 
                              when ( @branch <> '0' ) then ( @branch ) 
                              else ( a.branchid ) 
                            end ) ) 
         and len(rtrim(ltrim(a.taxidorssn))) <> 0 

Open in new window

0
 

Accepted Solution

by:
lancerxe earned 0 total points
ID: 35756696
Ended up removing the stored procedure from the report and using
Record selection criteria instead
0
 

Author Closing Comment

by:lancerxe
ID: 35783787
This is the solution that worked
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Screencast - Getting to Know the Pipeline
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question