|
Sorry, no idea - you didn't say you were using Oracle. I expected SQL Server like 99% of the questions.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
declare @val as varchar (255)<br />
set @val='ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well.'<br />
<br />
select charindex('ramu was a good boy',@val)
result:
1 = string exists
0 = string doesn't exists
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
In Oracle use INSTR (or it's variants):
WHERE 0 < INSTR('ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well',
'ramu was a good boy')
Mika
|
|
|
|
|
we know that when we insert data,update data and delete data against any table then that record is stored in transaction log of sql server. so how can i see those data in a very friendly way. please help with sample sql code.
tbhattacharjee
|
|
|
|
|
As far as I know, there is no way through SQL to read the transaction log of any database. You might find some tools out there to read the transaction log, but you would need to be very careful that you don't interfere with the running database.
Why don't you create an audit table that would hold these before and after values of the data ? You can implement this logic by creating triggers that would write to this table during an insert or delete. This is very standard practice when developing a system that requires auditing functionality.
|
|
|
|
|
First, apologies for hijacking this thread...
What kind of table layout do you use for the Audit tables? Right now, we have some old legacy apps that are being rewritten and we are going to be adding auditing to them via triggers. Is there a good / standard practice format for the tables?
|
|
|
|
|
The format I've seen before is typically an exact copy of the original table being audited along with some additional info such as datatime, userID and transaction code (Insert,Update,Delete). We used to name these tables something like EMPLOYEE_AUDIT. By keeping the format close to the original table format, it made it easier to implement.
Take into consideration the size of these audit tables !
I have seen audit tables grow at 500MB per month, which can cause serious performance drain on your DB server. Google around for other ideas on how to implement auditing in your new system.
|
|
|
|
|
hi,
I m trying to get values from three tables.
1) its has the sale id and product id
2) it has the product details based on id from 1st table,
3) it has the quantity of product being selected by the query.
My problem is I need to check the count of the product based on various sizes and if its more than 0 i need to display available else sold out.
I tried the following query.
if I remove the count it works well to get the details but If I m gonna get the count it throws error.
any suggestion where this query is wrong??
SELECT SaleProduct_tb.ProductId, Product_tb.BasePrice, Product_tb.SalePrice, Product_tb.PName, Product_tb.Image1, COUNT(ProductSizeQty.Quantity)
AS Expr1
FROM SaleProduct_tb INNER JOIN
Product_tb ON SaleProduct_tb.ProductId = Product_tb.ProductId INNER JOIN
ProductSizeQty ON Product_tb.ProductId = ProductSizeQty.ProductId
WHERE (SaleProduct_tb.SaleId = @SaleId) AND (SaleProduct_tb.CatId = @CatId) AND (SaleProduct_tb.SubCatId = @SubCatId) AND
(Product_tb.BrandId = @BrandId) ORDER BY saleProduct_tb.ProductId
Vijay V.
Yash Softech
|
|
|
|
|
Don't cross post, its rude.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
to be fair the guy was told to post here after posting in the asp.net forum.
|
|
|
|
|
someone suggested me to put that here. So I added here too.
sorry for that.
Vijay V.
Yash Softech
|
|
|
|
|
add a GROUP BY clause (after the WHERE clause adn before the ORDER BY one) listing all the fields in the SELECT clause excpet the one you're COUNTing
|
|
|
|
|
hi,
thanks for the help.
Vijay V.
Yash Softech
|
|
|
|
|
Hi
I hav got a stored procedure inside which im fetrching data from four tables..I want to genrate an excel file Populated with these results in the Stored procedure itself
Is it Possible? If yes then how can i do that?
Thnx and Regards
Joe
|
|
|
|
|
No, it is not...
You need to do this at the application level.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
You can write a DTS (or SSIS if using SQL Server 2005 or above) to run the stored procedure and export the results to an Excel file
====================================
Transvestites - Roberts in Disguise!
====================================
|
|
|
|
|
How about starting from Excel and pulling the data from the database into Excel ?
In Excel, go to the Data menu, Import External Data and use New Database Query to run the necessary query in your database.
Just a thought.
|
|
|
|
|
Hi,
I need to have a column value from multiple rows as one value
e.g. select name from employee
returns> name
-----
david
malcom
rave
I need to have this value as
name
----
david, malcom,rave (in one row)
Please note that I am on sql server 2000
Can anyone help in generating such sql script.
regards
Vijay
|
|
|
|
|
Sounds like another homework question, but here you are:
DECLARE @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + s.Name
FROM MyTable s
SELECT @List
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Bob,
My query is in my asp.net code is as (which is not a stored procedure i am implementing it in my vb code.
select assets.asset_pk as UNIQUEID,
assets.asset_ID ,
assets.[name] collate database_default ,
asset_types.ASSET_TYPE_NAME ,
(CASE when IsNull(townland.townland, '') = '' THEN '' eLSE townland.townland END) ,
(CASE when IsNull(thoroughfares_fme1.thorfare_name, '') = '' THEN '' eLSE thoroughfares_fme1.thorfare_name END) ,
(CASE assets.LEASED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
(CASE assets.DISPOSED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
(CASE assets.ASSET_REGISTERED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
assets.ASSET_FOLIO_NUMBER ,
assets.FLAGGED_NOTE collate database_default +'<br>' AS DETAILS
from assets
INNER JOIN asset_types on assets.type_id = asset_types.asset_type_id
left join asset_streets on assets.asset_pk =asset_streets.asset_ref_key
left join thoroughfares_fme1 on asset_streets.THORFARE_REF_KEY = thoroughfares_fme1.primaryindex
left join asset_townlands on assets.asset_pk=asset_townlands.asset_ref_key
left join TOWNLAND on asset_townlands.TOWNLAND_KEY = TOWNLAND.primaryindex
where
assets.asset_ID like '%L%' or
assets.[name] collate database_default like '%L%' or
asset_types.ASSET_TYPE_NAME like '%L%' or
townland.townland like '%L%' or
thoroughfares_fme1.thorfare_name like '%L%' or
assets.leased_to like '%L%' or
assets.LEASED_CUSTOMER_ID like '%L%' or
assets.LEASED_OCCUPIER like '%L%' or
assets.DISPOSED_TO_NAME like '%L%' or
assets.ASSET_FOLIO_NUMBER like '%L%' or
assets.FLAGGED_NOTE collate database_default like '%L%'
order by assets.asset_ID
here because of thoroughfares_fme1.thorfare_name field I am getting rows for the asset_id
e.g
62 BLD10001 ffa-234 LA BUILDING RATHQUARTER HOLBORN STREET
62 BLD10001 ffa-234 LA BUILDING RATHQUARTER CONNAUGHTON ROAD
I want this records as one record
e.g.
62 BLD10001 ffa-234 LA BUILDING RATHQUARTER HOLBORN STREET, CONNAUGHTON ROAD
Now my datagrid is binded to thi record set.
When I tried to have your list concept in my code I get the error as it can be implemented in other data retrieval fields.
Now please tell me how I can proceed from here?
regards
|
|
|
|
|
Well, for a start this is a totally different question to your original one. My reply would work for what you asked in the first place, but this is a different problem.
I can only assume your join for asset_streets.THORFARE_REF_KEY = thoroughfares_fme1.primaryindex returns multiple rows, which kind of goes against the name of primaryindex.
Without reviewing your data (and no, I am not interested in seeing it) I can only suggest you look at introducing additional outer joins.
hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
yes there are mulltiple thorfare entries for the asset
so it produces my record set as
e.g.
1 abc thorfare1
1 abc thorfare2
where as I just want it as
1 abc thorfare1,thorfare2
regards
Vijay
|
|
|
|
|
Try creating a function to string all the thorfare entries together using the same technique as I showed in my original reply, then your select bceomes something like
select asset_name, dbo.fn_thorfar(assetid), ....
If you are not sure about functions try BOL, there are some good examples.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I'm not familar with SQL, I get around with the basics which usually works fine for me.
However, I've come up with a situation where the code will no not run from a SQLDatasource object in VS.
I have, however gotten it to run properly from SSMSE so I want to write it as a stored procedure. But I really have no idea how to even begin. I'm sure easy to do, I just have never done it before.
Here is the SQL that I got to run properly.
SELECT Members.dbo.Members.LoginID, dbo.Categories.CategoryName, dbo.Listings.ListingID, dbo.Listings.Title, dbo.Listings.HasPhoto,
dbo.Listings.IsTaken, dbo.Listings.IsPickedUp, dbo.Listings.TakenDate, GetTakenUsername.LoginID AS TakenByUsername, dbo.Listings.PickedUp,
dbo.Listings.TimesViewed
FROM dbo.Listings INNER JOIN
dbo.Categories ON dbo.Listings.CategoryID = dbo.Categories.CategoryID INNER JOIN
Members.dbo.Members ON dbo.Listings.MemberID = Members.dbo.Members.MemberID INNER JOIN
Members.dbo.Members AS GetTakenUsername ON dbo.Listings.TakenBy = GetTakenUsername.MemberID
WHERE (dbo.Listings.MemberID = @MemberID)
Any help with this would be greatly appreciated.
modified on Tuesday, August 26, 2008 8:19 PM
|
|
|
|
|
you are right, it is very simple. Read BOL.
CREATE PROC MyProc<br />
--Declare <br />
@MemberID INT<br />
<br />
As<br />
<br />
SELECT Members.dbo.Members.LoginID, dbo.Categories.CategoryName, dbo.Listings.ListingID, dbo.Listings.Title, dbo.Listings.HasPhoto,<br />
dbo.Listings.IsTaken, dbo.Listings.IsPickedUp, dbo.Listings.TakenDate, GetTakenUsername.LoginID AS TakenByUsername, dbo.Listings.PickedUp,<br />
dbo.Listings.TimesViewed<br />
FROM dbo.Listings <br />
INNER JOIN dbo.Categories ON dbo.Listings.CategoryID = dbo.Categories.CategoryID <br />
INNER JOIN Members.dbo.Members ON dbo.Listings.MemberID = Members.dbo.Members.MemberID <br />
INNER JOIN Members.dbo.Members AS GetTakenUsername ON dbo.Listings.TakenBy = GetTakenUsername.MemberID<br />
WHERE (dbo.Listings.MemberID = @MemberID)
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
