|
For some reason, my query causes a data type mismatch error due to where clause #5 below. I am not sure how this is possible given clause #2 & 3.
Where clauses
1) (NOT ID_BB_SEC_NUM_DES IS NULL AND TYPENAME(ID_BB_SEC_NUM_DES)='String')
2) AND (LEN(ID_BB_SEC_NUM_DES) BETWEEN 9 AND 13)
3) AND ( RIGHT(ID_BB_SEC_NUM_DES, 1) IN ('W', 'M', 'Y') )
4) AND ( LEFT( RIGHT( RIGHT( ID_BB_SEC_NUM_DES, LEN( ID_BB_SEC_NUM_DES )-6 ), 3), 1) IN ('B','R','V') OR LEFT( RIGHT( RIGHT( ID_BB_SEC_NUM_DES, LEN( ID_BB_SEC_NUM_DES )-6 ), 4), 1) IN ('B','R','V') )
5) AND ( INSTR( RIGHT(ID_BB_SEC_NUM_DES, LEN(ID_BB_SEC_NUM_DES)-6), 'O')=0)
|
|
|
|
|
I'm not familiar with INSTR, and googling SQL instr function told me its semantics depend on the database one uses. I guess that is where the problem originates.
|
|
|
|
|
I am querying a csv file using ADO .NET in C++, which uses the JET provider. I can select the Instr statement, but cannot apply the where statement for some reason.
|
|
|
|
|
AFAIK Jet providers target MS Access, and this[^] shows a different INSTR syntax than what you are using.
|
|
|
|
|
I took a look at the page and it looks like there are multiple correct syntaxes. I am using the syntax like Instr ("Tech on the Net", "the"), except that I am using single quotes instead of double quotes.
Thanks, that's a helpful sight. I now know that MID can be used instead of SUBSTR, which I was replicating with left and right.
|
|
|
|
|
That is one of the ugliest schema I have ever seen, reminds me of SAS.
Anyway the error in (5) is due to comparing 'O' to 0, you are trying to compare a string to a numeric, one of them is wrong!
5) AND ( INSTR( RIGHT(ID_BB_SEC_NUM_DES, LEN(ID_BB_SEC_NUM_DES)-6), 'O')=0)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm trying to exclude ID's where the letter 'O' appears after the 6th character in the ID.
Instr returns an integer indicating the base one position where the character is found. I think it returns 0 if the character is not found.
It's a bloomberg schema btw
I realized that I can however directly tell INSTR to start at position 7 and look for the character 'O', so I will try doing that and see if I still have the same problem.
After I get the results of my query, I create a pivot table using ADO .NET. Then I will try to figure out how to do a custom sort of the columns. Easy for me in C or C++ STL, but I am not used to doing it in .NET
modified on Monday, March 29, 2010 1:44 AM
|
|
|
|
|
Ted2102 wrote: It's a bloomberg schema btw
Yah that would be right, stupid 1980's design.
What database use your using btw, I found INSTR is not a SQL function so I can't follow through on the error.
I think you are going about the process completely wrong, you are treating a csv file as a table in a database, trying to query and then bitching when the query does not work.
I would import the data into the database, even if you just query it and chuck it away you will not then be using the jet crap to do real work and you can use SQL properly. Don't try and clean up the data format, just load it all into varchar fields.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I agree that it would be easier to load the instruments in a database and have used Oracle and Sql Server in the past.
I am currently telecommuting over a thousand miles away, so database access is a little slow for me except for whatever portion I install locally in SQL Server Express. I am mainly doing this as an exercise to learn to use ADO .NET on live datasets that I get externally.
|
|
|
|
|
Ted2102 wrote: I am mainly doing this as an exercise to learn to use ADO .NET
In which case that explains why you are in areas no sane person dares to go (on a business process), using ado.net against csv files.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes, it appears buggy. The code works when I replace RIGHT( ID, LEN( ID )-6 ) with MID(ID, 7). Thanks for your help. I may install a local database after I figure out what I can store locally and maybe get desktop with a bigger hard drive.
|
|
|
|
|
I use SQL Express with no problem and it does most jobs. I tried to install the developer version but it was not an option with the download I was using so I stuck with Express
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi all,
i have two tables with data.like this
Table 1 Table 2
Name Age Sex Adress Phone no city
Basha 24 m 1 st street 2124 india
Peter 25 m 2nd street 24589 USAout put should be like this:
m1 m2 m3
basha 24 m
1 st street 2124 india
peter 25 m
2 nd street 24589 USA
How can i get the out like this what can i do...
Thanks & Regards,
S.Inayat Basha
modified on Saturday, March 27, 2010 4:11 PM
|
|
|
|
|
I am not sure this can easily be done on the server side.
I may be inclined to do the process on the client:
SELECT Name, Age, Sex, Address, Phone, City
FROM Person p
JOIN Address a
ON a.ID = p.ID
ORDER BY p.Name
Dim count as Integer = 0
Dim arr(( (dt.Rows.Count) * 2) - 1), 2) As String
For Each dr as DataRow In dt
arr( count, 0) = dr("Name").ToString
arr( count, 1) = dr("Age").ToString
arr( count, 2) = dr("Sex").ToString
count += 1
arr( count, 0) = dr("Address").ToString
arr( count, 1) = dr("Phone").ToString
arr( count, 2) = dr("City").ToString
count += 1
Next
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Why, the only reason I can think of for this is to service a presentation requirement, that is a report, output or a form requirement. The relevant word is PRESENTATION, do this type of formatting at the correct place, not in the database, as has been suggested.
This is not a suggestion it is a general rule, a database is for the data, not pissing about with formatting and layout.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm sending data to a closed, vendor application using views. We have one field (role) that represents the role the user plays. The vendor application exposes three of these roles but only as booleans. This is beyond my SQL skill level....how to I take this one field and return three boolean fields out of it?
Thanks,
Michael
|
|
|
|
|
create view view_name as <br />
select field_name as f1, field_name as f2, field_name as f3 from table_name
this will select a column a 3 duplicated columns
Die Energie der Welt ist konstant. Die Entropie der Welt strebt einem Maximum zu.
|
|
|
|
|
As Dmitry suggested, select the column 3 times. You can also incorporate a case statement to send a 1 or 0 as the value.
SELECT
CASE WHEN FieldName = 1 THEN 1 ELSE 0 END F1,
CASE WHEN FieldName = 2 THEN 1 ELSE 0 END F2,
CASE WHEN FieldName = 3 THEN 1 ELSE 0 END F3
FROM TableName
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Looks like your answer gives me the answer I need.
CASE WHEN Role = 1 Then 1 else 0 end IsAdministrator,
CASE WHEN Role = 2 Then 1 else 0 end IsDecisionMaker,
etc.
Thanks,
Michael
|
|
|
|
|
I now have to add one additional field but this one is harder. Each contact has an AccessGranted table tied to them with the locations they can access. I need to reduce that table down to a boolean CanAccess. How do I do something like that when I don't really have a customer key to work with. This is what my view looks like right now:
SELECT TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode,
dbo.ContactMaster.BmcCustKey, CASE WHEN dbo.ContactMaster.Role = 1 THEN 1 ELSE 0 END AS IsAdministrator,
CASE WHEN dbo.ContactMaster.Role = 6 THEN 1 ELSE 0 END AS IsDecisionMaker,
CASE WHEN dbo.ContactMaster.Role = 5 THEN 1 ELSE 0 END AS IsAccountingAP
FROM dbo.ContactMaster INNER JOIN
dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity LEFT OUTER JOIN
dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity LEFT OUTER JOIN
dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity LEFT OUTER JOIN
dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey
|
|
|
|
|
I'm on a WORKGROUP LAN at home, and I want to connect to an instance of SQL Server 2005 Express on one of the other boxes on the network. I have already enabled remote connections on that instance of SQL Server and restarted the service.
When I try to connect, it tells me this:
TITLE: Connect to Server<br />
------------------------------<br />
Cannot connect to HTPC.<br />
------------------------------<br />
ADDITIONAL INFORMATION:<br />
<br />
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)<br />
<br />
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
modified on Saturday, March 27, 2010 11:52 AM
|
|
|
|
|
Have you also enabled Named Pipes through the Configuration Manager?
|
|
|
|
|
Yeah.
Could it be a user account problem?
When I try to connect (using Windows Auth), it uses my local box's user account.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Have you got Mixed Authentication set in the Sql Server Express instance? If so, can you connect using non-windows authentication?
I noticed that the message said that it can't find instance HTPC; Is that the name of the instance or is it HTPC\SQLEXPRESS?
|
|
|
|
|
i.j.russell wrote: Have you got Mixed Authentication set in the Sql Server Express instance? If so, can you connect using non-windows authentication?
No, but do I want to use non-windows authentication?
i.j.russell wrote: I noticed that the message said that it can't find instance HTPC; Is that the name of the instance or is it HTPC\SQLEXPRESS?
HTPC is the machine name. The server is HTPC\SQLEXPRESS.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
