Showing Null Values From ON Inner Join Sproc CLAUSE
Jul 19, 2006
Hi all
My query has some inner joins to some tables. And problem is when any ON clause get null as value, the correspondent record is not displayed.
SELECT
TableA.A, TableB.A
FROM TableA
INNER JOIN TableB ON TableA.A = TableB.A
What I did try:
SELECT
TableA.A, TableB.A
FROM TableA
INNER JOIN TableB ON TableA.A = TableB.A OR TableA.A IS NULL
(but It generates redundant values from TableB)
I need to show all values even that value from Tablea is null
Thank a lot for any help
View 1 Replies
ADVERTISEMENT
Apr 7, 2008
I have an optional param passed to my sproc @Pid if that is populated i want to have it part of where clause, if it is null then not in where
I know this is wrong, and very new to sqlsrver:
WHERE (Pos = 'T') AND (ofDate= @OfDate)
IF not @PID is null
AND (PID = @PID )
GROUP BY
bla bla bla
Thanks
View 12 Replies
View Related
Apr 13, 2007
I have designed a for to submit some info to a sql database, but when i enter the information to the form fields and click submit, I get an error saying that a null value cannot be submitted, when it should not be null.
Not to sound whiney, but i really need a code fix, not links to msdn pages that define properties (usually dont do this, but i have a deadline to submit this to my tech admin of my company's site)
Here is the code, and THANK YOU to anyone who helps me with this!!
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" %>
<script runat="server">
Private Sub Submitdata(ByVal Source As Object, ByVal e As EventArgs)
SqlDataSource1.Insert()
End Sub ' Submitdata
</script>
<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
<asp:sqldatasource
id="SqlDataSource1"
runat="server"
connectionstring="<%$ ConnectionStrings:clientInfoConnectionString %>"
selectcommand="SELECT Client Name,Client Address FROM Clients"
insertcommand="INSERT INTO Clients (ClientName,ClientAddress) VALUES (@CName,@CAddress)">
<insertparameters>
<asp:formparameter name="CName" formfield="Namebox" />
<asp:formparameter name="CAddress" formfield="Addressbox" />
</insertparameters>
</asp:sqldatasource>
Name:<br />
<asp:TextBox ID="Namebox" runat="server" /><br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Namebox"
ErrorMessage="Please Enter A Name"></asp:RequiredFieldValidator>
<br />Address:<br />
<asp:TextBox ID="Addressbox" runat="server" /><br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Addressbox"
ErrorMessage="Please Enter An Address"></asp:RequiredFieldValidator>
<br /><asp:Button ID="Submitbtn" runat="server" Text="Submit" OnClick="Submitdata" />
<br />
</asp:Content>
Thanks,
The King
View 2 Replies
View Related
Dec 28, 2006
Hi,
I am using a SQL back end to dynamically populate an asp.net report/page.
As the data I'm interrogating is created from a tree control, I'm having to use a recursive function to retrieve the data into a series of ID values. This all happens at the moment in a DataTable manipulated with c# code. So my ID values end up in this datatable.
My problem is that I am then performing a crosstab query in SQL Server 2000 and these ID are required as part of that query.
Should I create a temp table and join this into the query or should i feed in a series of ID values into a where clause?
Any help gratefully appreciated.
Thanks.
John
View 2 Replies
View Related
May 16, 2005
If I try to run the code below, and even one of the values in the INNER
JOIN statements is NULL, the DataReader ends up with zero rows.
What I need is to see the results even if one or more of INNER JOIN
statements has a NULL value. For example, if I want info on
asset# 2104, and there's no value in the DriverID field, I need the
rest of the data to display and just have the lblDriverName by
blank. Is that possible?
<code>
Sub BindSearchGrid()
Dim searchUnitID As String
Dim searchQuery As String
searchUnitID = tbSearchUnitID.Text
lblIDNum.Text = searchUnitID
searchQuery = "SELECT * FROM Assets " & _
"INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _
"INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _
"INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _
"INNER JOIN AssetCategories
ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _
"INNER JOIN Store ON
Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '"
& searchUnitID & "'"
Dim myReader As SqlDataReader
myReader = Data.queryDB(searchQuery)
While myReader.Read
If
Not IsDBNull(myReader("Store Name")) Then lblStrID.Text =
myReader("Store Name")
If
Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text =
myReader("AssetCategory")
If
Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text =
myReader("Condition Description")
If
Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text =
myReader("DepartmentName")
If
Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit
ID")
If
Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year")
If
Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make")
If
Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model")
If
Not IsDBNull(myReader("Mileage")) Then lblMile.Text =
myReader("Mileage")
If
Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text =
myReader("Vin Number")
If
Not IsDBNull(myReader("License Number")) Then lblLicNum.Text =
myReader("License Number")
If
Not IsDBNull(myReader("Name")) Then lblDriverName.Text =
myReader("Name")
If
Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text =
myReader("DateAcquired")
If
Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text =
myReader("DateSold")
If
Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text =
myReader("PurchasePrice")
If
Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text =
myReader("NextSchedMaint")
If
Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR")
If
Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW")
If
Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text =
myReader("Crane Capacity")
If
Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text =
myReader("Crane Certification")
If
Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text =
myReader("Repair Cost")
If
Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text =
myReader("Estimate Replacement")
If
Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text =
myReader("SalvageValue")
If
Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text =
myReader("CurrentValue")
If
Not IsDBNull(myReader("Comments")) Then lblCom.Text =
myReader("Comments")
If
Not IsDBNull(myReader("Description")) Then lblDesc.Text =
myReader("Description")
End While
End Sub</code>
View 1 Replies
View Related
Oct 18, 2013
I've got 2 tables of towns. I'm using outer join because i need all the town from both tables. However I'm sometimes getting duplicates.
My query
select a.town, b.town
from a
outer join b on a.town = b.town
group by a.town, b.town
How to stop getting null values?
portsmouth null
portsmouth portsmouth
southampton southampton
null southampton
TownA null
null TownB
I'm looking for distinct values like this:
portsmouth portsmouth
southampton southampton
TownA null
null TownB
etc...
View 2 Replies
View Related
Oct 17, 2007
Hello,
I have this INNER JOIN that is fine to show all possible combinations. But I need to show only rows that have one or more Null values in tbIntersect.
Should I use nested LEFT JOINT? How?
This is the SQL statement:
sSQL = "SELECT DISTINCT tbCar100.Car100_ID, tbCar100.Description100 AS [Caractéristique 100], " & _
"tbCar200.Car200_ID, tbCar200.Description200 AS [Caractéristique 200], " & _
"tbCar300.Car300_ID, tbCar300.Description300 AS [Caractéristique 300], " & _
"tbCar400.Car400_ID, tbCar400.Description400 AS [Caractéristique 400], " & _
"tbCar500.Car500_ID, tbCar500.Description500 AS [Caractéristique 500], " & _
"tbCar600.Car600_ID, tbCar600.Description600 AS [Caractéristique 600], " & _
"tbCar700.Car700_ID, tbCar700.Description700 AS [Caractéristique 700], " & _
"tbProducts.Prod_ID, tbProducts.PartNumber AS [Part Number] , tbProducts.Description AS [Description] , tbProducts.DateAdded AS [Date] " & _
"FROM tbProducts INNER JOIN (tbCar700 INNER JOIN (tbCar600 INNER JOIN (tbCar500 INNER JOIN (tbCar400 INNER JOIN (tbCar300 INNER JOIN (tbCar100 INNER JOIN " & _
"(tbCar200 INNER JOIN tbIntersect ON tbCar200.Car200_ID = tbIntersect.Car200_ID) " & _
"ON tbCar100.Car100_ID = tbIntersect.Car100_ID) ON tbCar300.Car300_ID = tbIntersect.Car300_ID) ON tbCar400.Car400_ID = tbIntersect.Car400_ID) ON tbCar500.Car500_ID = tbIntersect.Car500_ID) ON tbCar600.Car600_ID = tbIntersect.Car600_ID) ON tbCar700.Car700_ID = tbIntersect.Car700_ID) ON tbProducts.Prod_ID = tbIntersect.Prod_ID " & _
";"
Here is the content of the tbIntersect table:
Car100_ID Car200_ID Car300_ID Car400_ID Car500_ID Car600_ID Car700_ID Prod_ID ID
1 1 1 1 1 1 1 1 1
1 2 1 1 1 1 1 19
1 3 1 1 1 1 1 20
I need to return the rows that have null data, ex: second row because Prod_ID is NULL and third row because Car300_ID is NULL. In fact I need the data from the other joint tables that correspond to these ID fields.
Thanks
View 5 Replies
View Related
Oct 16, 2004
I have table Products and Orders that has the following columns:
table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null
Obviously, my sql statement won't work becaue the where clause will filter out Product-C.
Could anyone help me figure out how to modify my sql code to get the resultset I want?
Thanks in advance.
View 2 Replies
View Related
Jan 25, 2008
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
View 7 Replies
View Related
Sep 15, 2005
Hi,
I've tried that but it does not work:
SELECT Field FROM Table WHERE Field LIKE @parameter
thanks
View 4 Replies
View Related
Nov 28, 2006
I'm still having issues with this despite my attempts to resolve. I even
have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution,
but still the encrypted data returns nulls when I exec as a user other than
DBO. Below is precisely what I have done. All ideas are welcomed.
TIA, ChrisR
--If there is no master key, create one now
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
USE [AdventureWorks];
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
alter procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
View 1 Replies
View Related
Aug 28, 2006
Hi. I wrote a sproc to insert values from a tab-delimited text file into SQL 2005. There is a column in the text file that contains datetime values. Some of the values may be null. I have defined my sproc as:[CODE]createproc [dbo].[spInsertTransactionData]@LoadTransactionCode tinyint,@AccountNumber char(19),@PostingDate char(8) = '01011900'asinsert into table1( LoadTransactionCode, AccountNumber, PostingDate)values(@LoadTransactionCode,@AccountNumber,cast(stuff(stuff(@PostingDate, 3, 0, '/'), 6, 0, '/') as datetime)) [/CODE]I am getting the error:"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."When I step into the sproc, I see that the problem arises when the code encounters a "blank" value for PostingDate.As always, thanks for the help!
View 3 Replies
View Related
Oct 8, 2015
In my source table, I have columns FirstName and LastName, both of datatype nvarchar. In my dataflow, I created two new derived columns mapped to these two columns. When pushing data to the source, I noticed that the FirstName column had a value of NULL while LastName was just an empty string (for rows that did not have any value).
My question is, why my source table column FirstName is showing a NULL value when the derived column datatype is string and the source is string? It should just be showing an empty string right?
View 3 Replies
View Related
Oct 23, 2006
how can i show the values of my database in for example texbox.can u give me some simple code on how to connect to my SQldatabase server express? and how i can retrieve the file?thnx
View 2 Replies
View Related
Jul 20, 2005
I am new to SQL server and I am looking for the best practiceregarding the following:The GUI for the database I am creating has a number of questions like:A. Where are you currently living?1) In my own home2) In the home of a family member3) In a shelter4) On the streetHowever, for speed of data entry, the GUI (it's a web front-end,ASP/VBScript) accepts the number only. Futhermore the database onlystores the number entered.So for example for the above question, when the user is doing the dataentry, they would just enter a 1, 2, 3, or 4.However, when viewing the data, the end user wants to see the textthat corresponds to the number. So for example, if the user entered"3" for the above question, when they are viewing the data in the GUI(not to make edits, just viewing), they want to see "(3) In ashelter".My question is how best to display these friendly values?Should I do all the conversions in a stored procedure? If so, whatwould be the best way to do that? I tried something like this:SELECT Current_living =CASE Current_livingWHEN 1 THEN '(1) In my own home with my partner'WHEN 2 THEN '(2) In the home of a family member'ELSE NULLENDFROM tbl_DemographicInfo WHERE ClientID = @ClientIDbut I can't figure out how to do this for multiple fields.ALSO, I'm not sure if burying this stuff in a stored proc is the wayto go, because if the client wants a slight change to the friendlyname I have to get into the SP and change it. Seems like there mustbe a better way.I can also do this in the ASP code, but that also doesn't seem likethe best practice.I can have a lookup table, but again, where do I do the conversion?In an SP? With a view?Again, I'm looking for the best practice here. I know some ways Icould do it (like in the ASP), but I want to do it efficiently andcorrectly.Any help/comments are greatly appreciated.thanks in advance,Jon LaRosajlarosa at alumni dot brown dot edu
View 2 Replies
View Related
Aug 18, 2015
I am tying to join tables to get the result but it is not showing any data,i have shipping address column in both tables I want to show data in single column I don't know how to display.
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress ,rs.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
inner join
ReSaleorReStock1Table rs on r1.ProductID=rs.ProductID
where r1.HazMat='No' order by ProductID
If I join two tables it is showing data
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
where r1.HazMat='No' order by ProductID
View 9 Replies
View Related
Aug 9, 2006
Could someone please tell me why the following SELECT statement...
SELECT ID, SpecimenNr, ScientificName, Locality, TaxonFROM petrander.QueryViewWHERE (InstitutionCode = 1) AND (Collectioncode = 1) AND (ScientificName LIKE N'%le%') AND (Locality LIKE N'%Fakse%') AND (22 IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...gives me 9 rows back, but embedding the exact same statement in the following sproc...
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [petrander].[DynamicQuery] @taxparent int = NULL, @museum int = NULL, @collection int = NULL, @binomen Nvarchar(254) = NULL, @locality Nvarchar(254) = NULLAS SELECT ID, SpecimenNr, ScientificName, Locality, Taxon FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE 'N%' + @binomen + '%' AND Locality LIKE 'N%' + @locality + '%' AND (@taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...and passing the exact same parameter values to with the following execute statement...
USE [Geomusdb]
GO
DECLARE @return_value int
EXEC @return_value = [petrander].[DynamicQuery]
@museum = 1,
@collection = 1,
@binomen = N'le',
@locality = N'Fakse'
SELECT 'Return Value' = @return_value
GO
gives me 0 rows!? What is different!?
Any help is greatly appreciated...
View 4 Replies
View Related
Feb 23, 2007
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies
View Related
May 27, 2008
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
View 1 Replies
View Related
May 27, 2008
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
View 1 Replies
View Related
Dec 9, 2013
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
View 5 Replies
View Related
Mar 29, 2006
Hi
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
If Not Row.Datum1_IsNull Then
Row.OutputDatum1 = Row.Datum1
Else
Row.OutputDatum1 = CDate(System.Convert.DBNull)
End If
Any help welcome.
View 1 Replies
View Related
Jun 4, 2007
Hello all,
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
Any suggestions?
thanks for your time...
View 5 Replies
View Related
Jan 21, 2008
Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?
Thanks
View 6 Replies
View Related
Nov 9, 2000
Hi,
My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list
e.g.
Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -
At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?
Thanks
Dan
View 1 Replies
View Related
Nov 10, 2015
When building stored procs, I typically have several nullable parameters. Then in the where clause I will either do this
WHERE (@MyParam IS NULL OR MyColumn = @MyParam)
or this:
WHERE MyColumn = COALESCE(@MyParam, MyColumn)
Lately, I've noticed that either of these imposes a performance penalty. In other words, if I change the first one to
WHERE MyColumn = @MyParam
without the IS NULL, it is noticeably faster. If I only have one or two nullable parameters, I can separate the logic using IF/ELSE, but it becomes huge if I have a bunch of nullable params.I've looked at the execution plans.
View 8 Replies
View Related
Sep 21, 2007
I think I am getting 0 records returned... because....
I am joining the third value based on a zip code. The two tables if directly compared to each other would never have an = match.
SELECT t2.company_name, t2.firstname, t2.lastname, modelname, configname, format, version, username, t2.zip,
t2.country
FROM EtechModelRequests JOIN
CC_Host.dbo.USR_SC as t2 ON
Cast(t2.user_id As char) = username
--JOIN
--Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON PostalCode = zip
WHERE RequestDateTime > CONVERT(DATETIME, '2007-09-1 00:00:00', 102) AND interfacename LIKE '%download%' AND
result=0 AND country='CA'
--AND t3.PostalCode Like 'z1x%'
ORDER BY company_name
I was trying to do it by using a Where clause AND t3.PostalCode Like 'z1x%' that I will later turn into an Input Parameter after I get it working.
Is there anyway to trim the PostalCode to the first three characters during the join process?
Something like Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON LEFT(PostalCode, 3) = zip
Not sure I got the LEFT function syntax correct even. Help appreciated.
View 1 Replies
View Related
Apr 22, 2008
I'm joining one table on to another table using one of 2 possibile fields (so table 1 key one can either match table 2 key 1 or key 2)... When the first key is null for a record, the script is to attempt to join using the second key instead. It is possible to have both values present, in which case the first one should be used.
I've taken a few runs at this so far:
...
from table1 t1
left join table2 t2
on
(t1.key1 = t2.key1
or
t1.key1 = t2.key2)
If either t2.key1 or t2.key2 are populated, this works. Unfortunately, it's bringing back multiple records if both key1 and key2 are populated. Question # 1... Is there a different relational operator I can be using instead of OR that would logically look like 'if thie first key didn't find anything try the second instead'?
As an alternative, I've put the NVL to use...
NVL(t2.key1, t2.key2) = t1.key1
That seems to work, but it's pretty heavy on the server. Any suggestions on how else to handle this scenario would be greatly appreciated
(and ya, I didn't design this datamodel).
View 6 Replies
View Related
Aug 15, 2007
view 1
I have a view that is drawing from two tables. Each table contains fields representing cube coordinates. The view is filtering the results based on some simple logic (where the defference between two values in the tables are greater than x) this part works fine.
view 2
notes field
I want to include a note field in my view. This field will contain the contents of a note field from another view. This second view also contains coordinates that I can use to map the notes to the appropriate rows in view 1. However, if I join the views in my FROM clause, I will end up filtering my resultset down to rows that correspond to view 2's contents.
I want to have the full contents of view 1, displayed with a note field containing the note field content from view 2 only in the rows that have corresponding notes. (some rows will have notes, some will not)
eg.
VIEW 1
row1 row2 row3 note_row (from view 2)
fsdfs sdfsdf sdfsdf <no note>
sdfs sdfsd sdfsd "note"
sdfsdf sdfsdf ssdfsd <no note>
so... my question: is there any way that I can include this field without joining the views in my FROM clause (meking my resultset exclusive)..... possibly somehow in fields list of the select statement?
THANKS!
View 4 Replies
View Related
Feb 12, 2004
I have a table "Users" like this:
GroupId
CompanyId
UserId
I need to query the users getting the company's and group's names, but I only know how to join one table. Example:
Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name
From Users JOIN Groups ON Users.GroupId = Groups.Id
Hon can I add the companies table in the Join ?
Thanks,
Moshe
View 2 Replies
View Related
Dec 1, 2005
like so often my Forums database design (in its simplest form) is:Forums -ForumID -Title -CategoryForumsMsgs -fmID -DateIn -AuthorID -MessageI need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?
View 2 Replies
View Related
Feb 26, 2004
In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set.
In layman's terms, what is the difference? Any examples? Thanks in advance.
ddave
View 6 Replies
View Related
Mar 7, 2008
I am trying to get all of the Fund_cdes to show up even if there was no transaction on the brkg fact table. The problem I coming up with is I am also retricting what I show in the brkg fact table so I am not getting all of the row from the fund table. How do I write a left join that shows all of the fund cdes
SELECT
SEP_ACCOUNT.sep_acct_cde as Account,
FUND.fund_cde as FUND,
BRKG_FACT.accum_unit_cnt as Units_Purchased,
BRKG_FACT.transaction_amt as Amount_Purchased
FROM
BRKG_FACT
SEP_ACCOUNT
FUND
where
BRKG_FACT.sep_acct_id_num = SEP_ACCOUNT.sep_acct_id_num
brkg_fact.FUND_ID_NUM = FUND_DIM.FUND_ID_NUM
brkg_fact.SEP_ACCT_ID_NUM = 5 and
brkg_fact.product_cde <> 'MM' and
brkg_fact.transaction_amt <= 0 and
brkg_fact.source_sys_id_num <> 3 and
brkg_fact.source_sys_id_num <> 5 and
BRKG_FACT.trans_process_dte >= '1/1/2008' and
BRKG_FACT.trans_process_dte <= '1/2/2008'
order by fund_cde
current output
ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED
U BLCD -0.01137 -1.48000
U BOND -0.01283 -1.67000
U CGDE -0.06743 -0.95000
U EQIN -0.13277 -2.39000
U GRST -0.11799 -4.07000
U IX4S -0.53996 -12.55000
U LCCS -0.18216 -5.31000
wanted output
ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED
U BLCD -0.01137 -1.48000
U BOND -0.01283 -1.67000
U BWDS NULL NULL
U CGDE -0.06743 -0.95000
U EQIN -0.13277 -2.39000
U GAFR NULL NULL
U GRST -0.11799 -4.07000
U IX4S -0.53996 -12.55000
U LIGE NULL NULL
U LCCS -0.18216 -5.31000
View 2 Replies
View Related
