SQL Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query Problem

Author

Topic

viewaskew
Starting Member

3 Posts

Posted - 2002-11-14 : 16:55:31

If anyone could help me I would appreciate it.

I am attempting to create a single select that will return an account number from a table like this:

TransCode Division MPCode Ledger Class
TUIT ZZ ZZ 1 Z
TUIT ZZ ZZ 2 Z
TUIT BU ZZ 3 9
TUIT BU BU0001 4 2
ACTY BU ZZ 9 1
ACTY EN ZZ 5 3
REM ZZ ZZ 6 Z
REM ZZ ZZ Z 8

The user supplies the TransCode and I find the Division and MPCode based on their ID. How would I devise a single select to return a the correct Account number (Account number is the combination of Ledger and Class where neither field is Z).

For Example: Given TUIT as a transcode and determine BU as the division and an MPCode of BU0002. The select should return Account Number 39.

Or given the transcode of ACTY and determining EN as the Division and EN0002 as the MPCode the select should return 53.

If this actually makes any sense I would appreciate some help. I have it functioning in several steps but would be interested in getting into a single select statement.

Thanks.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-14 : 17:33:35

nothing too hard actually. But it's very hard to do with the data you've provided us. If you could actually post the create table statements it would help. More so, you examples confusin the hell outa me.

quote:
For Example: Given TUIT as a transcode and determine BU as the division and an MPCode of BU0002. The select should return Account Number 39.

How do you get mpcode = BU0002? I only see BU0001 in your example list... something I'm missing ther?

What your going to need to put together is a CASE statement (inside the select). Look up case in Books Online and see what you can come up with. If you need more help, you will have to make this example a bit easier to understand.

edit... ViewAskew kicks ass... love their productions

-----------------------
SQL isn't just a hobby, It's an addiction

Edited by - m.e. on 11/14/2002 17:34:11

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-15 : 00:35:09

Is this it:
SELECT Ledger + Class AS Account FROM Table WHERE Ledger <> 'Z' AND Class <> 'Z'
or am I missing s/t? As M.E. pointed out, the examples don't match the data sample.

Sarah Berger MCSD

viewaskew
Starting Member

3 Posts

Posted - 2002-11-15 : 09:58:18

Sorry for the confusion I was trying to keep it as succinct as possible. Let me try the long version:

I'm creating a program that allows user to enter transactions against customers accounts. The minimum information they need to supply via a Powerbuilder Interface or tab-delimited text file is the amount of the transaction, whether it's a debit or credit, the customer's ID, and the Transaction Code (TransCode).

Transaction Codes can be unique or variable. Unique Transaction codes have a single account number. Therefore unique transaction codes would have a single entry in the TRANS_CODE_ACCT table like this:

TransCode Div MPCode Ledger Class
ACTY ZZ ZZ 1 2

Some Transaction Codes allow for any number in the ledger field but have specific restrictions for the class or vice versa. They would appear in the table like this:

TransCode Div MPCode Ledger Class
TUIT ZZ ZZ 1 Z
TUIT ZZ ZZ 2 Z

Finally some Transaction Codes have specific account numbers based on the Division and MPCode of the customer. The Division and MPCode are not set by the user, I pull this information from other tables based on the customer's ID. They would appear like this:

TransCode Div MPCode Ledger Class
REM ZZ ZZ 3 4
REM BU ZZ 8 3
REM BU BU0001 8 2

TransCode, Div, MPCode are all foreign keys in the table.

The trick is given a customer's ID and TransCode how do I determine the correct account number in a single select.

For example, the user selects REM as Transaction Code and the customer's ID has the Division as BU and the MPCode as BU3323. Therefore the correct account number would be 83 since REM and BU match but there is no specific account number for MPCode BU3323.

Example 2: User Selects REM As Transaction Code and the customer ID has Division of EN and MPCode of EN0001. The correct account number would then be 34 since REM EN EN0001 and REM EN do not match a record in the table.

I will take a look at the CASE statement though, thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-15 : 10:18:43

I think the biggest problem I can see with passing along what you are getting at is that you aren't phrasing EXACTLY what you want in 1 sentence, but rather as examples.

I have a lot of trouble with this when someone in my office needs a query or an algorithm, and they can't tell me what they want, and I usually tell them that unless they can phrase it in a sentence or two there's no way I can guess what they are looking for.

So, based on your examples, are you looking for:

"Given a TransCode, a Division and an MPCode, return the matching Ledger and Class. If there is no match, take the lowest Ledger and Class that matches the TransCode."

Is that what you mean? If not, try to phrase it like that. Then you'll find that the query writes itself! And, if not, one of us will write it for you !

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-11-15 : 10:52:15

I believe this will do what you're looking for.



set nocount on

create table #trans (
    TransCode varchar(10),  Division varchar(10), 
    MPCode varchar(10), Ledger varchar(10), Class varchar(10)
)

insert #trans select 'REM', 'ZZ', 'ZZ', '3', '4' 
insert #trans select 'REM', 'BU', 'ZZ', '8', '3'
insert #trans select 'REM', 'BU', 'BU0001', '8', '2' 


declare @msg varchar(200)

select @msg = '*** The data ***'
print @msg
select * from #trans


declare @TransCode varchar(10), @Division varchar(10), @MPCode varchar(10)

select @TransCode = 'REM'
select @Division = 'BU'
select @MPCode = 'BU3323' 


select @msg = '*** Get Account Number for TransCode = ' + @TransCode + 
	'; Division = ' + @Division + '; MPCode = ' + @MPCode + ' ***'
print ''
print @msg


select top 1 Account_Number
from (
	select 
	Ledger + Class as 'Account_Number' ,
	Rank = Case 
		When @TransCode = TransCode and 
                     @Division = Division and 
                     @MPCode = MPCode and
                     MPCode != 'ZZ' then 1
		When @TransCode = TransCode and 
                     @Division = Division and 
                     MPCode = 'ZZ' then 2
		When @TransCode = TransCode and 
                     Division = 'ZZ' and
                     MPCode = 'ZZ' then 3
		else 999 end
	from #trans
) a
order by a.Rank


select @TransCode = 'REM'
select @Division = 'EN'
select @MPCode = 'EN0001' 


select @msg = '*** Get Account Number for TransCode = ' + @TransCode + 
	'; Division = ' + @Division + '; MPCode = ' + @MPCode + ' ***'
print ''
print @msg

select top 1 Account_Number
from (
	select 
	Ledger + Class as 'Account_Number' ,
	Rank = Case 
		When @TransCode = TransCode and 
                     @Division = Division and 
                     @MPCode = MPCode and
                     MPCode != 'ZZ' then 1
		When @TransCode = TransCode and 
                     @Division = Division and 
                     MPCode = 'ZZ' then 2
		When @TransCode = TransCode and 
                     Division = 'ZZ' and
                     MPCode = 'ZZ' then 3
		else 999 end
	from #trans
) a
order by a.Rank


drop table #trans

/**** Here are the results ****/

*** The data ***
TransCode  Division   MPCode     Ledger     Class      
---------- ---------- ---------- ---------- ---------- 
REM        ZZ         ZZ         3          4
REM        BU         ZZ         8          3
REM        BU         BU0001     8          2

 
*** Get Account Number for TransCode = REM; Division = BU; MPCode = BU3323 ***
Account_Number       
-------------------- 
83

 
*** Get Account Number for TransCode = REM; Division = EN; MPCode = EN0001 ***
Account_Number       
-------------------- 
34

Edited by - muffinman on 11/15/2002 11:28:03

viewaskew
Starting Member

3 Posts

Posted - 2002-11-15 : 12:12:34

Okay, you're right jsmith8858, let me try that again:

Given a TransCode, a Division and an MPCode, return the matching Ledger and Class. If there is no match, take the lowest Ledger and Class that matches the TransCode and Division. If there is still no match then take the Ledger and Class that matches the TransCode.

I will try to remember that advice in the future.

I think MuffinMan managed to wade through my verbose ramblings and find a solution. Thanks very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources