Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

sql problem_ sp

table definition

BillID
Date_
SupplierCode
BillType
TypeCode
Total
Currency
ROE
payment_or_bill
AmountType
AmountFC
PoNo
AmountFC
MultiplicityID
BillID_Detailed
Reference

sample data in the table

61809779-be45-41a2-be9f-6d296dd740bc

9/8/2008 12:00:00 AMFR1ChequeCUSTOM66.00MUR96.000VAT996221.00CA101996221.005774c555-7785-45a6-828e-03c8973f68a17156f061-e587-43bf-b5d2-355543e5cee7754

ec44e842-6ec4-4620-8f3f-6671e6a3a500

9/9/2009 12:00:00 AMFR1ChequeCUSTOM51111.00MUR44.000DUTY454.00CA101454.0089cab236-5ea4-4d71-8c88-058b3b46bd9e3f0741af-3a5f-4561-98eb-e6f0b544a9764444

ec44e842-6ec4-4620-8f3f-6671e6a3a500

9/9/2009 12:00:00 AMFR1ChequeCUSTOM51111.00MUR44.000AMOUNT84.00CA10184.00e3b9e71d-097d-47c8-a180-5f0a8d3b264e52021fb8-e98b-497d-b360-15030955c6924444

61809779-be45-41a2-be9f-6d296dd740bc

9/8/2008 12:00:00 AMFR1ChequeCUSTOM66.00MUR96.000DUTY58.00CA10158.0093e74e27-7943-4bdf-9fca-66084c2ded4ecbfe7c24-dba8-4dd6-92b4-d00e07507ef3754

ec44e842-6ec4-4620-8f3f-6671e6a3a500

9/9/2009 12:00:00 AMFR1ChequeCUSTOM51111.00MUR44.000test454.00CA101454.0057aa2fa5-6304-4219-826e-6e15c2f824ee0cc4f1d7-fc08-4b0c-838f-f51abd30956f4444
61809779-be45-41a2-be9f-6d296dd740bc9/8/2008 12:00:00 AMFR1ChequeCUSTOM66.00MUR96.000AMOUNT21.00CA10121.00c91e4faa-99e0-4d3d-a990-98a4ff063192e6a374f0-66d7-4cf7-a223-bfd62fdb27aa754

ec44e842-6ec4-4620-8f3f-6671e6a3a500

9/9/2009 12:00:00 AMFR1ChequeCUSTOM51111.00MUR44.000tes19652.00CA1019652.008dff0708-4fe5-4340-9596-aaedc40a3815793a9acb-276e-4e99-b5a1-150ac32bda694444

ec44e842-6ec4-4620-8f3f-6671e6a3a500

9/9/2009 12:00:00 AMFR1ChequeCUSTOM51111.00MUR44.000VAT844.00CA101844.005a4b442e-25b1-46a3-a747-e79050108283f38fbd02-c8b0-4fec-bb69-0fdd853a23ff4444

stored procedure

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERPROCEDURE [dbo].[StP_Suppliers_on_PO]

@Ponoasnvarchar(25),

@payment_or_billnvarchar(2)

AS

BEGIN

SETNOCOUNTON;

declare @stmtvarchar(4000)

DECLARE @fieldnamevarchar(10)

DECLARE FieldsCURSORFORSELECT Types

FROM dbo.Bill_Types

set @stmt='Select distinct pono, supplier.suppliercode, supplier.Suppliername '

OPEN FieldsFETCH Next

FROM FieldsINTO @fieldname

WHILE@@Fetch_Status= 0

BEGIN

SET @stmt= @stmt+',(select Total from

bill_ where BillType = '''+ @fieldname+''''

SET @stmt= @stmt+' and bill_.BillType = brE.BillType '

SET @stmt= @stmt+' and bill_.typecode = brE.typecode '

SET @stmt= @stmt+' and bill_.reference = brE.reference '

set @stmt= @stmt+' ) As '+ @fieldname

FETCH Next

FROM Fields

INTO @fieldnameEND

CLOSE Fields

DEALLOCATE Fields

SET @stmt= @stmt+' From Bills_Expanded as brE,supplier'

SET @stmt= @stmt+' where pono = '''+ @pono+''''

SET @stmt= @stmt+' and payment_or_bill = '+ @payment_or_bill+''

SET @stmt= @stmt+' and supplier.suppliercode = brE.suppliercode'

SET @stmt= @stmt+' order by suppliercode'

exec(@stmt)

select(@stmt)

END

output

Select distinct pono, supplier.suppliercode, supplier.Suppliername ,(select Total from bill_ where BillType = 'Cheque' and bill_.BillType = brE.BillType and bill_.typecode = brE.typecode and bill_.reference = brE.reference ) As Cheque,(select Total from bill_ where BillType = 'Estimated' and bill_.BillType = brE.BillType and bill_.typecode = brE.typecode and bill_.reference = brE.reference ) As Estimated,(select Total from bill_ where BillType = 'Final' and bill_.BillType = brE.BillType and bill_.typecode = brE.typecode and bill_.reference = brE.reference ) As Final,(select Total from bill_ where BillType = 'Proforma' and bill_.BillType = brE.BillType and bill_.typecode = brE.typecode and bill_.reference = brE.reference ) As Proforma From Bills_Expanded as brE,supplier where pono = 'ca101' and payment_or_bill = 0 and supplier.suppliercode = brE.suppliercode order by suppliercode

pono suppliercode suppliername cheque estimated final proforma

CA101 FR1 Roger NULL NULL NULL 55.00
CA101 FR1 Roger 66.00 NULL NULL NULL
CA101 FR1 Roger 51111.00 NULL NULL NULL
CA101 FR2 Khune & Nagel 448.00 NULL NULL NULL
CA101 SP2 C2323 NULL NULL 44.00 NULL
CA101 SP4 Test NULL NULL NULL 4554.00

i need to get each amount type that is created by cursor in a column

note that the first three rows is a dilema.. there are 2 cheques so i need to be able to send all on one row, that is

ca101 fr1 roger 55.00 66.00 51111.00 null null

im not sure how to do this.

[11960 byte] By [hrubesh] at [2007-12-25]
«« View .rpt on .NET client without client install?
»» Catching unexpected Soap Faults
# 1

Hrubesh:

I started looking over your problem and ran into several snags. First was the syntax of the fragment

... ) As Proforma From Bills_Expanded as brE,supplier where ...

This looks like the obsolete comma delimited JOIN syntax; are you running SQL Server 2000? Next, in your sample output you have four different supplier codes -- FR1, FR2, SP2, and SP4; however, in your sample data you only have one supplier code -- FR1; should some of your sample records have different suppliercodes? Also, your sample output shows some data appearing in the CHEQUE, ESTIMATED and PROFORMA columns; however, your sample data only includes the 'Cheque' bill type; should some of these records really be either an 'Estimated' or a 'Proforma' bill type?

Also, the stored procedure as it exists appears to have four amount columns -- Cheque, Estimated, Final and Proforma -- that look like they are there to display specific data associated with each column. For supplier "Roger" it seems that there are three quantities -- a cheque amount of 66.00, a cheque amount of 51111.00 and a proforma amount of 55.00; however, in when desired output for this data is exhibited it looks like you are designating the 55.00 amount for the 'Cheque' column, the 66.00 amount to the 'Estimated' column and the amount of 51111.00 to the 'Final' column. My knee jerk reaction to this is that this is simply a data problem since none of the sample data is slotted to either the 'Estimated' or 'Final' column. Another possibility is that the intention is that these 4 fields are used as "buckets" for caching these amounts. What is the idea of these 4 amount columns?

Dave

Mugambo at 2007-9-3 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2
Specifying joined tables using a comma-separated list is not obsolete syntax. It is valid ANSI SQL syntax and still supported in SQL Server. The only syntax that has been deprecated is the proprietary outer join syntax (*=, =*).

Anyway, the problem requires more information to solve. Like expected results and meaning of each output column.
UmachandarJayachandran-MS at 2007-9-3 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3

I'll come back to it as soon as my design is confirmed. (2-3 days)

thanks.

hrubesh at 2007-9-3 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified