How To Append Where Clause To Existing Statement
Jun 17, 2015
I have SQL statement like this ..
CREATE PROCEDURE [dbo].[GetProspectBySearch]
@StartRowIndex AS INT,
@MaximumRows AS INT,
@ModuleType AS VARCHAR(50) = NULL,
@ProspectCategory AS VARCHAR(50) = NULL,
@ProspectsWithoutContact AS BIT = NULL
AS
BEGIN
IF @Status = 'All'
BEGIN
SET @Status = NULL
END
[code]....
1) How do I can write count and paging statements in to single statement ?
2) There is parameter @ProspectsWithoutContact is using for differentiation for count and paging statements. If @ProspectsWithoutContact is "1" then this clause ( AND ContactPerson = NULL AND TelNo = NULL AND FaxNo = NULL) will be added in to both count and paging statements. How do I can write this in to single statement with out IF ELSE ?
write normal SQL with out dynamic SQL ?
View 3 Replies
ADVERTISEMENT
Jul 25, 2005
I have an existing table I need to add data to. The data is in a text file, and the existing table already has data in it (I don't want to delete this I want to add to it).
I used Microsoft's import utility but this created a seperate table with generic fieldnames (column01, column02, ect). Is there a step in this wizard I missed?
View 2 Replies
View Related
Nov 21, 2006
Help,
I have a client that requres me to add a header line and trailer line to a flat file. The trick is the header and footer row is required to have a length of 120 with 5 colunmns while the data included in the query has a length of 1125 and about 70 columns. How can I append a header row to a file through SSIS.
View 1 Replies
View Related
Dec 28, 2014
I have 10k indexes I need to rebuild and each time the script reaches an error it stops all further activity. How can I append 'GO' to the end of each line so it will continue on error messages?
Once I have the syntax I can do a find and replace function in Notepad++
USE [AdventureWorks2014] + char(13) + char(10) + GO
ALTER INDEX [IX_Person] ON [Person].[Person] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO
ALTER INDEX [IX_Emp] ON [HumanResources].[Employee] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO
************** Truncate ***********
View 3 Replies
View Related
Sep 24, 2007
Dear All,
I know the basic defiinition of these two options, but i am not very clear why would someone choose one over another,
currently I am using Append to Media option, and every day backup, I see my backup files growing in size.
can someone give me nice example about these two options,
Thanks,
View 10 Replies
View Related
Jun 4, 2007
i have a case : i'm using a select statement with SqlCommand and save the results in the SQLDataAdapterand using the data table I post the result to the gridview and show it there....my question is what should I do to append string to the SQL Command??
View 2 Replies
View Related
Feb 18, 2014
I have written a merge Statement where i am facing trouble to delete the data basing on Where Clause Condition.
1) Case 1 : For example i have inserted Data from Source to Target based on Date Key Condition.Take an Instance 10 Records Inserted.
2) Case 2 : For example some changes in the records and it has been updated through the Merge Statement .
3) Case 3 : For the Same Date key based on Conditions now three records has came and need to be inserted and rest should be deleted for that Date Key.
How i need to proceed on this before 10 records are not getting deleted and new records adding for that one
My Example Code :
MERGE INTO TargetTable AS Target
USING (
SELECT DISTINCT Col1,
Col2
FROM Table1 AS cga
)
ON dad.AnchorDate = CASE
[Code] ....
View 1 Replies
View Related
Apr 3, 2008
I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query:
SELECT A.COMPANYCODE,
A.INVOICENUMBER,
A.LINENUMBER,
A.SONUMBER,
A.CUSTOMERCODE,
A.SHIPPERNUMBER,
A.INVOICEDATE,
A.ITEMCODE,
A.QUANTITYINVOICED,
A.UNITPRICE AS 'InvPrice',
A.QUANTITYINVOICED * A.UNITPRICE AS 'ExtInvPrice',
INVENTORY.UNITPRICE AS 'StdPrice',
INVENTORY.STANDARDCOST,
A.QUANTITYINVOICED * INVENTORY.STANDARDCOST AS 'ExtCost',
(A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$',
(INVENTORY.UNITPRICE - A.UNITPRICE) * -1 AS 'PriceListDiff'
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY
WHERE INVENTORY.COMPANYCODE = A.COMPANYCODE
AND INVENTORY.ITEMCODE = A.ITEMCODE
AND ((A.COMPANYCODE = 'csp')
AND (A.QUANTITYINVOICED <>$ 0)
AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08'))
ORDER BY A.INVOICEDATE,
A.ITEMCODE
They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this:
SELECT NewColumn = CASE
WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice
ELSE 'null'
END,
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY
I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list.
Any help would be greatly appreciated to modify the current query to display the new column.
View 2 Replies
View Related
Mar 31, 2008
Hello all,
I have the following SQL statement that works great for my purpose:
SELECT DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0)) AS TimePeriod, AVG(ZNT) AS Expr1, AVG(SAT) AS Expr2, AVG(RAT) AS Expr3,
AVG(RH) AS Expr4
FROM HVACValues
GROUP BY DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0))
ORDER BY TimePeriod
I'd like to do everything above but add to display only those records from 1/1/07 to 1/7/07...so a week's worth.
I'm no SQL expert but I assume that I'd add 'WHERE TimePeriod = ....' Any help is much appreciated!
Thank you very much.
View 3 Replies
View Related
May 30, 2002
I have the below where clause on a select
where substring(pa.other_id_number,5,1) = @monthid
and pa2.df_patstatus like 'act%'
and ((pa2.df_institutionname not like ('ACT%')
or pa2.df_institutionname not like ('county%')))
It returns records that have either one of the not like statements. If I comment out the line and use below it will work for one of the statements
where substring(pa.other_id_number,5,1) = @monthid
and pa2.df_patstatus like 'act%'
and pa2.df_institutionname not like ('ACT%')
How can I get the two to work together. I thought the parenthesis would work but no luck.
View 1 Replies
View Related
Dec 2, 2004
actually i need to do a sql report... there are 3 parameters (date from. date to, issue_user_id) that are required in order to retrieve the data...now i am facing one problem, if i just enter the date_from and date_to, and leave the issue_user_id blank, i wish to retrieve all the data, but i can't do it.
i have try to use:
if @IssueUserID = ' ' Select....(blah blah ) else select (blah blah)...it is long and work in Query analyzer but no working in .NET Reporting services.... :confused: is it possible to enter IF ..ELSE statement in WHERE clause?hope that u all can help.....
================================================== ==
SELECT REPLACE(REPLACE(REPLACE(REPLACE(tbl_Receipt.Paymen tRefID, '1', 'Deposit for Admission'), '2', 'Consultation Services'), '3', 'Medical Fees'), '4','Others') AS PaymentRefID, tbl_Receipt.ReferenceNo, tbl_Receipt.ReceiptDate, CONVERT(varchar(10), tbl_Receipt.ReceiptDate, 103) AS ReceiptDt, tbl_Receipt.TotalAmountPaid, tbl_Receipt.Payee, tbl_ReceiptDT.PaymentTypeID, tbl_PaymentType.PaymentType, tbl_ReceiptDT.Amount,
tbl_User.Name, tbl_Receipt.IssueUserID, tbl_User.Designation
FROM tbl_Receipt WITH (nolock) INNER JOIN
tbl_ReceiptDT WITH (nolock) ON tbl_Receipt.ReceiptID = tbl_ReceiptDT.ReceiptID INNER JOIN
tbl_PaymentType WITH (nolock) ON tbl_ReceiptDT.PaymentTypeID = tbl_PaymentType.PaymentTypeID INNER JOIN
tbl_User ON tbl_Receipt.IssueUserID = tbl_User.UserID
WHERE (tbl_Receipt.ReceiptDate BETWEEN CONVERT(datetime, @DateFrom, 103) AND CONVERT(datetime, @DateTo, 103)) AND
(tbl_Receipt.IssueUserID = @IssueUserID)
ORDER BY tbl_User.UserID, tbl_ReceiptDT.PaymentTypeID
View 5 Replies
View Related
Oct 22, 2013
I am trying to write sql to basically say if c.rel_value IN ('XYZ','ABC') then rel_attr_id = 'A0' else rel_attr_id = 'C1'...bbut to get the rel_value we need the rel_attr_id to be 'A0' to begin with....so my statement currently is...
select c.client, c.attribute_id, b.dim_value, a.description, c.rel_attr_id, c.rel_value, 'N' as status
from agldescription a, agldimvalue b, aglrelvalue c
where b.client = c.client AND b.attribute_id = c.attribute_id AND
b.dim_value BETWEEN c.att_val_from AND c.att_val_to AND
a.client = b.client AND a.attribute_id = b.attribute_id AND
a.dim_value = b.dim_value AND a.language = 'EN' AND
a.attribute_id = '70' AND c.rel_attr_id = 'A0'
then I want to say as per above but if the rel_value are in XYZ or ABC then sqap rel_attr_id to 'C1' otherwise keep with 'A0'...
View 4 Replies
View Related
Aug 20, 2006
Can the like clause be used in IF statement or only applicable to SELECT statement ?
e.g.
IF (@lADDRESS LIKE '%P.O. BOX')
BEGIN
PRINT 'IGNORE ADDRESS'
END
Thank you
Yog
View 1 Replies
View Related
Nov 8, 2015
I am new to use MERGE statement. The MERGE cannot find any match Cardnumber in the target table. It inserts row into an existing row on the target table causing SQL rejected with duplicate key not allowed. The CardNumber is defined as a primary key on the target table with no duplicate allowed. Below snippet stop when MERGE insert a row exists on the target. The source table contains multiple rows with the same Cardnumber because it is a transactional table with multiple redemptions.
If MERGE cannot handle many (source) to one (target) relationship, what other method that I can change to in order to update the target GiftCard table which keeps track of gift card balance?
Below is the error message:
Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK_GiftCard'. Cannot insert duplicate key in object 'dbo.GiftCard'. The duplicate key value is (63027768).
The statement has been terminated.
DDL of the target table:
CREATE
TABLE [dbo].[GiftCard](
[CardNumber] [varchar](50) NOT NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[Period] [int] NULL,
[TransAmount] [decimal](10, 2) NULL,
[Code] ....
View 8 Replies
View Related
Jan 22, 2007
I need a SQL statement that selects a specific year (@yr type int) in the "createddate" column...if this @yr is equal to 0 then I want to select ALL columns regardless of the year...This is what I have so far, but it doesnt work...SELECT * FROM tblUsersWHERE year(CreatedDate)=CASEWHEN @yr<>'0' THEN @yrELSE NOT NULLEND
View 1 Replies
View Related
Oct 3, 2007
Hi!I want to make search engine and I have problem with query for this search. User can write username to search or text to search or both. So at first I made query for each event individually: ALTER PROCEDURE [dbo].[Show_Search_Topics]
@username varchar(200),
@search_text varchar(200),
@days int
AS
DECLARE @date DATETIME
SET @date = DATEADD(day,@days,GETDATE())
IF @username IS NOT NULL AND @search_text IS NULL
BEGIN
SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id,
forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id,
dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total
FROM dbo.forum_topics INNER JOIN
dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN
dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN
dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN
dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id
GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date,
forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor,
dbo.forum_kategorie.forum_kat_nazwa
HAVING (forum_topics_1.post_parrent_id = 0) AND (dbo.aspnet_Users.UserName = @username) AND (forum_topics_1.post_current_date >= @date)
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
END
ELSE
IF @username IS NULL AND @search_text IS NOT NULL
BEGIN
SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id,
forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id,
dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total
FROM dbo.forum_topics INNER JOIN
dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN
dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN
dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN
dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id
GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date,
forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor,
dbo.forum_kategorie.forum_kat_nazwa
HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%')
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
END
ELSE
IF @username IS NOT NULL AND @search_text IS NOT NULL
BEGIN
SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id,
forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id,
dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total
FROM dbo.forum_topics INNER JOIN
dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN
dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN
dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN
dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id
GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date,
forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor,
dbo.forum_kategorie.forum_kat_nazwa
HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%') AND
(dbo.aspnet_Users.UserName = @username)
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
END
RETURN This 3 queries are different only by Having clause. So I want to put If/Case in Having clause, but I have problem. Can anyone help me?Also I want to make paging from SQL level, so if anyone will be so helpful and make working this query with this: ALTER PROCEDURE [dbo].[Show_Search_Topics]
@username varchar(200),
@search_text varchar(200),
@days int,
@page int,
@page_size int
AS
WITH Results As
(
//QUERY)
)
SELECT * FROM Results
WHERE RowNumber BETWEEN (@page_size * @page + 1) AND (@page_size * (@page + 1))
ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC
RETURN I will be grateful :-)
View 5 Replies
View Related
Feb 21, 2008
Hi,
I need help to use CASE Statement within my WHERE Clause. I want to change the WHERE Clause based on my condition as following:
SELECT ...
FROM ...
WHERE (condition)
AND (condition)
AND (condition)
AND (
CASE Table.Category
WHEN 'Drinks' THEN
Table1.Field1 = 1 -- Problem line
ELSE
Table1.Field1 = 1 AND Table1.Field2 = 1 -- Problem line
END
)
In the above case, my WHERE Clause is dependent on one of the fields in a table. If it has a certain value then only Table1.Field1 is used otherwise Table1.Field1 & Field2 come into action.
I am getting error on the mentioned line (Problem Line). Since CASE is an expression and does not execute a statement, can anyone help me to get my WHERE Clause working...
Thanks in advance...
View 3 Replies
View Related
Jun 29, 2005
I have a column in the database that stored moduleId that are seperated by '|' (pipes). For Example: '527|343|454'
I need to add a where clause to a query that pulls the data based on a
ModuleId. For Example: select * from table where 527 in [column above]
Does anyone know how I can do this in a query? Normally I could
use an IN statement, ex: select * from table where 527 in (527,343,454)
How can I get the column in that format?
Thanks for the help in advance,
KM
View 2 Replies
View Related
Aug 13, 2003
can i use case statement in where clause.
The scenario is as follow
declare @param int
select
*
from
table1
where
column1 = 'asdf'
column2= @param
In the above sql, if @param is '' then i don't want to include it in the where clause.I can use "like" statement for that, but i want exact value not partial value.
can i use case in where clause so that if @param is '' then i will not include in the where clause
View 10 Replies
View Related
Apr 7, 2006
Can I do the following? Keep getting an error stopping at the first < of the where clause.
declare @mon as int, @yr as int, @myDate as varChar(20)
set @yr=2006
set @mon=1
set @mydate='01/31/2006 23:59:59'
select 0 as DTAP, 0 as DT, 0 as TD, 0 as HIB, 0 as IPV, 0 as MMR, 0 as HEPB, 0 as _VAR, count(v.procedureKey) as FLU, 0 as PPV23, 0 as PCV7, v.chartID, max(rs1.dateService) as dateService from dbo.tbl1 v,
(select distinct dateService, chartID, procedureKey from fhc.dbo.tbl1 where (datePart(year,dateService)=@yr and datePart(month,dateService)=@mon) and (procedureKey='90657' or procedureKey='90658')) as rs1
where (v.chartID=rs1.chartID) and (v.procedureKey=rs1.procedureKey) and
(case when @mon=1 then dateDiff(month,dateService,@myDate)< 216
when @mon=2 then dateDiff(month,dateService,@myDate)<244
when @mon=3 then dateDiff(month,dateService,@myDate)<275
when @mon=4 then dateDiff(month,dateService,@myDate)<305
when @mon=5 then dateDiff(month,dateService,@myDate)<336
when @mon=6 then dateDiff(month,dateService,@myDate)<366
when @mon=7 then dateDiff(month,dateService,@myDate)<32
when @mon=8 then dateDiff(month,dateService,@myDate)<63
when @mon=9 then dateDiff(month,dateService,@myDate)<93
when @mon=10 then dateDiff(month,dateService,@myDate)<124
when @mon=11 then dateDiff(month,dateService,@myDate)<154
when @mon=12 then dateDiff(month,dateService,@myDate)<185
end) group by v.chartID, rs1.procedureKey
View 3 Replies
View Related
Aug 10, 2004
Hello
I want to put a case statement into a where clause but it's not working. Can anybody help, or tell me a better way of doing this
Thanks very much
declare @param varchar (100)
select @param = 'mytext
select
colA
,colB
,colC
from
mytable
where
(case
when @param is null then colA = 'group'
else colA = 'single'
end)
View 3 Replies
View Related
Mar 15, 2007
i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table:
(simple ex)
Select order_no
from table1
inner join table2
on table1.order_no = table2.order_no
group by order_no
having table1.Qty<> table2.Qty
BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes:
CASE WHEN table1.Qty<> table3.Qty
THEN table3.Qty<> table2.Qty
ELSE table1.Qty<> table2.Qty END
but how do i actually write this?
View 3 Replies
View Related
Mar 26, 2012
I'm trying to use a conditional statement in the where clause.
Here is my table
UID Amount ID PID Amount2
1 30000 8064 NULL NULL
2 30000 8042 8064 30000
What I'm trying to achieve:
If Amount = Amount2 for UID 2 then show UID 1
View 4 Replies
View Related
Nov 13, 2014
I have someone telling me that I should have put my AND statement in the join instead of the where part, so is there a difference in the where I put it. Is there a difference in the results in any way between the two here in the results in anyway?
Example:
Given a query:
But let’s take a simple one here:
Query(1)
Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode
From cutTableA ct1 Left join cutTableA ct2
On ct1.ID = ct2.ID
Where ct1.zipcode = '14124'
AND ct1.Name = 'Bob'
Query(2)
Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode
From cutTableA ct1 Left join cutTableA ct2
On ct1.ID = ct2.ID
AND ct1.zipcode = '14124'
Where ct1.Name = 'Bob'
View 4 Replies
View Related
Apr 17, 2008
I need to have a CASE statement inside a WHERE clause. Is this possible? Here is my WEHRE clause. Any suggestions are appreciated:
WHERE
--r.pBOM_ID=d.fEngr_BOM_ID and
r.fItem_ID=a.pEngr_Item_ID and
r.level<=@v_level+1 and
case when @v_showrootlevel=1 then r.level>1 else r.level>1 end
order by r.pID
View 3 Replies
View Related
May 8, 2008
I am trying to create a condition if the value of a parameter is null then pass a certain WHERE condition to my query. I keep on getting this error:
Line 15: Incorrect syntax near '='.
this is my query that lies on a PROC:
SELECT count(a.pEngr_Item_ID) as assembly_count FROM Engr_Item a
INNER JOIN Engr_BOM_Control b
ON a.pEngr_Item_ID=b.fEngr_Item_ID
WHERE
b.Bill_Type=@v_Bill_Type and
a.Item_No=@v_Item_No and
case when @v_Rev_Lett is not null then a.Item_No=@v_Rev_Lett else a.Item_No=@v_Rev_Lett end
View 6 Replies
View Related
Jun 12, 2008
Hi Everyone,
I have the following stored procedure, I would like to use
IF statement or something of the sort in the where clause i.e.
The last line in the SP is: AND (category.categoryID = @categoryID),
I only want to check this, if @categoryID is not = 12.
So can I do something like this:
IF @categoryID <> 12
AND (category.categoryID = @categoryID)
STORED PROCEDURE:
CREATE PROCEDURE sp_get_total_risk_patients
@categoryID int
AS
SELECT COUNT(DISTINCT patient.patientID) AS total_patients
FROM patient
INNER JOIN patient_record ON patient.patientID = patient_record.patientID
INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID
INNER JOIN category ON category.categoryID = sub_category.categoryID
WHERE risk = 6
AND (completed_date = '' OR completed_date IS NULL)
AND (category.categoryID = @categoryID)
View 4 Replies
View Related
Jun 27, 2007
Hello all,
I've got a question. I have an array that I am inserting into a table. The code is in classic asp and looks like this:
SQL1 = "INSERT INTO transactionz (classid, userid) values (" & listOfAnswers(i) & ",8)
The problem is that I want to only insert values that have the value "1". listofAnsers is an array that returns values of "1" and "2". If there a way to limit the inserts to values of "1". Another words, I want to just insert the values of "1" into the table. I know that you experts would say I should control this on the code side and thats true, however, I can't (because I don't control the code side) so is there a way to do this via SQL? Am I making any sense? I was thinking that I needed to add a "where" clause at the end of the code, but I don't know where to go from here.
View 1 Replies
View Related
Oct 13, 2006
If you could help me with my syntax i would really appreciateit. I'm trying to change the where clause on the fly, but it'sgiving me a syntax error.Hopefully from what I have below you can tell what i'm afterthe first part should evaluate a boolean expression, then if true,search using one field, otherwisesearch using a different fieldWHERECase WHEN @myCompany = 933THEN tblClient.companycode = 933 --problem lineELSEtblCase.clientcode = @myClient --problem lineENDAnd tblCase.status = 'Active'thank you!!
View 9 Replies
View Related
Jul 20, 2005
Can anyone tell me if it's possible to use a Case statement in a Whereclause, and if so, the proper syntax?J.R.Largo SQL ToolsThe Finest Collection of SQL Tools Availablehttp://www.largosqltools.com
View 2 Replies
View Related
Oct 15, 2007
Hi,
I have the following bit of SQL:
Code Block
INSERT INTO [INTRANETSQLEXPRESS].Trapped.dbo.TBL_Debtsolv (Debtsolv_ID, Payment_Amount, Payment_Status, Client_Status)
SELECT Client.ID AS ClientID,
InitialInstallment.OffsetAmountExpected AS FirstExpectedPayment
,
CASE WHEN Payment.TotalPaid <= 0 OR LatestPaymentDate IS NULL
THEN 'No Money Paid'
WHEN Payment.TotalPaid < InitialInstallment.OffsetAmountExpected
AND Payment.TotalPaid >0
AND InitialInstallment.DateExpected + ISNULL(Schedule.DayOffset, 0) >GETDATE()
THEN 'Still Awaiting - Part Paid'
WHEN Payment.TotalPaid >= InitialInstallment.OffsetAmountExpected
THEN '1st Payment Made'
WHEN Payment.TotalPaid < InitialInstallment.OffsetAmountExpected
AND InitialInstallment.DateExpected + ISNULL(Schedule.DayOffset, 0) <GETDATE()
THEN 'Late - First Payment Not Made'
ELSE 'Status Unclear'
END
AS Status
, CASE WHEN
Client.Status IN(0,1,2,3,4,5,6,7)
THEN 'In Preparation'
WHEN Client.Status IN(8,9)
THEN 'Active'
ELSE 'Inactive'
END AS ClientStatus
FROM SALEEN.Debtsolv.dbo.Client_Contact as Client
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Payment_Schedule AS Schedule
ON Client.ID = Schedule.ClientID
INNER JOIN SALEEN.Debtsolv.dbo.Client_LeadData CLD
ON Client.ID = CLD.Client_ID
INNER JOIN SALEEN.Debtsolv.dbo.Type_Client_Status TCS
ON Client.Status = TCS.ID
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Type_Lead_Source TLS
ON CLD.SourceID = TLS.ID
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Type_Lead_Source_Group AS LeadGroup
ON TLS.[Group] = LeadGroup.ID
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Users [User]
ON CLD.Counsellor = [User].ID
LEFT OUTER JOIN (SELECT ClientID
, SUM(CAST((Amount + ISNULL(AmountOffset, 0))AS Money) / 100) AS OffsetAmountExpected
, MAX(DateExpected) AS DateExpected
FROM SALEEN.Debtsolv.dbo.Payment_Schedule AS PS
WHERE (SequenceID <=(SELECT
CASE WHEN NumInitialInstalments = 0 THEN 1
ELSE IsNull(NumInitialInstalments, 1)
END
AS NumInitialInstalments
FROM SALEEN.Debtsolv.dbo.Client_LeadData AS LD
WHERE (Client_ID = PS.ClientID)))
GROUP BY ClientID
)
AS InitialInstallment
ON Client.ID = InitialInstallment.ClientID
LEFT OUTER JOIN (SELECT Receipt.ClientID
, SUM(CAST(Receipt.Amount AS Money)) / 100 AS TotalPaid
FROM SALEEN.Debtsolv.dbo.Payment_Receipt Receipt
INNER JOIN SALEEN.Debtsolv.dbo.Type_Payment_Status Type
ON Receipt.Status = Type.ID
WHERE (Receipt.Status = 5)
GROUP BY ClientID)
AS Payment
ON Client.ID = Payment.ClientID
LEFT OUTER JOIN (SELECT ClientID
, MAX(ID) AS MaxPSID
, MAX(LastPayment) AS LatestPaymentDate
FROM SALEEN.Debtsolv.dbo.Payment_Schedule Schedule
WHERE (NOT (LastPayment IS NULL)
AND LastPayment > CONVERT(DATETIME, '1980-01-01 00:00:00', 102))
GROUP BY ClientID)
AS LatestPaymentDate
ON Client.ID = LatestPaymentDate.ClientID
INNER JOIN (SELECT ClientID
, MAX(SequenceID) AS LFPDSequenceID
FROM SALEEN.Debtsolv.dbo.Payment_Schedule PS
WHERE (PaymentType = 1)
GROUP BY ClientID)
AS LastPayment
ON Client.ID = LastPayment.ClientID
AND Schedule.ClientID = LastPayment.ClientID
AND Schedule.SequenceID = LastPayment.LFPDSequenceID
WHERE Client.ID = TBL_Debtsolv.Debtsolv_ID
Executing this statement gives the following error:
Code Block
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TBL_Debtsolv.Debtsolv_ID" could not be bound.
I think thsi is soemthing to do with referencing another table in the INSERT into command as if I put:
Code Block
WHERE Client.ID = '12065'
The statement executes with no problem. I think I have read in some other threads that you cannot reference another table in the WHERE part of an INSERT INTO clause but cannot find out how I can make this work.
Most of the statement comes from a pre-made report from one of our systems and I have slighlty changed it to insert the data into another database that we have.
Any suggestions would be greatly appreciated
Thanks
View 1 Replies
View Related
May 16, 2006
Is it possible to use CASE within a WHERE?
I have a query which is something like this, but it returns an error:
SELECT * FROM tablex
WHERE
CASE WHEN 'sexec' IS NOT NULL THEN
dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' and dm_sexec LIKE 'sexec' ELSE
dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' END
GROUP BY dm_marque
In this case sexec is a form parameter, if something is passed then I need to include it in the where statement, if it isn't I need to include something else. I am using CASE because there are three of these parameters and I want it to stop evaluating as soon as it matches as more than one may match but I only want to apply one.
Many thanks
Karen
View 3 Replies
View Related
Aug 14, 2001
Hi,
I'm trying to inner join an update statement.
Something like this:
update #point_connection_temp AS a inner join #point_connection_temp_two as b on a.key_fld = b.key_fld set a.next_point = b.next_point
where #point_connection_temp.next_point is null
order by a.key_fld
I'm getting an error message:Incorrect syntax error near AS
Any help will be greatly appreciated.Thank you!!!!!!!!!1
View 1 Replies
View Related
