Ask Your Question
0

Query problem with "AND" "OR" "LIKE" [closed]

asked 2017-10-15 22:08:37 +0100

pascale gravatar image

Hi, I have created a query in SQL that gives me the answer I want for one element, but i would like it to give me the correct answer for several elementSELECT "GEN", "debit", "credit", "debit" - "credit" "Solde" FROM ( SELECT ( "ID" ) "GEN" FROM "General" WHERE "ID" = '445663' ), ( SELECT SUM( "Montant" ) "debit" FROM "MONGENDEB" WHERE MONTH( "Date" ) = :MOIS AND "Debit" = '445663' ), ( SELECT SUM( "Montant" ) "credit" FROM "MONGENCRED" WHERE MONTH( "Date" ) = :MOIS AND "Credit" = '445663' )I would like to have the reply for 445663, 445662, 445000 and several others, I have tried "AND", "OR" and "LIKE" with varying result from "Syntax error" to "Not in this Table". Any help or advise will be greatly appreciated Kind regards Paul

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by pascale
close date 2017-10-16 20:48:38.762983

1 Answer

Sort by » oldest newest most voted
1

answered 2017-10-15 22:39:29 +0100

Ratslinger gravatar image

updated 2017-10-16 07:33:53 +0100

Hello,

Kind of looks like the last similar problem - here.

When having multiple choices, best to enclose in parenthesis. So instead of:

WHERE "ID" = '445663'

you might have:

WHERE ("ID" = '445663' OR "ID" = '445662' OR "ID" = '445000')

EDIT:

Based upon your selection including 'ID', it appears you want separate records with totals for each. Here is a working statement:

SELECT *
FROM (SELECT ID,
             DEBIT,
             CREDIT,
             CASE  WHEN DEBIT IS NULL THEN 0 ELSE DEBIT  END 
      - CASE WHEN CREDIT IS NULL THEN 0  ELSE CREDIT END "Solde"
      FROM "General"
        LEFT JOIN (SELECT "Debit",
                          SUM("MONGENDEB"."Montant") DEBIT
                   FROM "MONGENDEB"
                   WHERE MONTH("MONGENDEB"."Date") = :MOIS
                   GROUP BY "Debit") A ON "General"."ID" = "A"."Debit"
        LEFT JOIN (SELECT "Credit",
                          SUM("MONGENCRED"."Montant") CREDIT
                   FROM "MONGENCRED"
                   WHERE MONTH("MONGENCRED"."Date") = :MOIS
                   GROUP BY "Credit") B ON "General"."ID" = "B"."Credit")
WHERE ID = '445663'
OR    ID = '445662'
OR    ID = '445000'

I would suggest you take another look at your setup or, as you can see, get more knowledge on SQL. This took quite a while since I worked only with the embedded version not knowing what you had.

edit flag offensive delete link more

Comments

HI again, yes it is similar !! I have tried your advise but it gives me the two lines with the same total (the correct total for the first line repeated on both), I have added the same parentheses to the "credit" and "debit" part of the query, but this then gives a different total (sum of both lines) repeated again on both lines. I think I need to start again with this query, attack from a different angle and try to use the "ID" column to somehow generate the totals. Thank you for rapid reply,

pascale gravatar imagepascale ( 2017-10-15 23:30:03 +0100 )edit

(continued) I will get back to you tomorrow morning. Goodnight from Bordeaux. Regards Paul

pascale gravatar imagepascale ( 2017-10-15 23:31:31 +0100 )edit

@pascale Not exactly sure what result you want - One set of totals based upon multiple accounts or separate totals for different selected accounts? Also, don't remember if you ever stated - are you using an embedded or split DB? Knowing this greatly helps when dealing with SQL as embedded has less capabilities.

Ratslinger gravatar imageRatslinger ( 2017-10-16 03:35:56 +0100 )edit

Hi Mr @Ratslinger, yes you are right again you SQL statement is Just what I need, it does exactly what I want, AGAIN thank you so much for your help!!!!! To answer your questions; Iam using embedded, so would you advise that I set up a MYSQL separately as this will be a large database? I am trying to learn SQL as I go, My tables are based on this link text document but here she is using ms base and the SQL is different to base 5. Again many thanks

pascale gravatar imagepascale ( 2017-10-16 10:51:38 +0100 )edit
1

@pascale Definitely move away from embedded. Move either to a split DB or another such as MySQL, Maria DB, PostgreSQL or others. Embedded will eventually give you problems.

Ratslinger gravatar imageRatslinger ( 2017-10-16 16:01:30 +0100 )edit

Thanks again for your help. I looked at link text which explains using outside DB. Regards paul

pascale gravatar imagepascale ( 2017-10-16 20:48:19 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-10-15 22:08:37 +0100

Seen: 114 times

Last updated: Oct 16 '17