[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusiness.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommissionReceived]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBusinessCommissionReceived] AS Expr2
, IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS Expr3
, IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID = dbo_Lead.ID) LEFT JOIN dbo_Mortgage ON dbo_Personal.ID = dbo_Mortgage.ID) LEFT JOIN dbo_OtherBusiness ON dbo_Personal.ID = dbo_OtherBusiness.ID) LEFT JOIN dbo_BuildingsAndContents ON dbo_Personal.ID = dbo_BuildingsAndContents.ID) LEFT JOIN dbo_Commissions ON dbo_Personal.ID = dbo_Commissions.ID) LEFT JOIN dbo_Life ON dbo_Personal.ID = dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
I have an application that uses Acces as a backend and VBA as front end. Application is secured and is supplied on a CD with setup.exe.
Can I use VB 2005 and MS SQL to achieve the same?
Would I be able to package my application with all the neccessery files (assuming that client does not have any e.g. SQL server) so that multiple front ends can access data from common source?
Would I be able to secure such an application using only VS 2005?
What would I need to quickly learn how to achieve the above ( any books you can suggest maybe)?
Currently, we are facing a serious problem of conversion from Sql Query to Semantic Query and we are looking for any API or Component that handles this conversion process.
Requirement Details:
I'd like to tell the story happened which lead us to this requirement. We want to convert Shazam Reports (SRW) to Reporting Services (RDL) reports that support Report Builder (semantic queries). So, we planned to convert the SRW query structure into a SQL query structure and later to Semantic Query, which is recognized by the Report Builder.
In that process, we've successfully developed a parser component that parses the SRW's query structure into standard Sql query structure but now we struggling, as we don't know how to convert an sql query into semantic query.
We are running out of time and any information towards the solution is a great help to us.
I have this query: SELECT Trim(Left([Display Name],InStr([Display Name],",")-1))+Trim(Mid([Display Name],InStr([Display Name],",")+2,1)) AS UserName, dbo_Employee.[Display Name] FROM dbo_Employee; Works good in access, but when trying to put it un sql server, it comes up with errors. I know mid is substring, I just don't know what trim and instr are in mssql.
Will it be possible to convert a varchar data type column (having values that are numbers or null only) to a numeric data type column having the number values and the null values not being replaced by 0.
Col A Col A (varchar) (int or numeric) 123 123 124 124 NULL 0 <expected blank> 125 125
I tried using CAST(Col A as int) and it converts the NULL to 0. Will CAST(ISNULL(ColA,'') as int) work? The reason for this requirement is the value 0 will give different meaning to the data. Any insights will be appreciated.
Having problem completing this query. I have a list of items. Some items need to be grouped by a list, some by a range. I was thinking of useing two tables, one for the items, and one for the groups. The groups would have something like groupid, title, listnumbers, rangelow, and rangehigh. The tables are in sql database. For example: The list of items numbers are 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20. In the group table, it would be listed like this: G1, Group1, 1,, G1, Group1, 6,, G2, Group2, ,2,5 G3, Group3,6,18,20 G3, Group3,7,15,17 G3, Group3,8,14,16 G3, Group3,9,11,13
In this example, Group1 is a list, group2 is a range, and group3 is a list of ranges. I can make a query that pulls all the items just in the groups: SELECT ECC_ITEMS.NBR, Group.Group_Name, Group.Title, ECC_ITEMS.DESCR, ECC_ITEMS.REG_PRC FROM Group, ECC_ITEMS WHERE ECC_ITEMS.NBR Between Group.RangeLow And Group.RangeHigh Or ECC_ITEMS.NBR=Group.GroupItems Now, I am not sure how to put the rest of the items (the ones that aren't in a group) in that query. I was thinking on making a union and the second query being a unmatched query. Not sure how to make it were that query is "unmatched" with a table in the same query. And ideas on how to make the second part of the union query?
Hi, I've created a SQL union query that grabs the top three values of a test and puts the results together on one line item by a recordnumber. The only problem is, one value is generating the error: " Syntax error converting the varchar value '=' to a column of data type int." This field is an operator field, so it will only display operator values ( <, >, = ). Everything seems to work BUT the union join on that column, which is a varchar data type. I need this union to work, and Im frustrated that I can't seem to figure out why I can't get the logic to work. Can someone take a look at this and help me with it? The union query looks like this:
exec('select RecordNumber,Sum(rank1) as HbA1CRank1,max(Operator1) as HbA1COperator1,sum(contentValue1) as HbA1CContentvalue1,max(dos1) as HbA1CDOS1,Sum(rank2) as HbA1CRank2,max(Operator2) as HbA1COperator2,sum(contentValue2) as HbA1CContentvalue2,max(dos2) as HbA1CDOS2,Sum(rank3) as HbA1CRank3,max(Operator3) as HbA1COperator3,sum(contentValue3) as HbA1CContentvalue3,max(dos3) as HbA1CDOS3 from (SELECT DISTINCT TOP 100 PERCENT recordnumber, Rank AS rank1, cast(Operator as varchar) as Operator1, contentValue AS contentvalue1, DOS AS DOS1, 0 AS rank2, 0 as Operator2, 0 AS contentvalue2, 0 AS DOS2, 0 AS rank3, 0 as Operator3, 0 AS contentvalue3, 0 AS DOS3FROM (SELECT (SELECT COUNT(*) FROM tblDiabetic_HgbA1C_Total vw1 WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, * FROM tblDiabetic_HgbA1C_Total vw2) vw3WHERE (Rank = 0)ORDER BY RecordNumber UNION SELECT DISTINCT TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, Rank AS rank2, cast(Operator as varchar) as Operator2, contentValue AS contentvalue2, DOS AS DOS2, 0 AS rank3, 0 as operator3, 0 AS contentvalue3, 0 AS DOS3FROM (SELECT (SELECT COUNT(*) FROM tblDiabetic_HgbA1C_Total vw1 WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, * FROM tblDiabetic_HgbA1C_Total vw2) vw3WHERE (Rank = 1)ORDER BY RecordNumber UNION SELECT DISTINCT TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, 0 AS rank2, 0 as operator2, 0 AS contentvalue2, 0 AS DOS2, rank AS rank3, cast(Operator as varchar) as operator3, contentvalue AS contentvalue3, DOS AS DOS3FROM (SELECT (SELECT COUNT(*) FROM tblDiabetic_HgbA1C_Total vw1 WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, * FROM tblDiabetic_HgbA1C_Total vw2) vw3WHERE (Rank = 2)ORDER BY RecordNumber )tblHgA1C group by RecordNumber ')GO Can anyone help? It looks right to me, I just can't figure out why the error keeps coming up = Thank you!
I am trying to construct a query in Query Analyzer (with SQL Server 2000), but am getting an error regarding "implicit conversion."
Here is the query:
SELECT dbo.AUCTION.EbayNum, dbo.AUCTION.EndDate, DATENAME([month], dbo.AUCTION.EndDate) + ' ' + DATENAME([year], dbo.AUCTION.EndDate) AS [PmtMonth], dbo.LOT.Description, dbo.AUCTION.WinBid, PaidStat = CASE dbo.AUCTION.PaidStatus WHEN 0 THEN '' ELSE 'PAID' END, PaidAmt = CASE dbo.AUCTION.PaidStatus WHEN 0 THEN '' ELSE dbo.AUCTION.WinBid END FROM dbo.AUCTION INNER JOIN dbo.LOT ON dbo.AUCTION.LotNum = dbo.LOT.LotNum WHERE (LEN(dbo.AUCTION.Winner) > 0)
The error occurs in the PaidAmt CASE statment: "Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query."
Why is there an implicit conversion going on? And how can I fix it? :mad:
Hi Friends, Need ur help desperately. I am stuck with one of the queries which i had written in Oracle and need the same in SQL Server.Please have a look at the following query :
select * from r_tin_1099_info where instr(translate( nm_ctrl_cd , '~!@#$%^&*()_+}{":?><`-=]['''';/., ', '*******************************' ),'*') > 0;
Basically my purpose is to replace the values in column NM_CTRL_CD having wild card characters with '*' and then select this rows to display.
However i am not able to run the same query in SQL Server since TRANSLATE is not a built in func. I have tried a lot to replace it but could only one func : REPLACE . But the same will not replace any one of the above wild characters but will replace the entire pattern.Please note that it should be able replace even if one of the wild card characters are present in the string and not necessarily the entire pattern shown above.
please reply ASAP since i am working and need this query to fix a defect.
Trying to run a query to get total number of direct mail campaigns by company. Would like the totals to be (1) total number of direct mail campaigns (there are 2 types) (2) total number of direct mail campaigns type direct mail (3) total number of direct mail campaigns type publication
Below is the query I put together with INNER JOIN:
SELECT Agency.PrimaryAgencyName, COUNT(Distinct Campaigns.DirectMailID) AS TotalCampaigns, COUNT(Distinct DirectMail.Type) AS TotalDirectMail
FROM Agency INNER JOIN (Clients INNER JOIN (Campaigns INNER JOIN DirectMail ON DirectMail.DirectMailID = Campaigns.DirectMailID) ON Clients.ClientID = Campaigns.CampaignID) ON Agency.AgencyID = Clients.AgencyID
GROUP BY Agency.PrimaryAgencyName, DirectMail.Month, DirectMail.Type ORDER BY Agency.PrimaryAgencyName
When I run the query, I recent the below error:
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value '1683 1-15017' to data type int.
The value mentioned in the above error message is the ClientID.
Below are the tables for reference (bold for entity name and underline for PK) and datatypes
USE Billing INSERT INTO tblUser (userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('test','1','1','9','6') where userId = id from tbl_account
and
INSERT INTO tblUserInfo (userNumber,userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('123','test','1','1','9','6') where userNumber = userNumber and userId = userId from tblUser
Im new when it comes to making SQL queries, so i need help badly.
Basically what i need the query to do is call from a DB "User" id where the id is in Binary data. from that i need it to insert into 2 other tables in the Billing DB.
the UserId needs to be the same in all areas, and when it inserts into tblUserInfo it has to pull from userNumber from tblUser after the rows are entered so that the userNumber in tblUser and tblUserInfo are the same.
Hopefully you can help. if any further information is needed please just ask and ill try to give as much as i know.
i am familiar with php mySql combo, but from now on the boss wants me on MS SQL. i have a little query sender that used mySql and i tried to switch it over to use mssql_functions like: php Code:
When i run my script i get a blank screen. I am already bent out of shape trying to switch from mySql to this. any help would be appreciated. here is my code...
php Code:
Original - php Code
<?php /*Program: mssql_send.php *Desc: PHP program that sends an SQL query to the * MS SQL server and displays the results. */ echo "<html> <head><title>MSSQL Query Sender</title></head> <body>"; if(ini_get("magic_quotes_gpc") == "1") { $_POST['query'] = stripslashes($_POST['query']); } $host="yourhost"; $user="you"; $password="example";
Now, I've been racking my brain on how to list the Buyers and the number of related rows in the Orders table. Add to this that I only want to count the Orders where DatePaid IS NOT NULL.
I have this database running (ignore that that was done in Access, this is being made in Microsoft SQL Server 2005).
What I need to do is if you look at the tbl_events table and the tbl_timekeeperDetails table I need to make a query that:
Lists the names of all timekeepers (whether they are booked for a meeting or not), and the meetings at which they are timekeeping.
The tricky part of this is getting the query to show the timekeepers who aren't assigned to an event.
I have two seperate querys so far, but I'm presuming there must be a way of merging them or something.
I have this code so far: select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast,eventID from timekeeperDetails,events where timekeeperDetails.timekeeperID = events.timekeeperID select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast from timekeeperDetails
If anyone has any ideas, please do post a reply or email me at paul [at] abscond [dot] org
I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.
Thank you
"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"
I have a mysql query in my php script like UNIX_TIMESTAMP() - UNIX_TIMESTAMP(sessioncreated) as sessionspan . What is the equivalent of above query in mssql. I need the same query in mssql. Is there any function that does the same action in mssql2000.
I have 3 tables, that appear as follows (insignificant fields are not mentioned for brevity):
RETAIL(code, CurrentLocation) ~ 2.6 million records
LOCAUDIT(code, Date, Time, Location) ~ 3.6 million records
STAFF(ID, NAME) ~ 40K records
Each record in the RETAIL table represents a document. The LOCAUDIT table maintains history information for documents: locations they've been to. A location can be represented by a staff (from STAFF table), or an unlimited range of different names - not enumerated in a table.
The query we run tries to find the currentlocation for each document in the RETAIL table (if any). Since a document may have been to many location, I'm interested in the last location which has the max Date,Time.
To perform the query, I created two views:
HISTORY ======= CREATE VIEW HISTORY AS SELECT CODE, "DATE", TIME, CAST("DATE" + ' ' + TIME AS datetime) AS UpdateDateTime, LOCATION FROM LOCAUDIT
LASTHISTORY ========== CREATE VIEW LASTHISTORY AS SELECT CODE, Max(UpdateDateTime) AS LastUpdated FROM HISTORY GROUP BY CODE
UPDATE RETAIL SET CURRENTLOCATION = (CASE WHEN t3.NAME IS NULL THEN t2.LOCATION ELSE t3.NAME END) FROM RETAIL AS t4 LEFT JOIN LASTHISTORY AS t1 ON (t4.CODE = t1.CODE) LEFT JOIN HISTORY AS t2 ON (t1.ITEM = t2.ITEM AND t1.LastUpdated = t2.UpdateDateTime) LEFT JOIN STAFF AS t3 ON (t2.LOCATION = t3.ID)
What the query does is update the current location of each document. If the current location is a staff, we find the name of the staff member (hence the case).
In addition to clustered indexes on the primary keys, I've also created an index on (Code, Date, Time) on LOCAUDIT.
However, the query still seems to take up to 3 hours sometimes to run on a server with 4 CPU's and a whole bunch of memory. Can anyone suggest some way to improve this, add more effective indexes, or rewrite the queries all together. Any help is appreciated..
I need to use mssql to create a ranking of some kind. This is the situation: I need to assign position to a list of students based on thier scores. e.g Student Score Position StudentA 56 4 StudentB 78 1 StudentC 66 2 StudentD 56 4
I need to create the positions based on the scores of the ctudents. I will appreciate any assistance. Thank you.
Heya, I'm stuck on this query, we are currently moving from mysql to mssql, and this one has stumped me.
I used to use CONCAT, and i thought i'd found the correct syntax for MSSQL but it fails when used.
Code:
$sql = " SELECT * FROM ol_user_2_group ug INNER JOIN ol_users_details ud ON ug.user_id = ud.user_id INNER JOIN ol_user_rank ur ON ug.user_id = ur.user_id WHERE ug.group_id= ".$group_id." AND ud.firstname + ' ' + ud.lastname AS fullname LIKE '%".$search."%'";
ud.firstname + ' ' + ud.lastname AS fullname LIKE '%".$search."%'";
I got a big problem. I try to run a query on ms query analyzer or my .net-app, but in both cause, it wouldn't work. when i run the query in ms query analyzer it returns me this error-message:
Der aktuelle Zeilenwert der [verkauf_online]..[hs].[std_ftext].text-Spalte konnte nicht vom OLE DB-Provider 'MSDASQL' gelesen werden. [OLE/DB provider returned message: Die angeforderte Konvertierung wird nicht unterstützt.] OLE DB-Fehlertrace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].
sorry, its in german, i know but the most important fact is, that a conversion failed. i tried to read a text-field from a sybase asa-8-database which is linked with a ms sql-server 2000. I tried to find out more about the error-messe an the error-code (x80040e1d) but i couldn't find anything helpful.
I'm converting an ASP system from using msaccess to SQL Server as the db engine, and I'm stumped on the following query
update timecard set TcdDate = #3/18/05#
TcdDate is defined as a date/time type
It will not run with the date bracketed by # signs, and when I take them out, 1/1/1900 is stored in the dbs. Is there a different symbol to bracket the date with or should I be using a function to convert the date?
Is there a way to do a SELECT TOP # using a variable for the #? In other words I'm doing a SELECT TOP 50* FROM DATATABLE If I pass an @value for the number SELECT TOP @value* FROM DATATABLE doesn't work I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have. Thanks in advance.
hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
Here is our problem...We are doing a lot of selects against a table that has one large fieldin it.If we do a select against all the fields except for description, thequery comes back relatively quickly. If we add that last field (768chars) to the query, our query takes 10x longer (5 seconds vs 56seconds.)When we run the one without the description column, we can watchperfmon and see a very quick spike to physical disk. If we add in thedescription field we can see that the server becomes I/O bound - thedisk sits at 100% until the query is complete.We have tweaked the min query memory setting for the server but itseems to have had no effect no matter how high we set it. Is theresome point at which MSSQL decides it cannot perform the transaction inmemory? What would I increase to cure this problem?For example:TMZDIFF int410WRITETIMEcharno 16System_Namecharno 64Timestampcharno16Name charno32Mount_Pointcharno32Size intno4 10Space_Usedintno410Space_Availableintno410Inode_Sizeintno410Inodes_Usedintno410Inodes_Freeintno410Space_Used_Percentintno410Inodes_Used_Percentintno410FS_Type charno8Space_Available_Percentintno410Name_U ncharno32Descriptionncharno768
Hi, I need help. Understatement of the year right now, too = So... I've created a stored procedure that partially pulls a SQL union query to give me a complete one-line result set of a bunch of data... four separate selects comprise the query. The tables are structured identically to each other, but the values being pulled are specific and distinct. All of the non-date values stored are varchar values in the tables, and yet when I execute the SQL union query, I get the error message: Syntax error converting the varchar value to a column of data type int. I know SQL does implicit conversions, however Im not sure why it's trying to do that with this query, I suppose. it should have been straightforward. Any suggestions? Here is the query logic to look at, in case I'm missing the obvious
exec(' SELECT ACCOUNT, LNAME, FNAME, SEX, DOB, cast(MA_ContentValue as decimal) as MA_ContentValue, cast(MA_DOS as smalldatetime) as MA_DOS,cast(LDL_ContentValue as decimal) as LDL_ContentValue, cast(LDL_DOS as smalldatetime) as LDL_DOS, cast(HgbA1C_ContentValue as decimal)as HgbA1C_ContentValue, HgbA1C_DOS, cast(Creatinine_ContentValue as decimal) as Creatinine_ContentValue, Creatinine_DOS FROM ( SELECT Account, Lname, FName, Sex, DOB,MA_ContentValue, MA_DOS, 0 as LDL_ContentValue, 0 as LDL_DOS, 0 as HgbA1C_ContentValue, 0 as HgbA1C_DOS, 0 as Creatinine_ContentValue, 0 as Creatinine_DOS FROM dbo.vwDiabetic_MicroalbuminTop3 UNION SELECT Account, Lname, FName, Sex, DOB, 0 as MA_ContentValue, 0 as MA_DOS, LDL_ContentValue, LDL_DOS, 0 as HgbA1C_ContentValue, 0 as HgbA1C_DOS, 0 as Creatinine_ContentValue, 0 as Creatinine_DOS FROM dbo.vwDiabetic_LDLTop3 UNION SELECT Account, Lname, FName, Sex, DOB, 0 as MA_ContentValue, 0 as MA_DOS, 0 as LDL_ContentValue, 0 as LDL_DOS, HgbA1C_ContentValue, HgbA1C_DOS, 0 as Creatinine_ContentValue, 0 as Creatinine_DOS FROM dbo.vwDiabetic_HgbA1CTop3 UNION SELECT Account, Lname, FName, Sex, DOB, 0 as MA_ContentValue, 0 as MA_DOS, 0 as LDL_ContentValue, 0 as LDL_DOS, 0 as HgbA1C_ContentValue, 0 as HgbA1C_DOS, Creatinine_ContentValue, Creatinine_DOS FROM dbo.vwDiabetic_CreatinineTop3) tbl1
') Anything else that I can provide for someone to help me? I appreciate any/all feedback!
Hi all,is there any tool which is capable to convert query initially written formsaccess database to query for mssql server.I have tons of queries which contains iif, trim and similar functions whichshould be converted to case, ltrim(rtrim(, etc. etc.Does anyone know for tool which could do that automatically?Thanks in advance,Anabella
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
