February 24, 2016 at 12:34 pm
Hi All,
Need assistance with a select query.
I need to write a statement to provide to a third party systems provider for automating courier ticket labels in our warehouse.
We'll be using the order reference number as the unique identifier for the warehouse users to enter in the dispatch system, and this then needs to look up the dispatch details for the specific order and print the delivery address lines
What I need, is for the select to look up the details from our Warehouse Management System header table, IDS2000D.WMFP00. In it's simplest form, the script looks like:
SELECT
NAMEX0 AS "Deliver to Name",
ADR1X0 AS "Address Line 1",
ADR2X0 AS "Address Line 2",
ADR3X0 AS "Address Line 3",
ADR4X0 AS "Address Line 4'
FROM IDS2000D.WMFP00
WHERE SUBSTR(REF#X0,2,10) = '2225190'
The above example looking at order 225190, and would work great where additional delivery details have been keyed in by the dealership
However, where the dealer doesn't key in a delivery address, this will override the delivery address fields to - for all 4 lines (ADR1X0-
ADR4X0), which indicates that it's being sent direct to the dealership. It then needs to pull the address information from the AR master table, IDS2001D.ARFP00, fields DAD1A0
- DAD4X0
I've tried writing Case statements, with an IF sub-clause, but not having any luck. We run an AS400 system with DB2 database
Any help would be appreciated. Thanks!
February 24, 2016 at 12:58 pm
1) Please provide create table statements, sample data via INSERT statements and expected output cases to back up what you are asking. I can't make heads or tails of it.
2) Are you really asking us how to help you with AS400 stuff here on this SQL Server forum?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 24, 2016 at 1:39 pm
TheSQLGuru (2/24/2016)
1) Please provide create table statements, sample data via INSERT statements and expected output cases to back up what you are asking. I can't make heads or tails of it.2) Are you really asking us how to help you with AS400 stuff here on this SQL Server forum?
Go easy on the newbie!
February 24, 2016 at 1:48 pm
Syzmik (2/24/2016)
Hi All,Need assistance with a select query.
I need to write a statement to provide to a third party systems provider for automating courier ticket labels in our warehouse.
We'll be using the order reference number as the unique identifier for the warehouse users to enter in the dispatch system, and this then needs to look up the dispatch details for the specific order and print the delivery address lines
What I need, is for the select to look up the details from our Warehouse Management System header table, IDS2000D.WMFP00. In it's simplest form, the script looks like:
SELECT
NAMEX0 AS "Deliver to Name",
ADR1X0 AS "Address Line 1",
ADR2X0 AS "Address Line 2",
ADR3X0 AS "Address Line 3",
ADR4X0 AS "Address Line 4'
FROM IDS2000D.WMFP00
WHERE SUBSTR(REF#X0,2,10) = '2225190'
The above example looking at order 225190, and would work great where additional delivery details have been keyed in by the dealership
However, where the dealer doesn't key in a delivery address, this will override the delivery address fields to - for all 4 lines (ADR1X0-
ADR4X0), which indicates that it's being sent direct to the dealership. It then needs to pull the address information from the AR master table, IDS2001D.ARFP00, fields DAD1A0
- DAD4X0
I've tried writing Case statements, with an IF sub-clause, but not having any luck. We run an AS400 system with DB2 database
Any help would be appreciated. Thanks!
First of all, welcome to SQL Server Central.
If I'm reading this correctly and it was SQL Server , I would suggest that either you use a CASE or you ISNULL your IDS2000D.WMFP00 address columns with the appropriate columns in IDS2001D.ARFP00. You'll need to be able to join the two tables together, but without table definitions, anything I suggest would be a guess and most likely wrong.
When considering what I said, please keep in mind that I don't know DB2. You'll probably get better advice if you post your question in an AS400 or a DB2 forum.
February 24, 2016 at 2:05 pm
Sorry, SQL is rather new to me and I'm basically the only one in the business who has a skill set here. So I'm trying to find my way through things.
Yes, we run as AS400 system, however all the query writing I'm doing is through SQL server 2012. Hence why I thoughts this forum would be OK
I don't have Create Table or Insert statements, as the data already exists. I'm simply trying to extract the data.
I need a conditional statement that retrieves data from a table, but if the data retrieved in the 4 address columns equals '-', then pull the address data from another table entirely.
I'll take your advise on board and look to post in another forum. Cheers
February 24, 2016 at 2:30 pm
I'm now wondering if you're querying a linked server or something.
This is a shot in the dark without the table definitions, especially on the join columns. I'm hoping that the syntax helps you. Obviously, it's completely untested.
SELECT a.NAMEX0 'Deliver To Name',
CASE WHEN a.ADR1X0 = '-' THEN b.DAD1A0 ELSE a.ADRX10 End 'Address Line 1'
FROM IDS2000D.WMFP00 a
INNER JOIN IDS2001D.ARFP00 b ON b.REF#X0 = a.REF#X0
WHERE SUBSTR(REF#X0,2,10) = '2225190';
Please let me know if this helps get you closer to your goal.
February 24, 2016 at 2:43 pm
Mate, that's exactly what I need to point me in the right direction.
Using the Case statement correctly has pointed me to what I needed. Script complete now. Many thanks!!
February 25, 2016 at 6:32 am
Excellent. Thanks for posting back.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply