• Welcome, Guest. Please login.
 
September 17, 2019, 09:48:03 am

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - Fim

31
I have a Item Master Table ITE with 100,000 items. I want all the items whose item name contains all the keywords. The number of keywords can be 1 to 5.     
Current solution is shown below (maybe something wrong in the translation from Swedish to English)
Is it possible to solve the selection in the server using LIKE or like() ?
STARTING:
    slSel "SELECT ITEM_NO, ITEM_NAMN FROM ITE ORDER BY ITEM_NO"

FETCH_NEXT_ITEM:
    IF slGetRow = %FALSE THEN GOTO ENDING
    ITE.ITEM_NO   = slF(01)
    ITE.ITEM_NAME = slF(02)
    ITEM_NAME = SHRINK$(ITEM_NAME)

SELECT_ITEM:
    IF INSTR(ITEM_NAME, KEYWORD1) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD2) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD2) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD2) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD2) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD3) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD3) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD4) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD4) = 0 THEN GOTO FETCH_NEXT_ITEM
    IF LEN(KEYWORD5) = 0 THEN GOTO SELECT_ITEM_END
    IF INSTR(ITEM_NAME, KEYWORD5) = 0 THEN GOTO FETCH_NEXT_ITEM
SELECT_ITEM_END:

    PRINT ITEM_NO, ITEM_NAMN
    GOTO FETCH_NEXT_ITEM   
   
ENDING:   
     
     
Example:
KEWORD1 = "FOR"
KEWORD2 = "SS"       

Item Names
1 CHASSI FOR SWITCH    <-- Hit
2 CHASSI-STOMME
3 FORMAT-BAND     
4 KASSERAD OMFORMARE   <-- Hit


/Fim W.
32
The server works so well, so you forget that there is an SQLiteningServer.Cfg. Now I have changed to Timout = -1.
Thanks for the help.
Then there's another thing, why should you have a TimOut at all?

/Fim W.
33
If a client does not call the server in x minutes, you will get an error -18 at the next call.
Is there any good way to stay connected?
/Fim W.
34
Bern,
Thank you for the comforting words.

/Fim W.
35
I have The Definitive Guild to SQLite by Michael Owns and
Using SQLite by Jay A. Kreibich.

Thanks for the tip.
36
Bern,
Many thanks.
I feel a bit stupid as not myself, did not understand it.

/Fim W.
37
This triggers does not work, what am I doing wrong?
CREATE TRIGGER ARB_TRIGG_05 UPDATE OF FK_KOST ON ARB FOR EACH ROW BEGIN UPDATE VOR SET FK_KOSTN_ARB_SUM = FK_KOSTN_ARB_SUM - OLD.FK_KOSTN + NEW.FK_KOSTN WHERE ORDERNR = NEW.ORDERNR; END
I am making a change of FK_KOST in ARB and want change to update an overruled table VOR.
But FK_KOSTN_ARB_SUM in VOR does not update.

/Fim W.
38
Thank you, I should have been able to figure it out myself.

/Fim W.
39
This works well:
CREATE TRIGGER ARB_TRIGG_01 INSERT ON ARB FOR EACH ROW BEGIN UPDATE VOR SET FK_KOSTN_ARB_SUM = FK_KOSTN_ARB_SUM + NEW.FK_KOSTN WHERE ORDERNR = NEW.ORDERNR; END

But not this:
CREATE TRIGGER ARB_TRIGG_01 INSERT ON ARB FOR EACH ROW WHEN OPKOD = 'LEGO' BEGIN UPDATE VOR SET FK_KOSTN_ARB_SUM = FK_KOSTN_ARB_SUM + NEW.FK_KOSTN WHERE ORDERNR = NEW.ORDERNR; END
I do not understand what it's wrong.
Pls help
/Fim W.
40
Here is my attempt with trigger:

    slExe "Drop Table If Exists LPL"
    slExe "Create Table LPL (" +  _
"LAGERPLATS,"                         + _
"ARTNR,"                              + _
"LSALDO_ANT,"                         + _
"LAGERKONTO,"                         + _
"MATERIALPALAGGKONTO)"

    slExe "CREATE UNIQUE INDEX LPL_INDEX0 ON LPL (LAGERPLATS, ARTNR)"
    slExe ("CREATE TRIGGER LSALDO_I AFTER INSERT ON LPL FOR EACH ROW BEGIN UPDATE ART SET LSALDO_ANT = LSALDO_ANT + LPL.LSALDO_ANT WHERE ARTNR = LPL.ARTNR; END", "E2")

But I get the error "1 = No such column: LPL.LSALDO_ANT
It must be "UPDATE ART ...... LPL.LSALDO_ANT .."
Can not I use a term from LPL when updating ART?
/Fim W.

41
Bern,
A trigger that you suggest seems to be a good solution, thanks for the tip.
I'll think about it.
/Fim W.
42
Now it is solved.
Here it is, in Swedish.

SELECT ART.ARTNR, ARTBEN, SUM(LPL.LSALDO_ANT) AS SALDO, ART.BESTPKT, PARTISTORLEK, ART.LEVNR FROM ART, LPL WHERE LPL.ARTNR = ART.ARTNR GROUP BY ART.ARTNR ORDER BY ART.LEVNR

The rest of my problems I will solve in the program not using SQL.

It had taken years to solve this on its own.
Thank you for your help.

/Fim W.


43
I will have a look at it in some days.
/Fim
44

Thank you, That looks very interesting, will look closer to it.
/Fim
45
I have two tables. One ART with all data (except inventory on hand) about all parts.
The other table is LPL with inventory on hand with one record for each part and each stock location.
Now I want a list, for each part where total inventory on hand for each part is less then the reorder point.
Is it possible to put these two SQL-statement into one statement?
SELECT PARTNO, SUM(QOH) AS QOH_SUM FROM LPL GROUP BY PARTNO
SELECT PARTNO, REORDEP, QOH_SUM FROM ART WHERE QOH_SUM < REORDP
As you see QOH_SUM a computed term and not a term in any table.

/Fim W.