Click here to Skip to main content
14,304,598 members
   

Database

 
GeneralRe: Displaying multiple rows into single row as columns based on id - Sql Server Pin
Member 111541886-Mar-19 23:24
memberMember 111541886-Mar-19 23:24 
AnswerRe: Displaying multiple rows into single row as columns based on id - Sql Server Pin
Richard Deeming6-Mar-19 22:08
mveRichard Deeming6-Mar-19 22:08 
GeneralRe: Displaying multiple rows into single row as columns based on id - Sql Server Pin
Member 111541886-Mar-19 23:21
memberMember 111541886-Mar-19 23:21 
GeneralRe: Displaying multiple rows into single row as columns based on id - Sql Server Pin
Mycroft Holmes7-Mar-19 10:50
memberMycroft Holmes7-Mar-19 10:50 
QuestionHelp me with caculate debit balace and incredit by SQL server 2012 Pin
Member 141737536-Mar-19 19:08
memberMember 141737536-Mar-19 19:08 
Rant[REPOST] Help me with caculate debit balace and incredit by SQL server 2012 Pin
Richard Deeming6-Mar-19 21:58
mveRichard Deeming6-Mar-19 21:58 
QuestionSQL Access: Calculate the total elimination of records not the same month and year ? Pin
Member 24584674-Mar-19 21:26
memberMember 24584674-Mar-19 21:26 
QuestionQuery Question Pin
milo-xml1-Mar-19 2:14
professionalmilo-xml1-Mar-19 2:14 
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 " +
        "WHERE ";

    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;
}


modified 1-Mar-19 11:31am.

AnswerRe: Query Question Pin
Afzaal Ahmad Zeeshan1-Mar-19 3:19
mveAfzaal Ahmad Zeeshan1-Mar-19 3:19 
GeneralRe: Query Question Pin
milo-xml1-Mar-19 5:31
professionalmilo-xml1-Mar-19 5:31 
GeneralRe: Query Question Pin
Afzaal Ahmad Zeeshan1-Mar-19 7:46
mveAfzaal Ahmad Zeeshan1-Mar-19 7:46 
GeneralRe: Query Question Pin
milo-xml1-Mar-19 8:05
professionalmilo-xml1-Mar-19 8:05 
AnswerRe: Query Question Pin
Eddy Vluggen1-Mar-19 7:46
mveEddy Vluggen1-Mar-19 7:46 
GeneralRe: Query Question Pin
milo-xml1-Mar-19 8:03
professionalmilo-xml1-Mar-19 8:03 
GeneralRe: Query Question Pin
Eddy Vluggen1-Mar-19 8:51
mveEddy Vluggen1-Mar-19 8:51 
QuestionMANAGEMENT DOCUMENT Pin
Member 1413102419-Feb-19 2:23
memberMember 1413102419-Feb-19 2:23 
AnswerRe: MANAGEMENT DOCUMENT Pin
Eddy Vluggen19-Feb-19 3:09
mveEddy Vluggen19-Feb-19 3:09 
AnswerRe: MANAGEMENT DOCUMENT Pin
CHill6019-Feb-19 4:14
protectorCHill6019-Feb-19 4:14 
AnswerRe: MANAGEMENT DOCUMENT Pin
Victor Nijegorodov19-Feb-19 9:27
memberVictor Nijegorodov19-Feb-19 9:27 
GeneralRe: MANAGEMENT DOCUMENT Pin
jschell23-Feb-19 11:03
memberjschell23-Feb-19 11:03 
GeneralRe: MANAGEMENT DOCUMENT Pin
Victor Nijegorodov23-Feb-19 11:07
memberVictor Nijegorodov23-Feb-19 11:07 
AnswerRe: MANAGEMENT DOCUMENT Pin
ZurdoDev1-Mar-19 5:52
professionalZurdoDev1-Mar-19 5:52 
QuestionHibernate error ORA-02289: sequence does not exist [SOLVED] Pin
Valentinor16-Feb-19 5:37
memberValentinor16-Feb-19 5:37 
AnswerRe: Hibernate error ORA-02289: sequence does not exist Pin
Richard Deeming18-Feb-19 1:42
mveRichard Deeming18-Feb-19 1:42 
GeneralRe: Hibernate error ORA-02289: sequence does not exist Pin
Valentinor18-Feb-19 2:37
memberValentinor18-Feb-19 2:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.