Best Practice Question: JOIN Criteria Vs. WHERE Criteria
May 24, 2004
For example, consider the following queries:
DECLARE @SomeParam INT
SET @SomeParam = 44
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
WHERE B.SomeParamColumn = @SomeParam
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam
Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?
Thanks.
View 3 Replies
ADVERTISEMENT
Jan 18, 2008
I have a stored procedure which is used to search records in the database based on the data sent from the web page. There are several search fields and all of them are in one table (Table1) except the "CallerName" field which is in a different table (Table2). Since I had to show CallerName also in the gridview apart from other columns, I did a LEFT JOIN (using field CallerNumber) to show all the required fields from Table1 and CallerName from Table2.
Now heres the problem. Since CallerName is a search criteria, its supposed to be in the WHERE clause after the JOIN or in the JOIN clause itself. The problem is, if I put it in WHERE clause, the result set doesn't show records from Table1 which do not have a matching CallerNumber in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName
FROM Table1 T1
LEFT JOIN Table2 T2 on
T1.CallerNumber = T2.CallerNumber
WHERE T1.CallerNumber = 'some number' AND T2.CallerName = 'some name' If I put it in the JOIN condition, it works just like a LEFT JOIN is supposed to work, showing all the records in Table1 and also those which had CallerName in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName
FROM Table1 T1
LEFT JOIN Table2 T2 on
T1.CallerNumber = T2.CallerNumber AND T2.CallerName = 'some name'
WHERE T1.CallerNumber = 'some number'
1st SQL won't work for me because it doesn't show all the records in Table1, even when no search criteria is specified.2nd SQL won't work for me because it shows more than required when just CallerName is sent from the web page as search criteria. It will show all the records where CallerName is "some name" and also all the additional records (since it is a left join).
Can I get the goodness of both in one or do I have to create two separate Stored Procedures?
Thanks all,Bullpit
View 11 Replies
View Related
Oct 14, 2013
I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.
Here is table a:
naics ust_code port all_qty_1_yr all_qty_2_yr all_val_yr all_air_val_yr all_air_wgt_yr all_ves_val_yr all_ves_wgt_yr all_cnt_val_yr all_cnt_wgt_yr all_border_val_yr
11111000 2010 2002 8070569.14298579 0 2335641254.30021 0 0 2335641254.30021 8156408492.66667 0 0 0
11111000 2230 2010 280841.478063446 0 84622385.9133129 0 0 84622385.9133129 299600780.773355 0 0 0
11111000 2410 1401 25735 0 12305667 0 0 12305667 25719794 0 0 0
[Code] ....
and here is table b:
naics ust_code port stat_month Cum_qty_1_mo Cum_qty_2_mo Cum_all_val_mo Cum_air_val_mo Cum_air_wgt_mo Cum_ves_val_mo
11111000 1220 0106 01 2 0 3440 0 0 0
11111000 1220 0107 03 14 0 3442 0 0 0
11111000 1220 0108 09 0 0 0 0 0 0
[Code] ....
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’
/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]
[Code] ....
Output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like
* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)
View 1 Replies
View Related
Jul 20, 2005
I have a set of udf's dealing that return a one column table of valuesparsed from a comma delimeted string.For example:CREATE FUNCTION [dbo].[udf_filter_patient](@patient_list varchar(2000))RETURNS @patient TABLE(patient_id int)ASBEGINinsert into @patientselect patient_id from patient-- parse @patient_list stuff excludedRETURNENDI have come up with the following two schemes to use these udfs. Theseexamples are obviously simplified, and I have a handful of stored proceduresthat will use between 10 or more of these filters. If the two areequivalent, I prefer Method 2 because it makes for much neater SQL whenusing many filter criteria.So my question is, will one noticebly outperform the other? Or is there abetter way in which to filter on a list of criteria?Method 1 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASCREATE TABLE #patient(patient_id int)INSERT INTO #patientSELECTpatient_idFROMdbo.udf_filter_patient( @patient_list )SELECT*FROMopen_billsINNER JOIN #patient on #patient.patient_id = open_bills.patient_idGOMethod 2 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASSELECT*FROMopen_billsWHEREopen_bills.patient_id IN ( SELECT patient_id FROMdbo.udf_filter_patient( @patient_list ) )GOThanks for the help!Chris G
View 4 Replies
View Related
Nov 28, 2007
SELECT Wins, Losses, Wins/Games AS WinningPct
FROM standings
Where WinningPct > 0.5
SQL will not allow me to put a column that I created(WinningPct) as criteria for WHERE (I know this is cause Select is evaluated last)
How can I list my results according to criteria I am creating in my query?
View 9 Replies
View Related
Sep 30, 2007
I have a table
GO
CREATE TABLE [dbo].[Speech] ( [SpeechId] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkSpeech_SpeechId PRIMARY KEY, [UniqueName] [varchar](52) NOT NULL, [NativeName] [nvarchar](52) NOT NULL, [Place] [nvarchar](52) NOT NULL, [Type] [smallint] NOT NULL, [LanguageId] [char](2) NOT NULL CONSTRAINT FkSpeech_LanguageId FOREIGN KEY (LanguageId) REFERENCES Language(LanguageId) ON UPDATE CASCADE ON DELETE CASCADE, [SpeakerId] [int] NOT NULL CONSTRAINT FkSpeech_SpeakerId FOREIGN KEY (SpeakerId) REFERENCES Speaker(SpeakerId) ON DELETE CASCADE, [IsFavorite] [bit] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDate] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDate] [datetime] NULL )
Now I want to search the Table Speech
Sometimes by : SpeechIdSometimes by : SpeakerIdSometimes by : LanguageIdSometimes by : SpeechId And LanguageIdSometimes by : SpeakerId And LanguageId
All can have conditions with IsVisible, IsFavorite and Type columns.
for example
I need all Speeches withany particular SpeakerId and LanguageIdwith IsVisible equals to trueand IsFvaorite No Matterand Type equals to Audio
For these type of queries I think the solution is
GO
CREATE PROCEDURE [dbo].[sprocGetSpeech]
@speechId int = NULL, @uniqueName varchar(52) = NULL, @nativeName nvarchar(52) = NULL, @place nvarchar(52) = NULL, @type smallint = NULL, @languageId char(2) = NULL, @speakerId int = NULL, @isFavorite bit = NULL, @isVisible bit = NULL
AS
SELECT SpeechId, UniqueName, NativeName, Place, Type, LanguageId, SpeakerId, IsFavorite, IsVisible, CreatedDate, ModifiedDate FROM Speech WHERE SpeechId = @speechId AND UniqueName = CASE WHEN @uniqueName IS NULL THEN [UniqueName] ELSE @uniqueName END AND NativeName = CASE WHEN @nativeName IS NULL THEN [NativeName] ELSE @NativeName END AND Place = CASE WHEN @place IS NULL THEN [Place] ELSE @place END AND Type = CASE WHEN @type IS NULL THEN [Type] ELSE @type END AND LanguageId = CASE WHEN @languageId IS NULL THEN [LanguageId] ELSE @languageId END AND SpeakerId = CASE WHEN @speakerId IS NULL THEN [SpeakerId] ELSE @speakerId END AND IsFavorite = CASE WHEN @isFavorite IS NULL THEN [IsFavorite] ELSE @isFavorite END AND IsVisible = CASE WHEN @isVisible IS NULL THEN [IsVisible] ELSE @isVisible END
Can anyone tell me?
Is it right way to do?Do you have any better solution?If my solution is better then Is there any performance loss with that query?
View 1 Replies
View Related
Jul 12, 2002
I am familiar and happy with using BCP to export from SQL Server to a flat file
.. 1) Is there any way to pass a parameter to the sql script file each time so that i can vary the selection critria the script file uses each time?
.. 2) Can i batch the BCP calls together so they all use this parameter with some kind of 'super' BCP cammand?
Thanks in anticipation
View 3 Replies
View Related
Feb 2, 2015
I have a table in the following format
reporting_date interest_payment balance
200401 10 10
200402 20 15
200403 30 20
200404 40 30
200405 50 40
200406 60 50
200407 70 60
i wanted to generate an OUTPUT in the following format :
The output of the query should look like this :
reporting_date interest_payment balance
Q1 -2004 60 10
Q2 -2004 170 30
Q3 -2004 70 60
Q4 -2004 0 0
i.e i wanted to represent data by quarter and year and group by quarter and year for interest_payment column but for balance i need to pick up the value from the first reporting date in that quarter ,so as you can see q1-2004 has 10,15 and 20 but only 10 is accounted as that was the first reporting date in that quarter
I have my query working for interest payment but i am not sure how do i pickup the first reporting value for balance in a quarter
SELECT report_year as "@date",'Q'+CAST(report_quarter+1 as varchar(1)) as "@quarter", SUM(a.balance) as "@balance", SUM(a.interest_payment) as "@interest_payment"
FROM (SELECT *,
(reporting_date%100 - 1)/3 as report_quarter,
reporting_date/100 as report_year
FROM employee) a
GROUP by report_year, report_quarter
order by report_year, report_quarter
View 1 Replies
View Related
Mar 17, 2015
use of Row_Number() over ( partition... but I dont understand how.
Imagine I have a table like
CustomerID, PartNum, QtyinOrder, shipped
1 6 3 0
1 6 2 0
2 6 1 0
2 5 1 0
2 5 2 0
2 5 3 0
2 5 4 1
1 6 4 1
2 6 2 1
But I wanted to return
CustomerID, PartNum, MaxQtyOrderedNotShipped
That would be just the rows
1 6 3 0
2 6 1 0
2 5 3 0
If I use this:
Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1
there is no restriction, so I get the shipped...If I alter the where clause to work only on not shipped, I get no records...as below
Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1 and shipped=0
View 2 Replies
View Related
Jan 27, 2006
Hi,
While playing with SQL Server 2000 I found you can specify the selection criteria in either the FROM clause or the WHERE clause:
e.g.
select *
from Table1 a inner join Table2 b ON a.key = b.key and a.field = 1
Is logically the same as:
select *
from Table1 a inner join Table2 b ON a.key = b.key
where a.a = 1
Any comments on which is best, and why?
Thanks,
Chris
View 3 Replies
View Related
Jun 7, 2006
jiang writes "Apologies in advance for my inexperience.
I have a SQL table to hold my product information:
prods(prodnum(char(10), prodname(char20), quantity(int))
The values in prodname column are like:
ABCDEF
ADCDEF
BCDEFG
CDEFGH
For those products that sold out, I made a mark in the front of prodname, like *ABCDEF
Then in my query, I want to sort the product name in alphabetic order, in addition, I also want to put prodname start with * at the end of the result list, like:
ADCDEF
BCDEFG
CDEFGH
*ABCDEF
I tried to use:
select prodname from prods order by prodname
this query shows *ABCDEF is on the top of the result, then I tried:
select prodname from prods order by charindex('*', namecode)
this query does put *ABCDEF at the bottom, but other records are not in alphabetic order.
Could you please help me? Many many thanks!
Jiang"
View 3 Replies
View Related
Feb 5, 2007
hi all... how do i write my where clause if i wanna search BETWEEN something to something, but at the same time, find ALL if user send nothing (''), NOT searching for '' column... and also find date if they send a date, and if they dont send date, do not consider date at all(find all at any dates).. is this possible to in one where clause without any IF statement... thanks..
WHERE d.Ownership LIKE '%' + @ClientID +'%' AND
d.WhsID LIKE '%' + @WhsFrom + '%' AND
d.CustomLotNo LIKE '%' + @CustomlotnoFrom+ '%' AND
d.LocID BETWEEN @LocFrom AND @LocTo AND
d.ItemID LIKE '%'+ @ItemFrom + '%' AND
substring(d.LocID,1,1) LIKE '%' + @ZoneFrom AND
d.RecvDate <= @Date
~~~Focus on problem, not solution~~~
View 20 Replies
View Related
Mar 22, 2007
I am trying to filter data from columns and this is just not working. If I select all the criteria below and try to run it - I do not get any records returned.
WHERE (DropDt >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 13, 0)) AND (DropDt <= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0))
and Type IN ('Employee', 'Refinance')
and Chan IN ('XM', 'BN', 'RS', 'MM')
and Seg IN ('Hoc','LeftOver', 'COnly')
View 4 Replies
View Related
Aug 31, 2007
I am just learning SQL server 2005 and I am having trouble with the sql statement of my sqlcommand. I am just trying to query for any ticket that was open yesterday. I need this to run daily
If I run the following it works
SELECT Assigned_Group,
Assigned_Technician,
Date_Created
From "Support Center Ticket" where "Date_Created" > '08/30/2007 00:00:00'
and division = 'Northern'
however when I change it
SELECT Assigned_Group,
Assigned_Technician,
Date_Created
From "Support Center Ticket" where "Date_Created" > convert(varchar, getdate()-1, 101) + ' 00:00:00'
and division = 'Northern'
SSIS package "Package_test.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Error: 0xC0047062 at Data Flow Task, DataReader Source [46]: System.Data.Odbc.OdbcException: ERROR [420] Driver]Unexpected extra token: (
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source" (46) failed the pre-execute phase and returned error code 0x80131937.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (6856)" wrote 0 rows.
Task failed: Data Flow Task
Warning: 0x80019002 at Package_test: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package_test.dtsx" finished: Failure.
View 2 Replies
View Related
May 6, 2008
I have a query that searches through a 4 million record table. The data is fed from the UNIX flat file systems so the data is not in optimal search format. So I created some views that massaged the data and then index them. I select and join the original table with the view, with NOEXPAND hint on the view. My question is this theory right: If I put the criteria in the FROM join part then it will make the join easier than if I put it in the where clause?
Example (any difference)
SELECT stuff1, stuff2 FROM UglyData u INNER JOIN MassageTable m ON m.RecNumber LIKE '112%' AND u.ID = m.ID
versus
SELECT stuff1, stuff2 FROM UglyData u INNER JOIN MassageTable m ON u.ID = m.ID WHERE m.RecNumber LIKE '112%'
THANKS!!!
View 3 Replies
View Related
Sep 10, 2007
I am creating a .aspx page that links with Miscrosoft SQL Server 2005 Express. It includes a GridView control that displays all the table data on the page. You can then select a record from the control (currently by clicking an image button to the left of each record- is there any way of selecting the record by clicking anywhere on the row? How would that be done?) and it displays the data in a detailsview control below where the data can be changed etc.
The data is like a phonebook (Name, Telephone number, and some other misc fields) and the user should be able to search by either name or number to filter out the records shown in the gridview control. I have two textboxes for this, and I started with the name text box and it works fine. i.e. with one filterparameter and one filterexpression. So that if you just enter 'Da' it filters out the records displaying only those whose name starts with 'Da'.
I have experimented but have found no way of including filter expressions to use the number as a search. I added the second filter parameter (under sqldatasource control so that:
<FilterParameters>
<asp:ControlParameter Name="DestinationName" ControlID="txtName" /><asp:ControlParameter Name="DestinationNumber" ControlID="txtNumber" />
</FilterParameters>
But I don't know what to do for the FilterExpressions. currently I just have:
FilterExpression="DestinationName LIKE '{0}%'"
i have tried using "DestinationName LIKE '{0}%' OR DestinationNumber LIKE '{0}%'" but it requires that both text boxes have data entered.
What I want is something that allows the user to enter either a name or number or both (all or part of so don't need to enter in full name/number) and it filters out the records accordingly. I.e. if you enterd 'Dav' and '079' it would bring back all the records who had a name starting with Dav and a number starting with 079. However if you enterd just 079 then it should just bring back all records with numbers starting 079 whatever their associated name.
Thanks
View 9 Replies
View Related
Feb 9, 2004
I have a text box that is used to submit stock symbols that are to be saved in a sql table. The symbols are to be separated by a space or a comma (I don't know which, yet). I want to retrieve the symbols later to be used in a query, but I don't know how to get the symbols in the proper string format for the query, eg
The symbols are stored in the tables as: A B C D
The query string criteria would look like: IN('A', 'B', 'C', 'D')
The IN('A', 'B', 'C', 'D') citeria would be the values in the @Symbol variable in this SPROC
SELECT a_Name_Symbol.Symbol, a_Financials.Revenue
FROM a_Financials INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
WHERE (a_Name_Symbol.Symbol @Symbol)
ORDER BY a_Name_Symbol.Symbol
Is there a slick (ie easy) way to change the contents entered in the text box (A B C D) into IN('A', 'B', 'C', 'D') ?
Thanks,
Paul
View 1 Replies
View Related
Mar 11, 2005
I need a little insight on how to select the same field from the same table, but for different criteria.
here are example tables...
Categories
CATSUBCATNAME
10MainTitle
11SubTitle #1
12SubTitle #2
20Section
21Section #1
DataTable
CATSUBCATINFO
11Detail Information for subtitle #1
12Detail information for subtitle #2
desired result would be:
MainTitle, SubTitle #1, Detail Information for subtitle #1
MainTitle, SubTitle #2, Detail Information for subtitle #2
Select c1.Name, c2.Name, d.info
from DataTable d, Categories c1, Categories c2
where c1.CAT = d.CAT
and c2.CAT = d.CAT
and c2.SUBCAT = d.SUBCAT
View 1 Replies
View Related
Jan 4, 2006
I have a database with some over normalized tables in it. The best I can do with one query is get the file ID. In the second query I want to get all the file names, based on all the fileID's I got from the first query. How would I go about doing this?
View 5 Replies
View Related
Aug 28, 2000
Is it possible to to view 2 tables with a common field name and display it in the following way
Name telephon no.
John 123-4567
789-4561
987-6543
Peter 159-7536
654-9874
896-3214
456-9874
without repeating the name in each row.
Thanks
View 1 Replies
View Related
Aug 25, 2005
Hi,
I was wondering if it is possible to order a recordset by specific values.
That is, I want my recordset to be ordered alphabetically, but I want to set the order of the alphabet.
For example, I don't want my recordset to be odered by A, B, C, D ...I want it to be ordered by G, V, T, A ... or something.
Can you do something like
Code:
SELECT * FROM myTable ORDER BY myField (G, V, T, A, B, C, Q, X)
...etc
Thanks
e
View 2 Replies
View Related
Nov 3, 2006
I have a table with 500 stores. (StoreNumber, StoreZip, StoreOpenClosed [999 if store is closed, 1 if store is open])
I have a view that only selets the open stores
SELECT *
FROM tblStores
WHERE StoreOpenClosed <> 999
This selects all the open stores. There are 2 instances where there are two stores in the same zip code.
EX StoreNumber: 3452 Zip: 02192
EX StoreNUmber: 5325 Zip: 02192
I only want to select ONE store per zipcode. Maybe even select one of the two stores randomly. How could I do this with out hardcoding anything?
Thanks
Dynasty
View 2 Replies
View Related
Aug 27, 2004
I have a table with a record that looks like the attached TXT.
I need to keep the most recently entered value where flag_out = 1 and delete those duplicate
records, and this should only apply to records where there's also an flag_in value of 1.
I've tried a bunch of delete statements without avail....
TIA
View 3 Replies
View Related
Oct 5, 2005
How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.
So is there any other way to handle multiple criteria in T-SQL ?
View 12 Replies
View Related
Feb 6, 2012
I'm having a problem writing a SQL query that excludes certain data. This is for a pay stub application to display current and previous paycheck stubs. To calculate certain data such as YTD figures and time off, we SUM on other tables. However, to display correctly, I can't SUM bonus checks for the current payperiod ONLY - but for previous pay periods, I must SUM bonus checks.
Here's an example of my data:
No code has to be inserted here.
No code has to be inserted here.
No code has to be inserted here.
No code has to be inserted here.
Right now my SQL is this:
Code:
SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum]
FROM PR04PTF
INNER JOIN PR11ERF_History ON
PR11ERF_History.EmployeeID = PR04PTF.EmployeeID
AND PR11ERF_History.PayPeriodEnd <= PR04PTF.PayPeriodEnd
WHERE PR04PTF.EmployeeID=441
View 3 Replies
View Related
Sep 17, 2014
I am trying to increase the price of an product by a user entered % for items with Dishwasher in the itemdesc.
Below is the procedure I have which doesn't appear to show any errors in the SQL Developer.
CREATE OR REPLACE PROCEDURE AdjustPrice(
pItemDesc IN ITEM.ItemDesc%TYPE,
pPercent IN NUMBER)
IS
BEGIN
UPDATE Item
SET ItemPrice = ItemPrice + ItemPrice * pPercent / 100
WHERE ItemDesc = pItemDesc;
END;
This is my run script:
BEGIN
AdjustPrice ('%Dishwasher%',10);
END;
I think the problem is with the way I have done the run script to filter to items with Dishwasher in the description. I tried LIKE an that didn't work either.
View 2 Replies
View Related
May 14, 2008
Hi,
Slightly knotty and hard-to-articulate problem here, so please bear with me.
As part of a series of transactions, I have a table which contains data which looks like this:
AdBookingID adWeeks AdID clientId valueid
----------- ----------- ----------- ----------- -----------
97336 3 95127 248 1007
98220 1 94304 458 1007
98220 1 94304 458 7610
98386 1 88123 319 7604
98388 1 91484 319 7610
98390 1 91963 319 7610
98392 1 92468 319 7601
98392 1 92468 319 7608
The key to this problem is the "valueID". The first digit of the value is significant - the "1" and the "7" in the above mean the values are parts of different groups of search criteria.
What I need to do is to select from this list into a temp table all those bookings (identified by AdBookingID) which have valueIds of both types - i.e. an row in the table where the valueId starts with a 1 and a row in the table where the valueId starts with a 7 - in the above data sample the only AdBooking which qualifies is 98220.
I worked out a method of doing this going by the number of entries each item had in the table:
select count(distinct valueId)
as valueId, adWeeks, clientId, adID, AdBookingID
into #worktable
from #tmp
group by adWeeks, clientId, adID, AdBookingID
having count(distinct valueid) > 1
Which is largely accurate. But - and here's the killer - although each adBooking can only have one valueId starting with 1, it can have multiple entries starting with 7. These bookings are rare, but they do exist and are causing anomalies in the data returned which - if you recall - must only contain booking records for which there are valueId entries with both a 1 and a 7.
Can anyone suggest a way I can get just the data I need?
Cheers,
Matt
View 7 Replies
View Related
Dec 4, 2013
I am very new to sql programming. I have a database with 15 columns. At this moment I do this to get all rows containing the year 2013:
WHERE DATEPART (yyyy, DownloadDate) = 2013 .
But I also want to add a criteria so that I can have all rows where DownloadDate contains 2013 AND WHERE IsRead(BIT) contains NULL. I tried this:
WHERE DATEPART(yyyy, DownloadDate) = 2013 AND IsRead = null .
But this gave me nothing!
View 3 Replies
View Related
Mar 20, 2014
If I have a table (lets name it table1) contains the columns: Customer Name, Creation date, Call status and I want to create a query for the last call status per Account name how I am going to do that via sql? I tried :
SELECT DISTINCT customer name, creation date, call status FROM table1
And I got less rows but there where still duplicates (customer name) since the call status was different..
View 8 Replies
View Related
May 16, 2006
Hi, I have a problem which is probably really simple to solve but I have gotten a tilt and cant get it...Hoping you can help me.
What Im trying to do in pseudo-code:
Get A from a tableX where B = 0
Check tableY for each A and get B from there
Update all B in tableX where TableX.LotNo=TableY.Lotno with the value from TableY.B
Anyone feel generous enuf to help a newbie out?
View 10 Replies
View Related
Jan 24, 2008
I have a query in access that is running extremely slow and I'm trying to find a better way to write it. It appears to be the criteria statement that is causing the lag. Is there a better way to write this? And unfortunately I have to keep it in access.
query:
SELECT DISTINCT "JBC" AS ClientCode, PaymentDetail.PatientNumber, Procedures.CaseNumber, IIf(IsNull([TicketNumber]),Procedures.patientnumber & Year(Procedures.dateofservice) & Month(Procedures.dateofservice) & Day(Procedures.dateofservice),Procedures.ticketnumber) AS ClaimNumber, PaymentDetail.PaymentCounter, PaymentDetail.TransAmount, Payments.PaymentDate, PaymentDetail.AccountingDate, PaymentDetail.TransDate, payments.PaymentType & "-" & Adjustments.adjustmentcode AS CombinedPmtType, Payments.PaymentType, Payments.PaymentCode, Adjustments.AdjustmentCode, PaymentCodes.BriefDescription, PaymentCodes.LongDescription, Payments.CarrierCode, Payments.Remarks, Procedures.ProcedureCode, Procedures.DateOfService, PaymentDetail.DetailCounter
FROM ((((PaymentDetail LEFT JOIN Procedures ON (PaymentDetail.PaymentCounter = Procedures.Counter) AND (PaymentDetail.AccountingDate = Procedures.AccountingDate) AND (PaymentDetail.PatientNumber = Procedures.PatientNumber)) LEFT JOIN Payments ON (PaymentDetail.TransDate = Payments.AccountingDate) AND (PaymentDetail.TransCounter = Payments.Counter) AND (PaymentDetail.PatientNumber = Payments.PatientNumber)) LEFT JOIN PaymentCodes ON Payments.PaymentCode = PaymentCodes.PaymentCode) LEFT JOIN Adjustments ON (PaymentDetail.TransDate = Adjustments.AccountingDate) AND (PaymentDetail.PatientNumber = Adjustments.PatientNumber)) LEFT JOIN AdjustmentCodes ON Adjustments.AdjustmentCode = AdjustmentCodes.AdjustmentCode
WHERE (((Procedures.CaseNumber)=0) AND ((PaymentDetail.TransAmount)<>0) AND ((Payments.PaymentType) Is Null) AND ((PaymentDetail.DetailCounter)=2)) OR (((Procedures.CaseNumber)=0) AND ((PaymentDetail.TransAmount)<>0) AND ((Payments.PaymentType) Is Null) AND ((PaymentDetail.DetailCounter)=5) AND ((AdjustmentCodes.InsuranceIndicator)="N"));
View 5 Replies
View Related
Feb 16, 2007
I have a table t1 with two columns : c11 varchar(32) , c22 varchar(32)The data in the table is :'11', 'aa01'and on upto'11', 'aa50' : total 50 entries'22', 'b01''22', b'02''22', b'03''33', 'c01' to '33', 'c40' : total 40 entries'44', 'b02''44', 'd01''44', 'd01''44', 'd01'How can write a query which will bunch together values of c11with rows 5, and then bunch together values of c11 withrows < 6, and add them up.My output should be :'11' 50'33' 40'others' 7 (3 rows for '22' and 4 for '44' are bunchedtogetheras the # of rows < 6, and added. 3+4 = 7)
View 1 Replies
View Related
Jul 26, 2007
Hi,
I created one fact and four dimensions in SSAS2005. I am accessing these in SSRS 2005.
But my problem is to develop one report with this fact i have to keep a criteria like this in SSRS 2005. how to compare a column in one dimession with another column in another dimension
For Ex:
CONVERT(VARCHAR,dt1.date,102)>= CONVERT(VARCHAR,dt2.Date,102)
Where should i keep this condition in SSRS 2005.
Can you help me
Thanks
Dinesh
View 4 Replies
View Related
