Is it possible to do something like this:
Select a.address from person p
join Addresses a on p.AddressPointer =
Case when @UseNewAddress=1 then a.NewAddress else a.OldAddress end
It gives a syntax error but I'm wondering if there's another way to do it.
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)
INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Any suggestions?
Awesome! I don't alway get the email notification of whoever reply to the posting. I think it only work after I log off of the forum.
Scott
There is a table called "tblvZipCodes" that contain a zipcode of all cities, area code that are located in that zip code.
The problem I have with the inner join is that there are more than 1 cities in one zipcode code. Is there a way to just return only the 1st row and not return the rest of the rows from the tblvZipCodes in the INNER JOIN query?
Thanks..
Code:
SELECT TOP 100 PERCENT dbo.tblPurchaseRaw.Year, dbo.tblPurchaseRaw.Make, dbo.tblPurchaseRaw.Model, dbo.tblPurchaseRaw.ModelType,
dbo.tblPurchaseRaw.Color, dbo.tblvZipCodes.ZIPCode, dbo.tblvZipCodes.City, dbo.tblvZipCodes.County, dbo.tblvZipCodes.State,
dbo.tblvZipCodes.AreaCode, dbo.tblvZipCodes.Region, dbo.tblaAccounts.Name, dbo.tblaAccounts.PhoneOne, dbo.tblaAccounts.AccountID,
dbo.tblPurchaseRaw.AcceptedID, dbo.tblPurchaseRaw.Series, dbo.tblPurchaseRaw.BodyStyle, dbo.tblaAccounts.WebSite,
dbo.tblaAccounts.SalesEmail, dbo.tblPurchaseRaw.EmailTo, dbo.tblPurchaseRaw.PhotoURL, dbo.tblPurchaseRaw.Mileage,
dbo.tblPurchaseRaw.RawID, dbo.tblvRegions.Name AS RegionName, dbo.tblPurchaseRaw.VIN, dbo.tblPurchaseRaw.Style,
dbo.tblPurchaseRaw.StockDate
FROM dbo.tblPurchaseRaw INNER JOIN
dbo.tblaAccounts ON dbo.tblPurchaseRaw.AccountID = dbo.tblaAccounts.AccountID INNER JOIN
dbo.tblvZipCodes ON dbo.tblPurchaseRaw.ZipCode = dbo.tblvZipCodes.ZIPCode INNER JOIN
dbo.tblvRegions ON dbo.tblvZipCodes.Region = dbo.tblvRegions.RegionID
WHERE (CONVERT(char, dbo.tblPurchaseRaw.StockDate, 101) <> '01/01/1900') AND (dbo.tblPurchaseRaw.SoldRawID IS NULL) AND
(dbo.tblPurchaseRaw.AcceptedID <> - 10) AND (dbo.tblPurchaseRaw.AcceptedID <> - 1)
ORDER BY dbo.tblvZipCodes.ZIPCode
begin
if @counter=2
update t_import_main set program2=@prog, g2=@gno,yr2=@yr, Program_code2=@pcode where uno=@oldu
case
when @pcode is null then
Program_code2=@gno
End
I get this error message
Incorrect syntax near the keyword 'case'.
What am i doing wrong??
Hello,
I am trying to do something like this:
select (CASE
WHEN tableA.col = 'a' then 'A'
ELSE (select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id )END ) as TEST
from tableA
But the problem is that the part in bold returns more than one row..
i want it to be select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id
for every value of tableA.col.
How do I do this?
Hi
I've been trying to put a simple case statement into my 'where' clause but having no luck, is there another way to do the following?
DECLARE @searchCriteria Int
SET @searchCriteria = 2
SELECT column1, column2 FROM TABLE WHERE
CASE @searchCriteria
WHEN 1 THEN (column3 = 1000100)
WHEN 2 THEN (column3 = 1000101)
END CASE
...cheers
table a
id group value
1 g1 2
2 g1 3
3 g2 4
4 g2 5
5 g2 10
table b
gourp name
g1 n1
g1 n2
g2 n4
g2 n5
I want ret table look like
1 g1 2 n1
1 g1 2 n2
2 g1 3 n1
2 g1 3 n2
3 g2 4 n4
3 g2 4 n5
4 g2 5 n4
4 g2 5 n5
5 g2 6 n4
5 g2 6 n4
How can I fulfill this task, thanks.
Jeff
IN SP i have the
AND EI.IID =TL.IID AND EI.GID=TL.GID
and TL.QID=EI.ID WHERE EI.TYPE='P'
but i need and TL.QID=EI.ID only when
@QID>0
if not > 0 the i dont need to add and TL.QID=EI.ID
i tried below
All - I'm having some trouble, and I hope someone can give me some assistance. I've scoured Google, and have only found stuff similar to what I already have.
My company has an old commercial ERP package. The package has a table called 'Lot_Bin_Tran' that tracks movements of parts based on the part number and a 'Tran_code' - a one-character field that represents what kind of transaction it is (receipt, shipment, inventory adjustment, etc...). Using those two pieces of information, you can join this single table to any of the tables that hold the information (such as receipts). I tried to write a left join from my transaction table to my primary tables to look for transaction records that have no corresponding records in the primary tables. Some SQL might be helpful here...
Code:
SELECTLBT.*
FROMLOT_BIN_TRANLBT(NOLOCK),
PRODUCEP(NOLOCK),
ORDERSO(NOLOCK),
ISSUESI(NOLOCK),
RECEIPTSR(NOLOCK),
LOT_BIN_XFERLBX(NOLOCK)
WHERELBT.PART_NO = 'OUR_PART_NUMBER_GOES_HERE'
AND
LBT.TRAN_NO *=
CASE LBT.TRAN_CODE
WHEN 'I' THEN I.issue_no
WHEN 'P' THEN P.prod_no
WHEN 'R' THEN R.receipt_no
WHEN 'S' THEN O.order_no
WHEN 'T' THEN LBX.tran_NO
END
AND
((I.ISSUE_NO IS NULL) AND (P.PROD_NO IS NULL) AND (R.RECEIPT_NO IS NULL) AND (O.ORDER_NO IS NULL) AND (LBX.TRAN_NO IS NULL))
when I run this, though - I get the following message...
Quote:
Msg 301, Level 16, State 1
Query contains an illegal outer-join request.
If I change the Left join to an inner join (using = instead of *=), I get a resultset, but not the one I want (since I can't detect nulls in all tables using inner join). Any ideas on how I can restructure?
Thanks!
Is it possible to have a join with case statement in it?
i.e.
select * from a inner join b on
case [x] then a.xid = b.xid
case [y] then a.yid = b.yid?
Hi, I have two tables TABLE_A and TABLE_BTABLE_A has rows like this:PROJECT_IDTASK_ID TASK_NAME1 100 One Hundred1 110 One Hundred Ten1 120 One Hundred Twenty2 200 Two Hundred3 300 Three Hundred3 310 Three Hundred TenTABLE_B has rows like this:PROJECT_IDTASK_IDAMOUNT1 10010001 11011002 NULL20003 3003000I want to inner join TABLE_A and TABLE_B such that if TASK_ID is available in TABLE_B, then join should happen on TASK_ID (on TABLE_A.TASK_ID=TABLE_B.TASK_ID), if TASK_ID is not available the join should happen on PROJECT_ID.For example for PROJECT_ID=2, there is no TASK_ID in TABLE_B (in this situation the join should be ON PROJECT_ID)How can we do a CASE like situation here?Thanks in advanceqA
View 14 Replies View RelatedHi,
I have a 2 different telephone number tables,
Table 1 has some numbers beginning with '0' i.e. 08001234567 and some without the '0' i.e 8001234567
on my Table2 i only have numbers starting with '0' i.e 08001234567.
I would like to make a INNER JOIN statement and check if the telephone number dont have a starting '0', then append to it and try to do the join so I get both set of data.
for example (this doesnt work however...):
SELECT *
FROM dbo.Calls INNER JOIN
ON
CASE WHEN SUBSTRING(dbo.CallData.TelephoneNumber, 0, 1) = '0'
THEN dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber
ELSE '0' & dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber <--append a 0 at the start
END CASE
AND dbo.Products.FK_Client = dbo.CallDataSets.FK_Client
GROUP BY dbo.CallDataSets.FK_Client
I hope you understand what I am trying to achieve here...
Any help is appreciated!
Many thanks,
Jon
Hi All,
I am in need for to write the following query for a stored procedure in SQL Server 2000. Please observe the T-SQL code first.
Please disregard the numbers and Product Numbers, they are not the correct data. I used them for ease of understanding. But the query is identical.
Code Block
SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
CASE WHEN @vchSubscription = 'Weekly' THEN
INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' ) -- Category and SubCategory
OR PM.chProductNumber IN (
'weekly1', 'Weekly2', 'Weekly3', 'Weekly4' )
)
AND C.dtInsertDate > = @dtIssueDate CASE WHEN @vchSubscription = 'Monthly' THEN
INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' )
OR PM.chProductNumber IN (
'Jan', 'Feb', 'Mar', ....'Dec')
)
AND C.dtInsertDate > = @dtIssueDate
END
GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8
my requirement is join the same table, but depending on the subscription type i have to join to different product numbers.
I hope you understand. I have been trying this since yesterday, but no luck.
Any help would be greatly appreciated.
I am trying to use this logic into a query:
Select P.S,E.S,E.R
from Pack P(nolock)
join Exp E on P.Id=E.O
on E.R is null
case when E.R is not null then ''
else ''
end
where P.s='PLT000044'
I have to query two conditions joining the tables. when E.R is NULL and when E.R is not null. but the value is coming from the join between the 2 tables :P and E.
I'm trying to join a table and based on the value of a given column I would join using the column in question, however if the column is NULL then I want to make the join without the column in the join. so I think I want to do something like this:
Case E.a
when NULL
then LEFT JOIN EPD ON EPD.b = D.b
AND EPD.SD = (SELECT MAX(E1.SD) FROM E1
WHERE E1.b = EPD.b AND E1.a = EPD.a AND E1.SD <= T.WD)
Else
LEFT JOIN EPD
ON EPD.a = D.a
and EPD.b = E.b
AND EPD.SD = (SELECT MAX(E1.SD) FROM E1
WHERE E1.a = EPD.a AND E1.b = EPD.b AND
E1.SD <= T.WD)
end
however T-Sql does not seem to like my Case statement mixed into my From/join clauses.
Thanks,
Mark E. Johnson
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
I'm trying to generate the data for a 2-column table, where both columns are defined as NOT NULL and the second column is a uniqueidentifier.
In SQL Server Management Studio, this works fine:insert into table_3(column_a, column_b)
select table_1.column_a, (case when table_2.column_b is NULL then newid() else table_2.column_b end) as column_b
from table_1 left outer join table_2 on table_1.column_c = table_2.column_c
That is, column_b of the SELECT result has no NULL values, and all 35,986 rows are successfully inserted into a previously empty table_3. (If I comment out the INSERT INTO clause and project table_2.column_b instead of "(case ... end) as column_b", the SELECT result includes 380 rows with a NULL in column_b, so I know the case expression plus the outer join are working as expected.)
But when I use the SELECT query as the SQL command in an OLE DB Source component that is connected directly to the OLE DB Destination for the result table, I get this error:There was an error with input column "column_b" (445) on input "OLE DB Destination Input" (420
The column status returned was: "The value violated the integrity constraints for the column.".
And sure enough, when I modify the result table to allow NULL in column_b, truncate it, and re-run the data flow, it inserts the exact same 380 rows with a NULL in column_b among the 35,986 rows.
So what is SSIS doing to screw up the results of the SELECT command???
An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.
tblErrors_ER
---------------
CREATE TABLE tblErrors_ER
{
ErrorID int,
ErrorType varchar(255),
DateLogged datetime,
[code]...
I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.
If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:
ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 Multiple
2 Test 08/08/2012 Multiple
this would suggest the original syntax is close to being accurate but I can not get it to work.
I have data that I want at multiple granularities, 5,15,30 and 60 minutes. To reduce repetition, I have put them all in the same table, so that there is a column for 5,15,30 and 60 minutes, with a filtered index on each of the columns that removes the nulls. This means that each day will have 288 slots, but only 24 of the slots are filled in for 60 min data, and all of them are filled for 5 minute data.
I have another column that specifies the interval granularity, and my first thought was to access my data through a join, where I can use a CASE statement, and depending on the data granularity necessary, it will look at a different column:
INNER JOIN Data d ON
AND d.settlement_key =
CASE st.interval_granularity
WHEN 5 THEN [5_min_settlement_key]
WHEN 15 THEN [15_min_settlement_key]
WHEN 60 THEN [60_min_settlement_key]
ELSE NULL END
Despite the presence of the indexes on the columns, then the process seems to be quite slow, I think probably due to the fact that any query plan isn't going to know beforehand which of the columns it is going to use for any given dataset, until it actually starts to run, so it may not be optimised.
How I could optimise this based on the given structure? Maybe there are hints to be added to the join, or maybe I can clear the query plan each time the SQL is run? My other option for dealing with the data of different granularity was to use one column and repeat the data multiple times, each at the different granularity, but this makes my data, row and table sizes much higher, as we are adding just a column for each additional granularity. Would this work any better in future versions of SQL server, maybe with column store indexes?
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies View RelatedHello:
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Any suggestions?
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
View 4 Replies View RelatedI am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Jacob
I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?
View 7 Replies View RelatedI am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
View 2 Replies View RelatedI have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
Thanks,
Jason
I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
View 2 Replies View Related