Urgent Converting A Query To A .asp Page
Aug 24, 2004
I am extremely new with .asp. We have the .net framework 1.1 installed on our server and the following query works in Crystal Reports (older version). We would like to have this converted to a .asp page with two inputs. One for the date range and one for the state. Can someone please tell me how to go about this. I do have downloaded as test. Visual Studio 2003.net and Web Matrix. I really am looking for (if there is any) a way to pretty quickly convert query to web pages so users can use them without a huge expense. IF someone already has something like this done. That would be great. The following query is for Visual Enterprise (Manufacturing software). Thanks
SELECT
SHIPPER."CUST_ORDER_ID", SHIPPER."SHIPPED_DATE", SHIPPER."INVOICE_ID",
SHIPPER_LINE."USER_SHIPPED_QTY", SHIPPER_LINE."SHIPPED_QTY", SHIPPER_LINE."UNIT_PRICE",
RECEIVABLE_CURR."CURRENCY_ID", RECEIVABLE_CURR."SELL_RATE",
CUST_ORDER_LINE."PART_ID", CUST_ORDER_LINE."ORDER_QTY", CUST_ORDER_LINE."USER_ORDER_QTY",
INVENTORY_TRANS."TYPE", INVENTORY_TRANS."ACT_MATERIAL_COST", INVENTORY_TRANS."ACT_LABOR_COST", INVENTORY_TRANS."ACT_BURDEN_COST", INVENTORY_TRANS."ACT_SERVICE_COST",
CUSTOMER_ORDER."CUSTOMER_ID", CUSTOMER_ORDER."SHIP_TO_ADDR_NO", CUSTOMER_ORDER."CURRENCY_ID",
PART."DESCRIPTION", PART."STOCK_UM",
CUSTOMER."STATE",
CUST_ADDRESS."ADDR_NO", CUST_ADDRESS."NAME", CUST_ADDRESS."STATE"
FROM
{ oj ((((((("VMFG"."dbo"."SHIPPER" SHIPPER INNER JOIN "VMFG"."dbo"."RECEIVABLE_CURR" RECEIVABLE_CURR ON SHIPPER."INVOICE_ID" = RECEIVABLE_CURR."INVOICE_ID") INNER JOIN "VMFG"."dbo"."SHIPPER_LINE" SHIPPER_LINE ON SHIPPER."PACKLIST_ID" = SHIPPER_LINE."PACKLIST_ID" AND SHIPPER."CUST_ORDER_ID" = SHIPPER_LINE."CUST_ORDER_ID") INNER JOIN "VMFG"."dbo"."INVENTORY_TRANS" INVENTORY_TRANS ON SHIPPER_LINE."TRANSACTION_ID" = INVENTORY_TRANS."TRANSACTION_ID") INNER JOIN "VMFG"."dbo"."CUSTOMER_ORDER" CUSTOMER_ORDER ON SHIPPER_LINE."CUST_ORDER_ID" = CUSTOMER_ORDER."ID") INNER JOIN "VMFG"."dbo"."CUST_ORDER_LINE" CUST_ORDER_LINE ON SHIPPER_LINE."CUST_ORDER_ID" = CUST_ORDER_LINE."CUST_ORDER_ID" AND SHIPPER_LINE."CUST_ORDER_LINE_NO" = CUST_ORDER_LINE."LINE_NO") INNER JOIN "VMFG"."dbo"."CUSTOMER" CUSTOMER ON CUSTOMER_ORDER."CUSTOMER_ID" = CUSTOMER."ID") INNER JOIN "VMFG"."dbo"."PART" PART ON CUST_ORDER_LINE."PART_ID" = PART."ID") LEFT OUTER JOIN "VMFG"."dbo"."CUST_ADDRESS" CUST_ADDRESS ON CUSTOMER_ORDER."CUSTOMER_ID" = CUST_ADDRESS."CUSTOMER_ID" AND CUSTOMER_ORDER."SHIP_TO_ADDR_NO" = CUST_ADDRESS."ADDR_NO"}
WHERE
SHIPPER."SHIPPED_DATE" >= {ts '2002-08-01 00:00:00.00'} AND
SHIPPER."SHIPPED_DATE" < {ts '2004-08-25 00:00:00.00'}
ORDER BY
SHIPPER."SHIPPED_DATE" ASC
View 3 Replies
ADVERTISEMENT
Mar 28, 2007
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
View 4 Replies
View Related
May 16, 2008
Hi All,
In my SSRS report. I have a report which has only one page. In preview it is showing as only 1 page but when I am printing the report. I am getting two printouts with the second page as a blank.. Please help me in printing the page that contains report. Intially I used a Page header, at that it used to print the blank page with a header only. Now as I removed the header it is printing the page without header i.e Blank Page.. So please help me in prinitng a single page that has the report. It is urgent,..
Thanks
dotnetdev1
View 5 Replies
View Related
Aug 25, 2004
IN Sybase
SP_HELPSORT is Producing the following
------------------------------
Sort Order Description
------------------------------------------------------------------
Character Set = 2, cp850
Code Page 850 (Multilingual) character set.
Sort Order = 50, bin_cp850
Binary ordering, for use with Code Page 850 (cp850).
Characters, in Order
What would be equivalent in SQL Server 2000 .
PLease let me know the Colation Name ASAP.
Greatly appreciate your help
Thanks
View 1 Replies
View Related
Jan 5, 2008
Hello, Im Getting an error (below) at the login page. The only control on
that page is a Login control, adn the error below comes when I click login. Does anyone know what Im doing wrong? This is my login page:<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Login.aspx.cs" Inherits="Login" Title="myapp- Login" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentHeader" Runat="Server"> LOGIN</asp:Content><asp:Content ID="Content2" ContentPlaceHolderID="ContentBody" Runat="Server"> <table border="1" cellpadding="0" cellspacing="0" width="305"> <tr> <td class="bodyText" colspan="2" style="height: 20px; text-align: center;"> <p> <!-- TemplateBeginEditable name="mainText" --> <asp:Login ID="Login1" runat="server" TitleText="Existing Users Log In"> </asp:Login> </p> </td> </tr> <tr> <td style="text-align: right"> <asp:LinkButton ID="lbNewmembers" runat="server" OnClick="lbNewmembers_Click">New Member?</asp:LinkButton></td> <td> <asp:LinkButton ID="lbForgotPassword" runat="server" OnClick="lbForgotPassword_Click">Forgot Password?</asp:LinkButton></td> </tr> </table></asp:Content>Error:
Server Error in '/myApp' Application.
Conversion failed when converting from a character string to
uniqueidentifier. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: Conversion failed when converting from a
character string to uniqueidentifier.Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.312;
ASP.NET Version:2.0.50727.833
View 1 Replies
View Related
May 20, 2008
Hi
When i use my insert-exec in my proc, say it Y, and the inner proc is X (don't contain an other insert-exec). when i call the Y proc in sql query analyzer the call is executed and it get me back results.
But when I call the Y proc from an ASP page, the execution is aborted, and I don’t know why, I have tested many thinks but it doesn’t work (for exp: the use commit transaction, set ....).
Please could you tell me if i miss some things i should add to my procs for the IIS web server could have right to execute my Y proc
Configuration of my application
- IIS Web server 6.0
- Sql Server 2000 SP 2.0
- TSQL as a sql language (of course)
- ASP as web porgramming language
- The call of the insert exec proc is as following:
o INSERT INTO #TMP_F_B (
o Field1 ,
o Field2,
o Field3 ,
o …,
o )
o EXEC dbo.ps_a5s_rpt_charge700 @util, @annee , @perimetre , @secteur , @poste , @fournisseur , @err , @lib_err
Many thanks
Sincerly
View 2 Replies
View Related
Aug 27, 2001
Any idea what this error is??
Thanks.
Attempt to fetch logical page (3:8360) in database 'XYZ' belongs to object '1934538212', not to object 'product'. [SQLSTATE HY000] (Error 605). The step failed.
View 1 Replies
View Related
Apr 2, 2008
Hi All,
Is there any way to remove the My Subscription,Site Setting links on the main page in Report manager.And also our client wants SSRS reports to be seen by his customer over the internet.Anybody knows about this?
Any help will be appreciated.
Thanks
Anupama
View 9 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Mar 30, 2006
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
View 2 Replies
View Related
Nov 11, 2015
I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
View 3 Replies
View Related
Jul 20, 2005
i have a query which goes like this:-select a.col1,b.col2,c.col2from tab1 a,tab2 b,tab3 cwherea.col1 *= b.col1 anda.col2 *= c.col2 andb.col3 = c.col3how do I write this query in SQL 92?also can we replace *= with =* by interchanging the participating tables as in"a.col1 *= b.col1" is this same as "b.col1 =* a.col1"
View 2 Replies
View Related
Dec 6, 2007
Hi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks
View 5 Replies
View Related
Jan 23, 2015
I getting an error when trying to convert the following into a view. I can't take the credit for the code as I copied it from with a program.
Create view vwMAW_KnowledgeDB AS
Select * from don0001
left join (select donclass.donor from donclass where classification = 9 and code in ('KB')) inc0 on inc0.donor = don0001.donor where inc0.donor is not null
quote:Error; Msg 4506, Level 16, State 1, Procedure vwMAW_KnowledgeDB, Line 2
Column names in each view or function must be unique. Column name 'donor' in view or function 'vwMAW_KnowledgeDB' is specified more than once.
View 1 Replies
View Related
Oct 15, 2007
SELECT distinct whse_zone_id, bay_id
From prod_geneology WHERE whse_zone_id in ('SH','CU','SG')
START WITH unit_id = 'BL7B230811'
CONNECT BY PRIOR parent_id = unit_id
And in this case, 'BL7B230811' could be any 10 character string
Any help would be grateful.
Thanks
View 1 Replies
View Related
Dec 7, 1999
Hi,
I'm using a database through ODBC in an application designed on Macromedia Drumbeat 2000, and use the following query for one of the segments:
SELECT DISTINCTROW Books.ISBN, Books.Title, Books.Category, Books.Description, Books.Price, Books.Pubdate, Books.Keywords, Books.UnitWeight, Authors.au_lastname, Authors.au_firstname, Authors.au_midname, Publishers.pub_name, Books.Pub_No
FROM Publishers INNER JOIN (Authors INNER JOIN Books ON Authors.au_id = Books.Au_No) ON Publishers.pub_id = Books.Pub_No
It works fine if I use the MS Access version of the database through ODBC, but if I try using the SQL version, I get the following error for this query:
Line 1: Incorrect syntax near '.'. Statement(s) could not be prepared.
Why on earth is this happenning? I'm completely at my wits end...and pointers would be wonderful.
Thanks...
View 1 Replies
View Related
Jan 28, 2008
I am getting a slightly different result set after converting my Oracle query to MS SQL. I am only getting those with invm_cost_code = 'A' and not those that are null as I do when I run the query on Oracle. Note, if I change to left outer join, still get wrogn values. But, if I change to full outer join I get those = 'A' and those that are either null or 'C' as nulls (the 'c' come through as null. Any ideas?
Oracle Query
SELECT CITM_PROPRD, CITM_INV_SEQ, CITM_ITEM_SEQ, INVM_COST_FLAG, (CITM_QTY * CITM_PRICE)
FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR
WHERE CSUM_COMP_CODE = 'A' AND
CSUM_EMPL_CODE = SUBSTR('85',1,2) AND
TRIM(CSUM_TERR_CODE) = TRIM('MB2') AND
CSUM_PROPRD BETWEEN '200610' AND '200611' AND
CSUM_PROPRD = CITM_PROPRD AND
CSUM_INV_SEQ = CITM_INV_SEQ AND
CITM_COMP_CODE = INVM_COMP_CODE(+) AND
CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND
(INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL)
Converted MS SQL Query
SELECT CITM.CITM_PROPRD, CITM.CITM_INV_SEQ, CITM.CITM_ITEM_SEQ ,INVM.INVM_COST_FLAG,(CITM.CITM_QTY * CITM.CITM_PRICE)
FROM ACC_CUST_SUM as CSUM
INNER JOIN ACC_CUST_ITEMS as CITM
ON
CSUM.CSUM_PROPRD = CITM.CITM_PROPRD AND
CSUM.CSUM_INV_SEQ = CITM.CITM_INV_SEQ
RIGHT OUTER JOIN ACC_INV_MSTR as INVM
ON
CITM.CITM_COMP_CODE = INVM.INVM_COMP_CODE AND
CITM.CITM_ITEM_CODE = INVM.INVM_ITEM_CODE AND
(INVM.INVM_COST_FLAG = 'A' OR INVM.INVM_COST_FLAG IS NULL)
WHERE CSUM.CSUM_COMP_CODE = 'A' AND
CSUM.CSUM_EMPL_CODE = SUBSTRING('85',1,2) AND
LTRIM(RTRIM(CSUM.CSUM_TERR_CODE)) = LTRIM(RTRIM('MB2')) AND
CSUM.CSUM_PROPRD BETWEEN '200610' AND '200611'
View 6 Replies
View Related
Jan 17, 2005
I'm trying to create an Insert query and I'm having difficulty in 2 areas:
First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.
INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP
Second, I've tried the following"conversions" in the SELECT statement, to no avail:
CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)
Any pointers much appreciated...
View 2 Replies
View Related
Jul 15, 2015
I'm struggling to modify the T-SQL query below to return the results as XML.
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119, 122);
The XML result document should have the following structure:
<Root>
<ProductModelData id="119">
<Name>Bike Wash</Name>
</ProductModelData>
<ProductModelData id="122">
<Name>All-Purpose Bike Stand</Name>
</ProductModelData>
</Root>
View 2 Replies
View Related
Sep 19, 2014
I have a string like below
DECLARE @STR VARCHAR(100) = 'AAAAAA~KKKKK~LLLL~AAAA'
i want to convert the string into rows like
AAAAAA
KKKKK
LLLL
AAAA
View 2 Replies
View Related
Aug 10, 2007
This query from access does not work in sql server 2000. How do I write this in sql to run?
SELECT First(tri_ProcMast.ddesc) AS FirstOfddesc, tri_ProcMast.proccd
FROM tri_ProcMast
GROUP BY tri_ProcMast.proccd;
View 2 Replies
View Related
Jul 20, 2005
I am trying to upsize a database to SQL server (on which I am a novice). InAccess as part of a much more complex query I had the following (from sqlview)SELECTIIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemName]) AS ShortName FROM corp_infoWhich gives a return value for the whole of ItemName if there is no star init, or the portion up to the star if there is a starI am having a nightmare trying to get an equivalent in SQL server. I'veworked out that Instr is charindex in sql and can adjust for that, but can'twork out how to get a conditional select statement working.It may well be obvious, but any help much appreciated. Thanks.Robin Hammondwww.enhanceddatasystems.com
View 1 Replies
View Related
Jul 20, 2005
I am struggling rewriting my query from MS Access' IIF, Then to SQLServers TSQL language. I am hoping some one can give me someguidance. I believe I have the first portion of the query correct butdo believe this requires a "NESTED" argument. This is where I amlost.My Original MS ACCESS Query reads--SELECT DISTINCTROW REGION_TRAFIC.*,IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],IIf([Carrier]="VENDOR1",[DOMESTIC LDRATES]![INTRA_VENDOR1])))),IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],IIf([Carrier]="VENDOR1",[DOMESTIC LDRATES]![INTER_VENDOR1]))))) AS CPM,[CPM]*[MOU] AS COSTINTO INTRALATA_LDFROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ONREGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEYWHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))ORDER BY REGION_TRAFIC.[TERM LATA] DESC;I have tried to re-write this in SQL SERVER as --SELET DISTINCT REGION TRAFIC.*,CASEWHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LDRATES].INTRA_VENDOR4WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LDRATES].INTRA_VENDOR3WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LDRATES].INTRA_VENDOR2WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LDRATES].INTRA_VENDOR1ELSEWHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LDRATES].INTER_VENDOR4WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LDRATES].INTER_VENDOR3WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LDRATES].INTER_VENDOR2WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LDRATES].INTER_VENDOR1ENDAS CPMCPM*MOU AS COSTINTO INTRALATA_LDFROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ONREGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEYWHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))ORDER BY REGION_TRAFIC.[TERM LATA] DESCMy challenge is the Case portion of the query and the nesting! I amnot sure if I have the correct syntax or even chose the correctargument for my purpose.Any guidance is appreciated.
View 9 Replies
View Related
May 28, 2008
Hi,
I want to run an asp file from a query written in sql 2005.
How can I achieve this in sql?
Please let me know its very urgent.
View 1 Replies
View Related
Sep 9, 2004
Hi, I have an SQl database that i want to build a simple search query for that would be accessible from an HTML page, what would be the best way of going about this? and what would be the best language to use?
Thanks in advance
View 1 Replies
View Related
Jan 28, 2005
How would I convert this query to ANSI 92 standard?
select p.InternalID,
n.LastName
from tblPatient p,
tblName n
where p.PatientID = n.EntID and
n.EffDt = (
select max(n2.EffDt) from tblName n2 where n.EntID = n2.EntID and n2.EffDt <= getdate()
)
View 1 Replies
View Related
Jan 14, 2001
Hi ,
I have a question for all , please help me out.
the question is : i have a query in Oracle like this
select count(*), to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy') from ISL_USERS
group by to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy')
order by to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy');
i want to convert this into SQLserver but in SQL server equlent function for TO_CHAR is Datename ..when i have given this i am getting error . please give me the tip on this issue .
thanks
shekhar
--------------------------------------------------------------------------------
|
View 1 Replies
View Related
Nov 9, 2001
I am switching my database from MS access to SQL server, and i want the following query to br converted to SQL stored procedure
CREATE PROCEDURE FORUM_MESSAGE AS
SELECT *
FROM FORUM_MESSAGES
WHERE ID=MessageID;
here "MessageID" is a run time generated parameter, and is not a field in the database.
thanx
View 1 Replies
View Related
Nov 24, 2004
I have the following query:
(SELECT MIN(CFGDates.AccountPdEnd)
FROM CFGDates LEFT JOIN
AR ON AR.Period = CFGDates.Period
WHERE AR.Period = '200408')
I need to convert this into a case statement.
I tried various ways but did not get the result that I was after
Thanks,
Laura
View 6 Replies
View Related
Mar 19, 2015
I have a field that was text YESNONull. When I imported it into SQL I converted it to Boolean and had to go back and null out the ones that were supposed to be null. I am now trying to use the fields in my old reports and find that I have to convert the values to YesNo inside the query. This causes me to have to change almost every report. So I am wondering if there's a easier way to return YesNoNull or should I just keep the field as a 3 byte text?
Code:
SELECT Jobs_Table.JobNum,
[Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces,
The_Big_One1.HT_Mail_STId,
IIf(IsNull([The_Big_One1].[First_Scan_Date]),"",IIf([The_Big_One1].[STC_SCAN]=True,"Yes","No")) AS STC,
[Code] ....
or
Code:
SELECT Jobs_Table.JobNum,
[Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces,
The_Big_One1.HT_Mail_STId,
THE_Big_One1.STC_SCAN,
[Code] ...
View 5 Replies
View Related
Aug 5, 2014
I have a rather strange requirement where I need to convert my stored procedure into a normal SQL query for generating a SSRS report.
The business is very specific not to use SP or temp table in the query for the report generation.
I have a stored proc where I have used temp table and I have inserted values into that table.
what all these things I need to take when I convert it to as a SQL query for report generation?
View 9 Replies
View Related
Oct 10, 2006
You can display both by referring to their position in the recordset or by aliasing them in the query and referring to the alias in your code.
Note that in order to put Products.Quantity or ProductDetails.Quantity in just one column you'll have to add some logic in the query, using a coalesce, like:
sql Code:
Original
- sql Code
SELECT
Products.ProductID, Products.ProductName, Products.ProductPict,
COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity,
ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_Color
FROM
Products
LEFT OUTER JOIN
ProductDetails
ON Products.ProductID = ProductDetails.ProductID
WHERE
Products.Quantity <= "& +rsResults__var1 & "
OR
ProductDetails.Quantity <= "& +rsResults__var1 & "
Order by Products.ProductID asc;
SELECT Products.ProductID, Products.ProductName, Products.ProductPict, COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity,ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_ColorFROM Products LEFT OUTER JOIN ProductDetails ON Products.ProductID = ProductDetails.ProductID WHERE Products.Quantity <= "& +rsResults__var1 & " OR ProductDetails.Quantity <= "& +rsResults__var1 & " ORDER BY Products.ProductID ASC;
Untested, beware of syntax errors!
In this case you'll have just one Quantity colum and you won't have to change anything in your asp code.
View 1 Replies
View Related
Apr 28, 2008
I have a table which records the amount of times a user views a webpage
tblpageview
id
pagename
view_date
userid
I would like to have a query that returns the unique pagename and the amount of times that page has been viewed. I've had a go with count etc. but cant seem to get it. Anyone can help?
View 3 Replies
View Related
