Link to home
Start Free TrialLog in
Avatar of snyperj
snyperjFlag for United States of America

asked on

SQL Server code help needed

I am a novice at SQL server but have a need to pull some data for a report.  I need some help creating some coding in a view to pull a field of data (service_code) from some other views.

For simplicity, lets say I have 3 views (view1, view2, view3) that I need to look at to pull the data field from,  and then one other view, mview where I need to use that data field.

All three views have three similar fields.
order_nbr
line_nbr
service_code

I need some code that will look at all three views and provide the following output:
if view1 has a record for order_nbr, line_nbr and a value for service_code,  use that as the output and dont look anymore.

If view 1 does not have a record for order_nbr, Line_nbr,  or the serivce_code field is empty,  look for the value in view2.
if view 2 has a record for order_nbr, line_nbr and a value for service_code,  use that as the output and dont look anymore.

If view 2 does not have a record for order_nbr, Line_nbr,  or the serivce_code field is empty, then look for the value in view3.
if view 3 has a record for order_nbr, line_nbr and a value for service_code,  use that as the output and dont look anymore.

The key for me here is to look in this specified order (view1, view2, view3) and then stop looking as soon as a value for service_code is found.  One of the views will alwasys have a value and sometimes all three could have a value for service_code... but the value for service_code  could be different in the views...that is why the order is crucial.

Then I need to understand how to add this value to mview  (in Management Studio)  which also has the order_nbr and line_nbr fields in it.

Hoping someone can help with this.  I was trying to do it with a UNION of the three views, but that did not work .

Thank you!
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try to visualise ur issue and I think it helps us to provide a solution for you.

a stored procedure could be a solution for you if the logic is too complex to handle by normal views.
Avatar of snyperj

ASKER

Yes.  That is what I need help with please, a stored procedure.
here it is...

with t as (
select 1 SourceType, v1.* from view1 v1
union
select 2 SourceType, v2.* from view2 v2
union
select 3 SourceType, v3.* from view3 v3
)
select * from (
select t.*, 
       row_number() over (partition by order_nbr, line_nbr order by SourceType) rn
  from t
) x where rn=1

Open in new window


I am assuming key is order_nbr, line_nbr

if it is only order_nbr, then remove line_nbr from row_number() section, Line 10
Why can't you just UNION all three views. You get one record for the combination. How does it matter from which view it is picked? Do you want to retain that information (say view name) also?

select order_nbr, line_nbr, service_code from view1 union
select order_nbr, line_nbr, service_code from view2 union
select order_nbr, line_nbr, service_code from view3

Open in new window

Sharath, op said, service_code may be different in all views...
so, union does not help here...
Avatar of snyperj

ASKER

Yes.... good question...the service_code value may or may not be coded in during order entry.  If not, view3 would provide the default service_code for the type of order it is.  So when I tried to use a union, i would get two records for the line number (if the default was not being used/entered.)

Yes, order_nbr, line_nbr would be the unique key.   I am just not sure how/where I enter the code you provide.  I know I have to change the values to the actual field and view names... but I dont know where to enter the code itself in Management Studio?  Sorry, I am really a novice here.
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of snyperj

ASKER

No I saw the code...thank you... but do I just create a new stored procedure in Management Studio and paste it in?  That is the part I also am not familiar with....   Then, how do I pull the correct order_nbr, line_nbr and service_code value into my mview?
that is the query that gives you the result...

it will have

order_nbr, line_nbr, service_code

columns... + SourceType (with values 1,2,3)
Avatar of snyperj

ASKER

ahh...ok I will try it ...
Avatar of snyperj

ASKER

I entered as below, with the actual names.  When I saved it and clicked 'Verify SQL Syntax'... it says:  Incorrect syntax near ')'

SELECT     1 SourceType, v1.*
FROM         dmine_OP_PRICING_RESPONSE_service_level v1
UNION
SELECT     2 SourceType, v2.*
FROM         dmine_OP_SPECLQTE_service_level v2
UNION
SELECT     3 SourceType, v3.*
FROM         dmine_OP_SPECQTDT_service_level v3)
    SELECT     *
     FROM         (SELECT     T .*, row_number() OVER (partition BY Order_No, Line_No
                            ORDER BY SourceType) rn 
     FROM         t) x
WHERE     rn = 1

Open in new window

why you removed Line 1?
Avatar of snyperj

ASKER

my bad... I dont know how I missed that.   Ok so the query runs... it adds two columns SourceTye as you  mentioned and also a column called rn that has a value of 1.   I am testing it now in my other view.... I am hopeful!
Avatar of snyperj

ASKER

This seems to be working great.  THANK YOU!