You have already posted your question in QA. Reposting the question in other CodeProject forums - even if it's just a link to your question - will only serve to annoy the people you're relying on for help.
Remember, the people who answer questions here are unpaid volunteers, who are spread out across the globe. Be patient - nobody is under any obligation to answer your question within a set time.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I want to calculate the total conditional in the month but remove the records that are not the same month and not the same year, the condition that eliminates the view on the sql access because the result is true or false in the excel file http://www.mediafire.com/file/kq2mr2v6px1uxan/exercise.rar
DOANHTHU2: Sum(IIf(Not IsNull([DONGIA]),[SOLUONG]*([DONGIA]/1000000),[SOLUONG]*([DONGIATT]/1000000)))
DOANHTHU2: Sum(IIf(Not IsNull([DONGIA]) AND (SELECT COUNT(*) AS DEM FROM HDMUABAN WHERE (((HDMUABAN.HD)='M1101') AND ((HDMUABAN.LOAIHD)='FBAN') AND ((Month([NGAYGIAO]))=1) AND ((Year([NGAYGIAO]))=2019)) GROUPBY HDMUABAN.HD, HDMUABAN.NGAYGIAO, HDMUABAN.LOAIHD) AS NUM <=0,[SOLUONG]*([DONGIA]/1000000),[SOLUONG]*([DONGIATT]/1000000)))
TONGDOANHTHU2: IIf(Not IsNull([DOANHTHU2]) AndNot IsNull([BSLN]),[DOANHTHU2]+[BSLN],IIf(IsNull([DOANHTHU2]) AndNot IsNull([BSLN]),[BSLN],[DOANHTHU2]))
My example only removed one record, I designed the problem of removing many records if the same conditions exist
So I'm working on a vendor's database trying to search it. It's not normalized real well and the tables are kind of elephanted up as it was originally an MS Access database that they ported to MSSQL. I cannot modify the database but I can create views. In any case, what I currently have is a recursive string builder assembling a SQL statement that searches across multiple tables and views that I created to try to streamline things (one of my first SQL projects, it's not pretty). As you can imagine, it's sloooow. Can anyone point me towards a tutorial that would help me optimize this search?
Current query and code:
string strSearch = TextBox1.Text.Split(' ');
string strSelect = "SELECT INVY.ITEMNUM AS Item, " +
"INVY.DESCRIPTION AS Description, STOCK.QTYONHAND AS Quantity, " +
"INVY.UOM AS 'Issue By', STOCK.LOCATION AS Location, INVY.MODEL AS Model, " +
"INVY.UD2 AS IDGroup " +
"FROM INVY LEFT OUTER JOIN STOCK ON INVY.ITEMNUM = STOCK.ITEMNUM " +
"LEFT OUTER JOIN vw_Koogle_ISSREC_Grouping ON INVY.ITEMNUM = vw_Koogle_ISSREC_Grouping.ITEMNUM " +
"LEFT OUTER JOIN INVVEND ON INVY.ITEMNUM = INVVEND.ITEMNUM " +
foreach (string strParam in strSearch)
strSelect = strSelect + "AND ((INVY.DESCRIPTION LIKE '%" + strParam + "%') OR " +
"(INVY.NOTES LIKE '%" + strParam + "%') OR " +
"(INVY.MODEL LIKE '%" + strParam + "%') OR " +
"(INVY.UD2 LIKE '%" + strParam + "%') OR " +
"(INVY.UD1 LIKE '%" + strParam + "%') OR " +
"(INVY.ITEMNUM LIKE '%" + strParam + "%') OR " +
"(vw_RRD_Koogle_ISSREC_Grouping.EQNUM LIKE '%" + strParam + "%') OR " +
"(vw_RRD_Koogle_ISSREC_Grouping.NUMCHARGEDTO LIKE '%" + strParam + "%') OR " +
"(INVVEND.VENDORITEMNUM LIKE '%" + strParam + "%')) ";
strSelect = strSelect.Replace("WHERE AND", "WHERE") +
"GROUP BY INVY.ITEMNUM, INVY.DESCRIPTION, STOCK.QTYONHAND, " +
"INVY.UOM, STOCK.LOCATION, INVY.MODEL, INVY.UD2 " +
"ORDER BY INVY.DESCRIPTION";
sqlMP2Search.SelectCommand = strSelect;
Do you want to get so many tutorial URLs that point to nothing, and might not be able to provide a valid result? I am sure, not.
Please share a sample query with us, that you are running and the result you are getting. It would be really helpful to share the schema with us, and we would be able to help you out with the normalization process, or at least share how you can use other services on SQL Server like stored procedures to minimize the overheads of the queries.
With that update in the question, first thing that anybody is going to say is to remove the concatenation and use parameters in the query—it will protect you from SQL Injection.
Secondly, there are so many LIKE operators, why are you using searches on the table, and trying to match every column with the input. A quick tip would be, use a separate search for each column and then try to aggregate the overall results. This would have a little amount of WHERE clause, and the query would end quickly, yours is having multiple OR clauses, which is not letting SQL Server short-circuit the query either and is making it run on each of the records.
So, you can start by:
1. Add indexers to the columns as needed. You will know where to add them.
2. Create separate stored procedures to find the query results. Then try to aggregate the result of all queries.
3. Use caching to store the results of the most recent queries.
4. Also, try using full text search capabilities of SQL Server.
What else could cause this error beside the sequence not being created?
I have recreated the sequence using SQL code, also recreated it using Oracle SQL Developer "New Sequence" menu but still nothing. I only have a single class and and a single table, being a learning project, I don't want to complicate things. Project made using Maven.
<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN""http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<class name = "com.testing.classes.Person" table = "PERSON">
<id name = "id" type = "int" column = "ID">
<property name = "firstName" column = "FIRST_NAME" type = "string"/>
<property name = "lastName" column = "LAST_NAME" type = "string"/>
<property name = "age" column = "AGE" type = "int"/>
CREATE SEQUENCE CREATE_PERSON_ID INCREMENT BY 1 START WITH 1 MAXVALUE 9999 MINVALUE 1 CACHE 10;
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();
Person person = new Person("Name", "Last name", 48);
session.save(person); //Eclipse says the error is here
Well, as you can see in my hibernate.cfg.xml, I'm using SYSTEM, so that isn't the problem unfortunately.
After some more research I found the problem. All the sites with tutorials google suggested were using either <generator class="native"> or <param name="sequence" />SEQUENCE_NAME
The problem is that hibernate devs changed <param name="sequence" /> to <param name="sequence_name" />. It is weird that you get sequence does not exist error, instead of something like invalid value for param or something to suggest that is the problem.
After you install Oracle 11g XE, it will automatically create some tables (from what I've read, some are used for "Application Express", and you shouldn't drop them), but having them listed in Tables view is really annoying.
Is there a way to filter them out? And why do you use $ in table name for?
modified 16-Feb-19 14:04pm.
Last Visit: 31-Dec-99 19:00 Last Update: 29-Nov-21 20:23