Databases
StackOverflow2013
Postsdbo
POSelect IIF SUM command
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect IIF SUM command
    primarykey
    data
    text
    <p>I am using Jet SQL from excel using an ADODB connection to an IBM400 server to try and and get some data. I have done this fine before and it is fine with all other JET SQL commands however I have ran into a problem to which I am unable to solve. It is quite simple so I imagine that I am just not putting the correct syntax in but what I am trying to do is get some totals.</p> <p>I have a table that contains part numbers and quantities within the locations of that part (more than one location per part). My goal is to have an sql command grab the total quantity (summing all locations) per part. I am able to do this one part at a time successfuly using: (for simplicity I will use part numbers 12345678 and 01234567)</p> <pre><code>SELECT SUM(CPJDDTA81.F4101JD.LIPQOH) FROM CPJDDTA81.F4101JD WHERE CPJDDTA81.F4101JD.IMLITM = '12345678' </code></pre> <p>CPJDDTA81.F4101JD is my table, IMLITM is the column name of part numbers, LIPQOH is the quantity on hand per location. </p> <p>The single search produces the sum I want however the problem comes when trying to run more than one sum within one sql command. I have tried using a select iif command like the following:</p> <pre><code>SELECT IIF(CPJDDTA81.F4101JD.IMLITM = '12345678',SUM(CPJDDTA81.F4101JD.LIPQOH),IIF(CPJDDTA81.F4101JD.IMLITM = '01234567',SUM(CPJDDTA81.F4101JD.LIPQOH),0) FROM CPJDDTA81.F4101JD </code></pre> <p>This command provides an error saying that "=" is not a valid token (the = sign within the IIF statement). I was hoping that someone out there can help me write a correct statement to accomplish this. My actual part list will be much larger so I will be using VBA to construct the SQL statement but I need to learn how to do two parts first. Thanks ahead of time.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USEric F
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload