DTS Transformation Task: needing

Showing posts with label needing. Show all posts

Monday, March 19, 2012

Newbie needing help with sql script

I keep getting this error on this script can some one help me out.

There is an error in the query. The data types varchar and sql_variant are incompatible in the add operator.
The multi-part identifier "LANGUAGES.DESCRIPTION" could not be bound.
The multi-part identifier "LANGUAGES.DESCRIPTION" could not be bound.

SELECT 'Quarter All' as 'qtr',
COUNT(JOB.JOBID) as 'transcount',
COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
SUM(JOB.LANGUAGE_TCOST) as 'lcost',
SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS EXPR1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
LANGUAGES.DESCRIPTION

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS

WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)
AND
(MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12))
AND
(PAYER.PAY_COMPANY like '%' + @.Company + '%')

Group By
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
LANGUAGES.DESCRIPTION
Order By 'QTR' asc

You have Languages.Description in your select list and your group expression but I don't see where you have specified Languages as a table in your join expression. Otherwise, I don't know your table design so I don't know where you are trying to add a varchar and a sql_variant.|||

I am able to run this script in my sql 2005 report but when i go from the design mode to the preview mode I get this error.

An error occurred during reporting processing

cannot read the next data row for data set al2

conversion failed when converting the nvarchar value 'AARIYA' to data type int

the only changes I have made to this script is adding the JOB.LANUAGE_ID AND THE LANGUAGE.DESCRIPTION AND THE LEFT JION STATEMENT FOR THEM

SELECT 'Quarter All' as 'qtr',
COUNT(JOB.JOBID) as 'transcount',
COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
SUM(JOB.LANGUAGE_TCOST) as 'lcost',
SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS EXPR1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
LANGUAGES.DESCRIPTION

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN LANGUAGES
ON JOB.LANGUAGE_ID = LANGUAGES.DESCRIPTION

WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)
AND
(MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12))
AND
(PAYER.PAY_COMPANY like '%' + @.Company + '%')

Group By
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
LANGUAGES.DESCRIPTION

Order By 'QTR' asc

|||

Is that value coming through this parameter @.Company? What are the values that you are passing to @.Company? and what is the datatype of PAY_COMPANY?

Also, is this really required? Can there be a month thats not from this list?

(MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12))

|||

First I ahave a more complex tsql query I just took the first part of it I have it breaking down to quarters, weeks,and days. Evething in the query worked until I add the join statement and the 2 fields in the grouping mentioned above. I wiil past what works firs below and then what doesnt work.

This works

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS

ORDER BY 'QTR' asc

The below query doesnt work gives the error listed below when I go to preview in the sql reports

SELECT 'Quarter All' as 'qtr',
COUNT(JOB.JOBID) as 'transcount',
COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
SUM(JOB.LANGUAGE_TCOST) as 'lcost',
SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS EXPR1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
LANGUAGES.DESCRIPTION

WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)
AND
(MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12))
AND
(PAYER.PAY_COMPANY like '%' + @.Company + '%')

Group By
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
LANGUAGES.DESCRIPTION
Order By 'QTR' asc

Newbie needing help with joining 2 fields

I have a sql 2005 dev ed running an application developed in vs.net 2005 C# I have several gridviews which have a field call first name and a field called lastname I need to put both the firstname and lastname in the same cell or colum row. I do not now how to join these. Can some one help me with a SQL query string that will do this for me.You could do SELECT FirstName + ' ' + LastName FROM YourTable|||SELECT FirstName + ' ' + LastName AS FullName
FROM Employees

Newbie needing help creating query

Hi,

I know some SQL but not enough to write the query i'm trying to create and
could do with some help!

I have 2 tables (Product and ProductProgram) that are linked by a common
identified 'ProductID'. Each product has 5 different price levels
(1,2,3,4,5) and these are stored in the ProductProgram table.

The ProductProgram table contains the following columns:

ProductID
Level
Price

The Product table has the following columns:

ProductID
Name
Description

Can anyone show me how to return each product with all 5 of their individual
price levels?SELECT a.ProductID,
a.Name,
a.Description,
p1.Price AS Price1,
p2.Price AS Price2,
p3.Price AS Price3,
p4.Price AS Price4,
p5.Price AS Price5
FROM Product a
LEFT OUTER JOIN ProductProgram p1 ON p1.ProductID=a.ProductID AND
p1.Level=1
LEFT OUTER JOIN ProductProgram p2 ON p2.ProductID=a.ProductID AND
p2.Level=2
LEFT OUTER JOIN ProductProgram p3 ON p3.ProductID=a.ProductID AND
p3.Level=3
LEFT OUTER JOIN ProductProgram p4 ON p4.ProductID=a.ProductID AND
p4.Level=4
LEFT OUTER JOIN ProductProgram p5 ON p5.ProductID=a.ProductID AND
p5.Level=5|||Hi Mark,

Thanks very much! I would never have gotten there on my own!

Mintyman

<markc600@.hotmail.comwrote in message
news:1163429919.872807.120980@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

SELECT a.ProductID,
a.Name,
a.Description,
p1.Price AS Price1,
p2.Price AS Price2,
p3.Price AS Price3,
p4.Price AS Price4,
p5.Price AS Price5
FROM Product a
LEFT OUTER JOIN ProductProgram p1 ON p1.ProductID=a.ProductID AND
p1.Level=1
LEFT OUTER JOIN ProductProgram p2 ON p2.ProductID=a.ProductID AND
p2.Level=2
LEFT OUTER JOIN ProductProgram p3 ON p3.ProductID=a.ProductID AND
p3.Level=3
LEFT OUTER JOIN ProductProgram p4 ON p4.ProductID=a.ProductID AND
p4.Level=4
LEFT OUTER JOIN ProductProgram p5 ON p5.ProductID=a.ProductID AND
p5.Level=5
>

Newbie needing help ASAP with width issue in sql report.

I am building a Sql Report in VS.net 2006 I have run into an issue I have 258 colums it am using the wizard table generator in design mode. the problem is the screen will on allow 160 inches wide I need to mak this larger to 300 inches the purpose of the report is to sort data and them save to csv format is there a better way or can some on tell me how to increase the size of the width the properties tab will not allow me to explain pass 160 inches. Some one please help me thanksWhy not tackle the job another way ? Have a look at http://www.codeproject.com/useritems/filehelpers.asp "An easy to use .NET library to read/write strong typed data from fileswith fixed length or delimited records (CSV). Also has support toimport/export data from different data storages (Excel, Acces,SqlServer, MySql)"|||The issue I am having is a task was given to me to pull data from an invoice module out of SQL and creat a 288 field output of data in a csv format. The problem is we on have about 100 fields data the rest have to be blank and all this has to be in an order so . I was going to build a report in th sql reports in my business intell create the headers for all 288 files and create the expression in the correct placement create several sort paramerters then I was going to just save as csv format this would create the csv file with the correct order and blank fields separated by the commas. the problem is I dont know how to creat empty fields on an output to csv.|||

Suppose your table FRED has three fields A, C and D and you want to output 5, use:

SELECT A, ' ' AS B, C, ' ' AS D, E FROM FRED

That will create 2 empty fields - you can readily extend the technique to create 188 blank fields.

|||

Thank youy this makes sense. The only question is I may need it to look like this

"john", "Smith","","","","","","","404","555-5555","123 Main Street","","completed",

If I under stand the your code I can select A,B,' ' AS C,' ' AS D,' ' AS E, ' ' AS F, ' ' AS G, ' ' AS H,I,J,K,' ' AS L,M FROM TABLE

|||

>>The only question is I may need it to look like this: "john", "Smith","","","","","","","404","555-5555","123 Main Street","","completed",
The quoting takes places aroung each value, whether it is occupied or empty.

>>If I under stand the your code I can select A,B,' ' AS C,' ' AS D,' ' AS E, ' ' AS F, ' ' AS G, ' ' AS H,I,J,K,' ' AS L,M FROM TABLE

Yes! I put a space between the quote marks for clarity; you will not need to do this.

Newbie needing help -> poll a folder for new files

I need to create a SQLNS app to poll a particular folder for mpeg files. When a new file (which in this case will be an mpeg video file) is dopped into the folder, I need to write some of the files metadata (filename, size, path) to an existing SQL database on the same server. I'd also like to send out an e-mail notifying that an end-user that a record was added to the database.

The subscription piece (e-mail notification) seems easy enough. I'm curious about the implementation of inserting the file metadata into the database. I'm pretty new to SQLNS, so any help to point me in the right direction would be most appreciated.

Thanks in advance!

Hi -

If it were me, I'd probably investigate the following design. I'd create a Windows Service to watch the selected folder for the presence of new mpeg files. When one is discovered, I'd insert a row with the file's metadata into the SQL Server table.

Now for the SQLNS part. I'd create an instance/application that uses the SQL Server Event Provider to watch the SQL Server table mentioned above for the presence of a new row. Subscribers could create subscriptions based on whatever criteria you'd like to put in place (filesize, name, genre, etc).

Make sense?

Could all this be done within SQLNS? Sure, you could create a custom event provider to watch the folder, insert the metadata row, and then insert the event data.

Either would work....but for some reason I tend to prefer the separation afforded by the first solution.

HTH...

Joe

|||Sounds like a great idea! Thanks for your help!! :-D

Monday, February 20, 2012

Newbie - Advice pls !

Hello

I am running a HR database on a 2K SQL 2000 box which administrators have no access to in the near future - I am needing to export certain data from the db to another 2K SQL 2000 box for use in the company phonelist etc.

I am looking at running the task say at midnight every night, the query will replace the data on the destination server.

I know this sounds very vague - is DTS the correct medium for the job ?

Can anyone point me in the right direction on this ?

Thanks in advance !:confused:yeah, i would say DTS is the way to go....just schedule the update every night...do you want to overwrite the data on the destination server or simply append to it? If you want to append an leave the existing data there dont forget, use insert rather than update.

Hope this help