OK I'm trying to create a sql query on our as400. I have a main table
like this:
acct prf acct no. sku
AAA 123 ABCD
AAA 123 XYZ
I have another table
Acct prf acct no. acct type
AAA 123 BU
and another
Acct prf Cont typ
AAA BUA
Finally, I have the table I want to pull information from:
Cont Typ acct typ sku price
XYZ 5.00
BUA XYZ 4.50
BUA BU XYZ 4.00
BUA ABCD 10.00
ABCD 12.00
I need to pull the price for each sku in my main table. I want a
perfect match of Cont, Acct, Sku if I can get it, a Cont, acct=' ',
sku match if not, and a Cont=' ', acct=' ', sku match if neither of
those exist. I tried using a correlated subquery, but can't get it to
work.
Please help.
Lee 1 2247
Make names of first, second and third table as sku, acct_type and
Cont_typ.
And name last table that include price as Price.
Then, I thought that it is worth to try following Query.
While I have no environment of AS/400 or iSeries to try it,
I couldn't find no inconsistency with syntax by referencing "DB2
Universal Database for iSeries SQL Reference Version 5 Release 2". - WITH prf_Price (sku, price, match_level) AS (
-
SELECT P.sku, P.price
-
, CASE
-
WHEN P.Cont_typ = C.Cont_typ
-
AND P.acct_typ = A.acct_type THEN
-
1
-
WHEN P.Cont_typ = C.Cont_typ
-
AND P.acct_typ = '' THEN
-
2
-
WHEN P.Cont_typ = ''
-
AND P.acct_typ = '' THEN
-
3
-
END
-
FROM Cont_typ C
-
, acct_type A
-
, sku S
-
, Price P
-
WHERE A.acct_prf = C.acct_prf
-
AND S.acct_prf = A.acct_prf
-
AND S.acct_no = A.acct_no
-
AND P.sku = S.sku
-
)
-
SELECT sku, price
-
FROM prf_Price PP
-
WHERE match_level
-
= (SELECT MIN(match_level)
-
FROM prf_Price PM
-
WHERE PM.sku = PP.sku
-
)
-
;
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Wm |
last post by:
As an amateur wannabe-pro programmer, I am trying to learn not only how to
use PHP but how to do it *efficiently*. (Trust me, you don't wanna see some
of my stuff!!!) I'm noticing a number of my pages have a mixture of HTML
and PHP, very interspersed. Example: A form with 20 fields where I echo a
variable in each field to show existing data....
|
by: Aaron Davies |
last post by:
I'm developing a collaborative whiteboard, in which all objects
(shapes, clip art icons, etc.) are synchronized between all
participants in a session. It's working well, but I'm running into a
problem: if two people try to drag the same object at the same time,
nothing prevents them from doing so, and whichever one them lets go
first will...
|
by: NowItsWhatever |
last post by:
In query DESIGN view, how do I automatically "fit" the columns in the
table/field grid to the lengths of the table and field names (including any
functions applied to the fields). I am not talking about the query results.
I am talking about sizing parts of the query DESIGN view, so that they are
readable (long table and field names that get...
|
by: Achim Domma |
last post by:
Hi,
I have to convert a string to its "best possible" ascii representation.
It's clear to me that this is not possible or sense full for all unicode
characters. But for most European characters it should be possible.
For example:
"Müller" should become "Muller" and "é" should become "e".
|
by: kjworm |
last post by:
Greetings,
I have a report displayed on a screen that anyone in the office can look at. The data on this report will change from time to time. This report has different colors displayed depending on the data. Since I'm using Access '97, I can't do this on a form. What I am doing is "refreshing" this report every 60 seconds. Since Access...
| |
by: elias.farah |
last post by:
Hello Everyone,
I'm having some very weird behavior on a couple of Access forms. (Not
all forms, just some of them).
The forms have been working for years, under Access XP/2003 etc, and
last week upgraded from Windows XP/Office 2003 to Vista x64/Office
2007.
Under Access 2007, a couple of forms are now taking 60 seconds to
|
by: =?Utf-8?B?QW1tZXI=?= |
last post by:
I've read many incomplete opinions about the "Best Practice" for securely
accessing SQL but what I really need to find the "Best Practice" that fits my
applications needs.
Currently (alpha stage) I am Using a .Net DSN-Less SQLConnection method in
my client program (vb.net) and sending uid/pwd across the network. The
client only
calls upon...
|
by: =?ISO-8859-1?Q?Andr=E9?= |
last post by:
Hi everyone,
I'd be interested in hearing suggestions as to the "best" way to drive
a Python program step by step from another application.
Details:
---------
I have implemented a "Robot" that can be programmed by a user to
perform certain actions. (see Reeborg below for a simple javascript-
|
by: 200dogz |
last post by:
Hi guys,
I'm making an expandable table which currently layers of <table>s are used to contain the table rows in order to show/hide them.
This works, but having to format columns of the child table to be like its parent table can be quite annoying. Before I use tables to do this I use <tbody> to store the <tr>s. Doing so made the format...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |