Hello Friends
I am very glad to tell all u ppl that i ve got the solution to my problem at last
I ve just posted my problem(few mins back) in which i was getting problem with connecting to the ASP.NET Web application. I have made a new user on sql through SQL Enterprise Manager as <machinename>ASPNET, and my problem gets solved
sorry to bother u all
Regards
BSS
I have a table with 3 columns (ID Int , Name Varchar(25), Course Varchar(20))
My source data looks like below
ID Name Course
1 A Java
1 A C++
2 B Java
2 B SQL Server
2 B .Net
2 B SAP
3 C Oracle
My Output should look like below...
ID Name Course(1) Course(2) Course(3) Course(4)
1 A Java C++
2 B Java SQL Server .Net SAP
3 C Oracle
Basically need t-sql to Convert non fixed rows to non fixed columns...
Rule: IF each ID and Name have more than 1 course then show it in new columns as course(1) course(2)..Course(n)
Create SQL:
Create table Sample (ID Int null , Name Varchar(25) null, Course Varchar(20) null)
Insert SQL:
INSERT Sample (ID, Name, Course)
VALUES (1,'A','Java'),
(1,'A','C++'),
(2,'B','Java'),
(2,'B','SQL Server'),
(2,'B','.Net'),
(2,'B','SAP'),
(3,'C','Oracle')
Can someone from MS tell me if this has been fixed in SP1?
http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx
Thanks
Jamie
Hi all.
I'm trying to export some tables in fixed width format for a client that needs it this way. The first time I did this, using Import/Export in Enterprise Manager, integer fields were 12 chars long. However, when I saved this as a DTS and ran it again later, it made integers 4 chars in the text file (which matches the lengths of the datatypes you see in enterprise manager.)
Why is this difference there? What do I need to do differently? Also, is there a list of how wide each data type is when exported as fixed delimited text?
thanks,
Craig
philippe writes "Hello ,
i am just starting with sql...
I have a table: Table_user
Inside a column user_no (integer)
with value like 35678 (about 8000 rows)
I would like to modifie all rows of this Table_user.user_no
with a value of 60 at the begining of each value.
For example : before 35678 will be 6035678
Its'a concatenation,but it's an integer value .
so do i need to convert tehm to varchar first
I will appreciate some tips...."
Hi everyone,
I'm trying to make a hierarchy in which some nodes are locked, preventing children nodes from being added.
The hierarchy table points to itself (the parent entry). I want to secure this from the DB side. Initially, no nodes have parents but can attach themselves to one.
I'd like my FK contraint to be like:
CONSTRAINT FK_hierarchy FOREIGN KEY (parent_id, 1) REFERENCES nde_recursion(hierarchy_id, allow_children)
This is so the parent_id can only be set if that entry allows children.
Here's my approach. Any better ideas? I feel like I'm adding an extra field (parent_allow) when I shouldn't.
CREATE TABLE hierarchy(
hierarchy_idINTIDENTITY(1,1) UNIQUE NOT NULL,
allow_childrenBITDEFAULT 1 NOT NULL,
parent_idINT,-- NULL is for root nodes
parent_allowBITDEFAULT 1 NOT NULL,
CONSTRAINT PK_hierarchy PRIMARY KEY (hierarchy_id, allow_children),
CONSTRAINT FK_hierarchy FOREIGN KEY (parent_id, parent_allow) REFERENCES nde_recursion(hierarchy_id, allow_children),
CONSTRAINT CK_hierarchy CHECK ((nde_status = 'Normal' AND parent_id IS NULL) OR (nde_status != 'Normal' AND parent_id IS NOT NULL)),
CONSTRAINT CK_hierarchy2 CHECK (parent_allow = 1)
)
Hello!
I have a report that the users reach from an url. In the url I have rc:parameters = false. That part works fine. But when I use rc:parameters = false, the property fixed header doesn't seem to work. If I run the report from the report server it works fine, but not from the url.
Any ideas?
/C
Hi
I have a table in a report which has the FixedHeader property set to true. What happens is that when I try to look for a text using the find option, the found text gets hidden under the header row, which then I have scroll back to see that. The user may think that the text was not found. Any solution to be able to bring the found item to somewhere the user can see?
Thank you
I am trying to show latitude and longitude with 5 decimal points. Now its showing (for example: 55.744025477, -4.1256633333333 etc.). How do I get data in 5 decimal points? Your help with example would be appreciated. aspx code: <asp:GridView ID="GridView1" runat="server" DataSourceID="odsGPS" AllowPaging="true" AllowSorting="true" AutoGenerateColumns="False" CellPadding="1" CellSpacing="1" BackColor="White" GridLines="None" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" PageSize="20" Width="100%" Font-Size="8pt" OnLoad="GridView1_Load" > <Columns> <asp:TemplateField HeaderText="Show"> <ItemTemplate> <asp:CheckBox ID="CheckBox2" onclick="MarkerForThisRow(this);" ToolTip="Click to show on map." runat="server" OnCheckedChanged="CheckBox2_CheckedChanged" /> </ItemTemplate> <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:TemplateField> <asp:BoundField DataField="Latitude" HeaderText="Latitude ( ° )" > <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField> <asp:BoundField DataField="Longitude" HeaderText="Longitude ( ° )" > <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField>
I have a request to produce a SQL report that will be produced with fixed column width. For example see below Position Element LengthField Format 1-2 Column1 2 Alphanumeric 3-4 Column2 2 Alphanumeric 5-13 Column3 9 Chars Any idea how I can produce the report specified above? Thanks
View 1 Replies View RelatedI have a server that was upgrade to 7 several months ago. The cmptlevel was set to 7 once upgraded. I have a developer group as dbo_owner role on a database and now on certain tables (not all) they can add fields and save. But then go back in and remove the field then try to save they get the error message "- Unable to modify table. ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server]SETUSER permission denied, database 'XXX',owner 'dbo'."
View 1 Replies View Relatedhi when I try to export data to a fixed width txt file I get the following error:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "postcode" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
even though my sql for postcode is LEFT(postcode,8) and the field length is 8 !
do you now why ?
I am attempting to build our first set of packages populating or DW. All of our source system primary keys are bigints (on the main tables at least). SSIS seems to have a problem dealing with bigint values, i.e. having to assign variables as doubles rather than int64, having to cast returning bigint values as float, or bigint sproc output parameters as double. It is all a bit messy (and possibly the most frustrating part of SSIS for me) - does anyone know if this problem is due to be fixed in any forthcoming release?
View 2 Replies View Related
I would like to add right number of 0 to make the data length of varchar (14)
For example,
before after
1768 -- > 0000001768.000
23456.78 --> 0000023456.780
123679.876 --> 0000123679.876
what is the best way to do this?
I have table like below
ID AMT
1001 1234.560
1001 34.560
1001 134.000
1002 45.000
1002 3456.000
1003 5678.999
I need to create a fixed length data file..For example, ID char(6) and amt num(10.3) and sum(23.3)
It should be group and order by ID : 01 Represent ID line and 02 represent amt ( there can be multiple amt records) and 03 represent sum of amt.
01 + ID
02 + AMT
03 + Sum(AMT)
The output should look like this.. How do this either using a sql or cursor?
011001
021234.560
0234.560
02134.00
031403.12
011002
0245.000
023456.000
033501.000
011003
025678.999
035678.999
A table has a column of int type. I need to select a fixed number of rows for each value. For example, if data in that column (c) are 5, 6, 7, and the number I want to select is 2, then I need 2 rows from c=5, 2 from c=6, and 2 from c=7. How to write that query? Any idea?
Thanks.
I created a package to import records from a fixed length 700 byte text file to a table in a SQL Database. I used the wizard to set it up and note the byte where each column ends. I need to customize the process, as the name of the text file will change each night, so I want to be able to set the file name from the VB front end app.
I have tried modifying the Datasource property of the DTS connection to the flat file, without success. I have also tried setting a global variable for the datasource property in DTS, and assigning that from VB, similarly without luck.
Do I have to create a custom package in code from VB? If so, how do indicate where each column in the text file ends? If I can customize the existing package, is there a specific reference that I need to set in my project that will let me control the value of the global?
TIA
Hi,
I try to use Data Transaction Service (SQL Server 7) to copy information from an Sql Server Table to an text file (fixed field). When I run the process, no problem, the text file is created and a Ok message appears. But when I look in my text file, every time a field was NULL or empty in the source table, the following fields are not aligned !
Somebody knows what the problem ?
Thanks
Vincent
Ps: I'm french so excuse me for my english
What is the best way to force a 2 digit decimal place without rounding?
For example select price*UOM returns
47.1294
3.255
.5
8
.49
What i want to be returned is
47.12
3.25
8.00
.50
.49
Thanks,
Jim
I have a request from a vendor to export data out of my SQL Server 2K database view to a 'flat fixed file'.
What kind of file is this exactly, not a .csv ? Does EM have the capabilities through the DTS wizard, by choosing the output to a text file and fixed width ?
Thank you
I need to write data into a fixed column length file and was wondering the best (most efficient) way to tackle this. For example, the first few pieces of the report I'm working on now would be:
PacketID - Starting position 1, Field length 9
TransactionID - Starting position 10, Field length 9
Group number - Starting position 19, field length 10
PID/SSN - Starting position 29, field length 10
For the PID/SSN, if I have a PID it'll be 10 digits and fill the field length, if I don't I use SSN which is only 9 digits and enter a space as the 10th digit. Obviously if I don't have certain pieces of information I'll just need spaces of the specified length to satisfy the file format. I'm using SQL 2005. Thanks in advance for any help provided.
I have approximately 13 columns. Each Column has a start position and end position.. I created this in a table and defined the position, it's still not working for me.
FiceCode char(6), -- starting position 1, field length 6
StateStudID char(10), -- starting position 7, field length 10
CampusStudID char(10), -- starting position 17, field length 10
LastName char(25), -- starting position 27, field length 25
[Code] .....
I need a text output file that will define each start position.I also used: right(replicate('0',25) + cast(last_name as char(25)), 25) in my sql statement.when I add the first_name, I can't get it to start in position 52.
I have a table (tblCustomer) with three fields (customer_id, s_id, s_string)
I want to fill in this table with two fixed values ??and customer_id from another table.
Every customer that starts at P in tblMainCustomer.customer_nr should be entered in table tblCustomer.
Select customer_id from tblMainCustomer where customer_nr like 'P%'
Customer_id taken from tblMainCustomer and s_id, s_string these fixed values ??that are equal for each customer.
These fixed values ??are:
100-----Gold
101-----Steel
1002----Super Copper
Example: If I have a client who has has a customer_id 45 so it will be like this in tblCustomer:
customer_id----s_id----s_string
-------------------------------
45-------------100-----Gold
45-------------101-----Steel
45-------------102-----Super Copper
I am stuck, how do I do this the best way?
Rob writes "We have had an on-going problem in our production database. It appears to all be index corruption. We've opened a PSS with Microsoft, but I figured I'd post here also. We're getting 604,605 errors as well as DBCC CHECKDB errors. All reports are that either REPAIR_REBUILD or REPAIR_FAST will fix the problem. Since putting the database into single_user mode is not an option at this time, we've just been rebuilding the problem indexes with DBCC DBREINDEX. This typically fixes the problem. Microsoft PSS says they believe that it's hardware problems. We're in the process of updating firmware on our storage device. (MSA 1000) This is a clustered machine. Tonight, our index rebuilds completed successfully, but a DBCC CHECKTABLE failed with 8951 and 8955 errors with the REPAIR_FAST option being supplied. I've rebuilt all the indexes on the table, but the DBCC CHECKTABLE still reports with errors. As a test, I DTS'd a copy of this table to make sure that I would at least be able to run a select * on it. That worked okay. My question is the following: Does the repair option for DBCC CHECKDB/TABLE do something that DBCC DBREINDEX doesn't? Thanks in advance! Rob P.S. This is a Siebel environment. :("
View 3 Replies View RelatedHello,
Here is the code as it now stands:
select TECHN, RESCODE,
(CASE
WHEN TECHN IS NOT NULL AND RESCODE IS NOT NULL AND COMPLET IS NULL THEN 'TOTAL RESCODE'
when TECHN IS NOT NULL AND RESCODE IS NULL THEN 'TOTAL TECH'
ELSE CONVERT(VARCHAR, CAST(COMPLETE AS DATETIME), 105) END) COMPLETE, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUM, OPR, sum(QTYW) QTY, sum(NCommissionAMT) CommissionAUT
FROM
(select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 1, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser.dbo.Parser_ocr
Inner Join Parser.dbo.Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN Parser.dbo.RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 1, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 1, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 4, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 4, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 4, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 7, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT (DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 7, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 7, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 10, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 10, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 10, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 13, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 13, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 13, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 16, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 16, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 16, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT)
group by rollup(TECHN, RESCODE,(COMPLET, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUMB, OPR))
order by 1, 2,3, 5;
Msg 156, Level 15, State 1, Line 150
Incorrect syntax near the keyword 'group'.
Could the error message be in the INNER JOIN?
What do I need to check for next.
Thanks for eveyrone's help so far.
Kurt
How do I create a text file with fixed length fields from a SQL table using a stored procedure.
View 4 Replies View RelatedI have table like below
ID AMT
1001 1234.560
1001 34.560
1001 134.000
1002 45.000
1002 3456.000
1003 5678.999
I need to create a fixed length data file..For example, ID char(6) and amt num(10.3) and sum(23.3)
It should be group and order by ID : 01 Represent ID line and 02 represent amt ( there can be multiple amt records) and 03 represent sum of amt.
01 + ID
02 + AMT
03 + Sum(AMT)
The output should look like this.. How do this either using a t-sql or SSIS?
011001
021234.560
0234.560
02134.00
031403.12
011002
0245.000
023456.000
033501.000
011003
025678.999
035678.999
I am attempting to import a fixed width file into a SS2005 table and am having problems when importing a date that has no value in it. The table will allow nulls.
The date is in dd/mm/yyyy foramt and when there is no date then there are 10 spaces. When transforming the data I TRIM the data down using a derive transform script so all there is, is an empty string. When the file attempts to load I get the following message:
[OLE DB Destination [2238]] Error: There was an error with input column "paid_date" (2306) on input "OLE DB Destination Input" (2251). The column status returned was: "The value could not be converted because of a potential loss of data.".
How can it potentially lose data when there is nothing to lose?
I need some way of converting the empty string into a null. Has anyone got any ideas for me?
I have a fixed width file that I only need a middle field imported.
The field I need starts at character 116 and is 15 character wide. What should my format file look like?
I have a format file that can import a field before it but this is data I don't need. How do you skip the first field?
Hello,
I am trying to find a way to fix the size of a table in SSRS
so that it would not push any items underneath it when it grows, due to
multi-row data set.
I have set the canGrow = false property of all the text
boxes in the table, but this did not help.
Does anyone know if it is possible to force a fixed size of
the table and how it is done?
Thanks.
TF
Hello I need to export my table fixed filed size by making numbers 15 characters right alignment and strings 20 characters left alignment.
View 1 Replies View RelatedI'm sending the results of an SSIS data flow to an fixed-width flat file output, but instead of getting separate rows of data, like so:
row1data...
row2data...
row3data...
etc...
I get:
row1data...row2data...row3data...etc...
Is there some setting I'm missing in either the flat file output or the file connection to turn this on?
I have a fixed-length flat file that contains about 30 columns. I have got it pretty well figured out using the flat file connection tool, but I am having trouble with the end of the line.
I know when I look at the file it is a CrLf that separates the rows, and SSIS only seems to understand this to a certain extent. It knows to go to the next line, but it also adds two rectangles to the lines, like this:
Col1 Col2 Col3 Col4 Col5
aaaaaa bbbbb ccccc ddddd eeeee
[][]aaaaa bbbbb ccccc ddddd eee
ee[][]aaaaa bbbbb ccccc ddddd e
eeee[][]aaaaa bbbbb ccccc ddddd
While this does create a cool pattern, it is a pain in the butt. The only solution I have found is adding two more spaces to the last column in the table, but the ?s just get appended there.
If anybody has any clue how to get rid of them, that would be great.
Thanks in advance