Solved

SQL Query - Get additional info into query..

Posted on 2010-08-16
7
216 Views
Last Modified: 2012-05-10
Hello,
I am having difficulty understanding how I can get some data to show in my query - This query sits in a stored procedure and called from report services report.  Below is my query, the field I would like to have remain constant (in other words have all five records returned even if there is no data attached to it) is the:  id.inventory_state_name_EN

Right now the data comes back by province and by state - So, I could see five records for
Alberta with two different inventory states because tghere is related data for all the other fields.  I would like to see all these records and then for each provinces any of the other inventory states that do not having any related records.  Have I just made this clear as mud..the field that is referenced comes from the view vwInventory_Denormalized.  If anybody can point out to me how this can be done - much, much appreciated!

Select x.*
,CASE concentration_range WHEN 'QTY(L)' THEN
CASE when Concentration < 10000 AND Concentration <> 0 AND Concentration <> -1 Then
CASE WHEN phase_type_name = 'Solid' Then
      (concentration * quantity * 1/1000000)
ELSE
      (Concentration * quantity * 0.9 * 1/ 1000000)
END
when Concentration >= 10000 Then
CASE WHEN phase_type_name = 'Solid' Then
      (Concentration * quantity * 0.9 * 1/ 1000000)
ELSE
      (Concentration * quantity * 1.5 * 1/ 1000000)
END
ELSE                                               CASE WHEN phase_type_name = 'Solid' Then
      (700000 * quantity * 1/ 1000000)
ELSE
      (700000 * quantity * 1.5 * 1/ 1000000)
END
END
WHEN 'QTY(L) 50/500' THEN
CASE WHEN phase_type_name = 'Solid' Then
Case WHEN Concentration IS NOT NULL AND Concentration <> 0 AND Concentration <> -1 Then
      (275 * quantity * 1 / 1000000)
Else
      (700000 * quantity * 1 / 1000000)
END
ELSE
Case WHEN Concentration IS NOT NULL AND Concentration <> 0 AND Concentration <> -1 Then
      (275 * quantity * 0.9 * 1/ 1000000)
Else
      (700000 * quantity * 1.5 * 1/ 1000000)
END
END
 WHEN 'QTY(L) >500' THEN                               
CASE WHEN phase_type_name = 'Solid' Then
      (700000 * quantity * 1 / 1000000)
ELSE
      (700000 * quantity * 1.5 * 1/ 1000000)
END
END AS PCBInLiquid
FROM (Select id.report_id,rd.title, pc.PROV_STATE_NAME_EN as prov_state_name,rd.Operating_entity_name,rd.YEAR,
rd.report_type_name_en as report_type_name,
CASE id.inventory_state_name_EN
WHEN 'IN USE' Then
      '1  In Use on Dec 31st'
WHEN 'STORED' Then
      '3  Stored on Dec 1st'
WHEN 'TRANSFERRED' Then
      '2  Sent to Transfer Site'
WHEN 'SENT FOR DESTRUCTION' Then
      '4  Sent to Destruction'
WHEN 'DESTROYED' Then
      '5  Destroyed'
END as inventory_state_name,id.phase_type_name_en as phase_type_name,
CASE id.in_equipment
      WHEN 'false' THEN 'Liquid In Equipment'
       WHEN 'true' THEN  'Liquid Not In Equipment'
 END AS InEquipment , Round(id.quantity, 2, 0) As quantity,id.unit_en as unit,
id.Concentration_value AS Concentration,
CASE CAST(id.concentration_range as varchar(30))
      WHEN '0' THEN 'QTY(L)'
      WHEN '50' THEN 'QTY(L) 50/500'
      WHEN '500' THEN 'QTY(L) >500'
END as concentration_range
 FROM vwInventory_Denormalized AS id Inner JOIN vwReportLegalEntity_Denormalized AS rd
ON id.report_id = rd.report_id Inner JOIN Company_location cl
ON cl.Legal_entity_id = rd.legal_entity_id Inner JOIN Location_address la
ON la.location_id = cl.location_id Inner JOIN ProvState_code pc
ON pc.Prov_state_code = la.Prov_state_code Inner JOIN EC_Region r
ON r.EC_Region_id = pc.Region_id Left outer join report_amendment AS a1
ON a1.report_old_id=id.report_id
WHERE      id.quantity > 0
      AND rd.REPORT_TYPE_ID = 2
      AND rd.REPORT_STATE_NAME_EN = 'SUBMITTED'
      AND rd.Year = @YEAR
      AND a1.report_old_id is null
GROUP BY      pc.PROV_STATE_NAME_EN,
      rd.Operating_entity_name,
      rd.YEAR,
      rd.report_type_name_EN,
      rd.title,
      id.inventory_state_name_EN,
      id.phase_type_name_EN,
      id.report_id,
      id.unit_EN,
      id.concentration_range,
      id.in_equipment,
      rd.report_type_name_EN,
      id.quantity,
      id.Concentration_value)                              
as x
ORDER BY    PROV_STATE_NAME asc,
      inventory_state_name asc,
      Concentration desc
0
Comment
Question by:Mosquitoe
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:Larissa T
ID: 33445641
Try  outer join
left outer JOIN ProvState_code pc
 
Move this join to be the last one in you joins
0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33445692
Sorry - didn't notice your other left join

try this
 FROM ProvState_code pc left outer join
(vwInventory_Denormalized AS id
Inner JOIN vwReportLegalEntity_Denormalized AS rd ON id.report_id = rd.report_id
Inner JOIN Company_location cl ON cl.Legal_entity_id = rd.legal_entity_id
Inner JOIN Location_address la ON la.location_id = cl.location_id  
Inner JOIN EC_Region r ON r.EC_Region_id = pc.Region_id
Left outer join report_amendment AS a1 ON a1.report_old_id=id.report_id
) ON pc.Prov_state_code = la.Prov_state_code
0
 

Author Comment

by:Mosquitoe
ID: 33447364
No this does not work - it gives an error message...I was thinking, that this field, the inventory_state_name_en - it is within the vwInventory_denomalized view.  But those values are from a look-up table called inventory_state.  I have been trying to join that table to this query, and using the WHERE clause, specify only the five states that are relevant to this...What I tried is below.  I would like the return data to have everything it has now, but if the province of Alberta (say), has five records, two are 'Destroyed', one is 'In Use' (these values are the inventory_state_name_en field) then the remaining three states ie/ transferred, sent to destruction, and stored still show up in the query results, just with NULLS everywhere..For the life of me, I cannot get that to happen - going in circles here.  


FROM         dbo.vwInventory_Denormalized AS id INNER JOIN
                      dbo.vwReportLegalEntity_Denormalized AS rd ON id.REPORT_ID = rd.REPORT_ID INNER JOIN dbo.Company_Location AS cl ON cl.LEGAL_ENTITY_ID = rd.LEGAL_ENTITY_ID INNER JOIN
dbo.Location_Address AS la ON la.LOCATION_ID = cl.LOCATION_ID INNER JOIN
dbo.ProvState_Code AS pc ON pc.PROV_STATE_CODE = la.PROV_STATE_CODE INNER JOIN
dbo.EC_Region AS r ON r.EC_Region_ID = pc.REGION_ID LEFT OUTER JOIN
dbo.Inventory_State AS t ON id.INVENTORY_STATE_ID = t.INVENTORY_STATE_ID RIGHT OUTER JOIN
dbo.Report_Amendment AS a1 ON a1.REPORT_OLD_ID = id.REPORT_ID
WHERE     (id.QUANTITY > 0) AND (rd.REPORT_TYPE_ID = 2) AND (rd.REPORT_STATE_NAME_EN = 'SUBMITTED') AND (rd.YEAR = 2009) AND
                      (a1.REPORT_OLD_ID IS NULL) OR
                      (t.INVENTORY_STATE_ID = 1) OR
                      (t.INVENTORY_STATE_ID = 5) OR
                      (t.INVENTORY_STATE_ID = 6) OR
                      (t.INVENTORY_STATE_ID = 10) OR
                      (t.INVENTORY_STATE_ID = 11)
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 11

Accepted Solution

by:
Larissa T earned 125 total points
ID: 33450462
It is difficult to debug it without data.
here are things to look:
1) You do "left join as
LEFT OUTER JOIN
dbo.Inventory_State AS t ON id.INVENTORY_STATE_ID = t.INVENTORY_STATE_ID

and at the same time  in your "where" you have
((t.INVENTORY_STATE_ID = 1) OR
                      (t.INVENTORY_STATE_ID = 5) OR
                      (t.INVENTORY_STATE_ID = 6) OR
                      (t.INVENTORY_STATE_ID = 10) OR
                      (t.INVENTORY_STATE_ID = 11))

Your where eliminates your left join completely since in result of left join if there are any t.INVENTORY_STATE_ID will be null
move this where to join
as
LEFT OUTER JOIN
dbo.Inventory_State AS t ON id.INVENTORY_STATE_ID = t.INVENTORY_STATE_ID
   and t.INVENTORY_STATE_ID in (1,5,6,10,11)

or if state ID is always populated change "where"
(t.INVENTORY_STATE_ID in (1,5,6,10,11) or t.INVENTORY_STATE_ID is null)

2) the same problem with right join. You can not put anything into outer joined table into where without explicitly checking for nulls.

3)
also check parentheses in you "where". I would say you enclose all "or" conditions as

(rd.YEAR = 2009) AND
         (            (a1.REPORT_OLD_ID IS NULL) OR
                      (t.INVENTORY_STATE_ID in (1,5,6,10,11) or t.INVENTORY_STATE_ID is null)
        )
 


dbo.Inventory_State AS t ON id.INVENTORY_STATE_ID = t.INVENTORY_STATE_ID
0
 

Author Comment

by:Mosquitoe
ID: 33453442
No, I realise it's hard ...  I'm going to include a piece of the data output below - these are the results from the query without any additions for the inventory_state_name field.  I tried the options above and none of them give the desired results...If I can offer any further explanation so as to help you ,help me :) please let me know!
example.xls
0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33454869
Try this. I created example table from you data.

drop table  #province_names
go
drop table  #inventory_states
go
-- this is list of all provinces you have
select distinct province into #province_names  from [example] order by province
go
-- this is list of all inventory_state_names you have
select distinct inventory_state_name into #inventory_states  from [example] order by inventory_state_name


select  p.province, s.inventory_state_name, sum(quantity)
 from (#province_names  p  full outer join #inventory_states s on 1=1)
	left join   [example]  data on p.province=data.province and s.inventory_state_name=data.inventory_state_name
group by p.province, s.inventory_state_name 
order by p.province, s.inventory_state_name

Open in new window

0
 

Author Closing Comment

by:Mosquitoe
ID: 33457844
The WHERE clause had a condition that I continued to overlooked - this response held the key to correcting the query - Thanks SO much!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now